How to eager load child model's sum value for ruby on rails?

Question

I have an Order model, it has many items, it looks like this

class Order < ActiveRecord::Base
  has_many :items

  def total
    items.sum('price * quantity')
  end
end

And I have an order index view, querying order table like this

  def index
    @orders = Order.includes(:items)
  end

Then, in the view, I access total of order, as a result, you will see tons of SUM query like this

SELECT SUM(price * quantity) FROM "items" WHERE "items"."order_id" = $1  [["order_id", 1]]
SELECT SUM(price * quantity) FROM "items" WHERE "items"."order_id" = $1  [["order_id", 2]]
SELECT SUM(price * quantity) FROM "items" WHERE "items"."order_id" = $1  [["order_id", 3]]
...

It's pretty slow to load order.total one by one, I wonder how can I load the sum in a eager manner via single query, but still I can access order.total just like before.

score 0 · Answer 1 · answered May 07 '15 at 22:06

Try this:

subquery = Order.joins(:items).select('orders.id, sum(items.price * items.quantity) AS total').group('orders.id')

@orders = Order.includes(:items).joins("INNER JOIN (#{subquery.to_sql}) totals ON totals.id = orders.id")

This will create a subquery that sums the total of the orders, and then you join that subquery to your other query.

score 0 · Answer 2 · answered Oct 08 '18 at 20:49

I wrote up two options for this in this blog post on using find_by_sql or joins to solve this.

For your example above, using find_by_sql you could write something like this:

Order.find_by_sql("select
  orders.id,
  SUM(items.price * items.quantity) as total
from orders 
join items
   on orders.id = items.order_id
group by 
  order.id")

Using joins, you could rewrite as:

Order.all.select("order.id, SUM(items.price * items.quantity) as total").joins(:items).group("order.id")

Include all the fields you want in your select list in both the select clause and the group by clause. Hope that helps!

How to eager load child model's sum value for ruby on rails?

2 Answers2