Order By Aggregations With ActiveRecord

The issue is following, say you have got a classical structure in two models

class Order < ActiveRecord::Base
  has_many order_items

  def total_due
    order_items.to_a.sum(&:total_price)
  end
end

class OrderItem < ActiveRecord::Base
  belongs_to :order

  # and it has two principal fields
  # decimal :price
  # integer :quantity

  def total_price
    price * quantity
  end
end

As you see the orders don't have own total_due fields, instead of this each order can calculate the value depend on the aggregated items. So, that's cool, we follow all the good design habits and keep the things clean.

But when you retrieve/display the orders list there might be a situation when a customer would like to order the list by the total_due value. Quite a natural desire, but there's the problem, we don't have the total_due field in really. It's virtual and depend on the aggregated objects.

Yes, you may reorder the items after retrieving, manually, but if you have quite a lot of orders and use a pagination you'll get back to the problem anyway.

The example is pretty simple, but you see, there might be lots of similar situations when you have some calculations on an aggregated data and would like to order your lists depends on the virtual fields.

So, how do we handle that?

We use the :select option for the ActiveRecord find method. Usually with the option you specify a number of fields you would like to extract, to not suck up heavy ones when you don't want them. But with the same option you may specify additional fields to extract and use all the power of SQL expressions. Those new fields will be added to the instanced object temporary, you can read them but you can't save them.

In our case this might be something like that

:select => "SUM(order_items.price * order_items.quantity) as total_due"

In the case we are using the SQL function SUM, calculate the total-due for each order, and then each selected order will obtain the total_due attribute after extraction and you may use it for your sorting options.

Then, as you see we mentioned the 'order_items' column in the select option, to make it working we should add a tables join and the items grouping options. The overall instruction might look like that.

Order.find(:all,
  :select => "SUM(order_items.price * order_items.quantity) as total_due, orders.*",
  :joins => "INNER JOIN order_items ON order.id = order_items.order_id",
  :group => "order.id",
  :order => "total_due"
)

After this you will need to fix your Order model a little, to make it use the temporary field when it appears

class Order < ActiveRecord::Base
  def total_due
    self[:total_due] || order_items.to_a.sub(&:total_price)
  end
end

Yes, that looks a little bit sqlish dirty for a rails application, but then it works and does what you need.

And there's another issue with the solution. You actually cannot use the :include option with the :select option at the same time. That's usually not a big issue, just note, if you specify the :include option, your :select option won't work.