Duplicated Business Logic in Ruby and SQL

Question

I have PORO (Plain Old Ruby Object) to deal with some business logic. It receives an ActiveRecord object and classifies it. For simplicity, take the following as an example:

 class Classificator STATES = { 1 => "Positive", 2 => "Neutral", 3 => "Negative" } def initializer(item) @item = item end def name STATES.fetch(state_id) end private def state_id return 1 if @item.value > 0 return 2 if @item.value == 0 return 3 if @item.value < 0 end end 

However, I also want to make queries that group objects based on these state_id "virtual attributes". I am currently doing this by creating this attribute in SQL queries and using it in GROUP BY statements. See an example:

 class Classificator::Query SQL_CONDITIONS = { 1 => "items.value > 0", 2 => "items.value = 0", 3 => "items.value < 0" } def initialize(relation = Item.all) @relation = relation end def count @relation.select(group_conditions).group('state_id').count end private def group_conditions 'CASE ' + SQL_CONDITIONS.map do |k, v| 'WHEN ' + v.to_s + " THEN " + k.to_s end.join(' ') + " END AS state_id" end end 

That way, I can get this business logic in SQL and make such a query in a very efficient way.

The problem is this: I have duplicated business logic. It exists in the ruby ​​code for classifying a single object, and also in SQL for classifying a collection of objects at the database level.

Is this a bad practice? Is there any way to avoid this? I really was able to do this by following these steps:

 item = Item.find(4) items.select(group_conditions).where(id: item.id).select('state_id') 

But by doing this, I lose the ability to classify objects that are not stored in the database. Another way is to classify each object in ruby ​​using Iterator, but then I will lose database performance.

It seems inevitable to maintain duplicate business logic if I need the best of two cases. But I just want to be sure of that. :)

Thanks!

Answer 1

Is there a chance to enter a trigger in the database? If so, I would go with the “computed” state_id field in the database, which will change its value to both INSERT and UPDATE (this will bring even more performance gain), and this code in ruby:

 def state_if return @item.state_id if @item.state_id # persistent object case @item.value when 0 then 2 when -Float::INFINITY...0 then 3 else 1 end end 

Answer 2

I would prefer to keep the database as simple and logical as possible in Ruby code. Since the classification is not stored in the database, I will not expect queries to return it.

My solution is to identify the problem that will be included in the classes of the ActiveRecord model.

 module Classified extend ActiveSupport::Concern STATES = { 1 => "Positive", 2 => "Neutral", 3 => "Negative" } included do def state_name STATES.fetch(state_id) end private def state_id (0 <=> value.to_i) + 2 end end end class Item < ActiveRecord::Base include Classified end 

And I get items from the database, as usual.

 items = Item.where(...) 

Since each item knows its own classification value, I do not need to query the database for it.

 items.each do |item| puts item.state_name end