CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4
Vishal G
Vishal G

Reputation: 1531

Rails: How to remove n+1 query when we need to query association inside loop?

I have output as result in code having queries in it (only showing basic one here) So basically I need sum of the custom line items as well as all line items

results = Order.includes(:customer, :line_items).where('completed_at IS NOT NULL')

results.each do |result|
  custom_items_sum = result.line_items.where(line_item_type: 'custom').sum(:amount)
   total_sum = result.line_items.sum(:amount)
end

In this code, there is n+1 query issue, I have tried adding includes but for sure it is not going to work as we have another query inside the loop, Any help will be appreciated??

Upvotes: 1

Views: 835

Answers (4)

Holino
Holino

Reputation: 86

Try to use the scoping block. The following code generates very clean SQL queries. 

Order.includes(:line_items).where.not(completed_at: nil).scoping do
   @custom_items_sum = Order.where(line_items: { line_item_type: 'custom' })
                            .sum(:amount)
   @total_sum        = Order.sum(:amount)
end

There's not that much documentation about the scoping block but it scopes your model to the ActiveRecord requests made before (here : where('completed IS NOT NULL') and with the :line_items included).

Hope this helps! :)

Upvotes: 1

engineersmnky
engineersmnky

Reputation: 29308

Just because @AlekseiMatiushkin says write it in raw SQL let's do the same with rails 

order_table = Order.arel_table
line_items_table = LineItem.arel_table
custom_items = Arel::Table.new(:custom_items)
Order.select(
   order_table[Arel.star],
   line_items_table[:amount].sum.as('total_sum'),
   custom_items[:amount].sum.as('custom_items_sum')
).joins(
   order_table.join(line_items_table).on(
     line_items_table[:order_id].eq(order_table[:id])
   ).join(
      Arel::Nodes::As.new(line_items_table,:custom_items), 
      Arel::Nodes::OuterJoin
   ).on( 
      custom_items[:order_id].eq(order_table[:id]).and(
       custom_items[:line_item_type].eq('custom')
      ) 
   ).join_sources
).where(
   order_table[:completed_at].not_eq(nil)
).group(:id)

This will produce an ActiveRecord::Relation of Order objects with a virtual attributes of total_sum and custom_items_sum using the following query

SELECT 
  orders.*,
  SUM(line_items.amount) AS total_sum,
  SUM(custom_items.amount) As custom_items_sum
FROM 
  orders
  INNER JOIN line_items ON line_items.order_id = orders.id
  LEFT OUTER JOIN line_items AS custom_items ON custom_items.order_id = orders.id
    AND custom_items.line_item_type = 'custom'
WHERE 
  orders.completed_at IS NOT NULL
GROUP BY 
  orders.id

This should handle the request in a single query by using 2 joins to aggregate the needed data.

Upvotes: 1

Ursus
Ursus

Reputation: 30056

If you don't want to trigger other queries in the loop you need to avoid methods which work on relations and use that ones which work on collections. Try

custom_items_sum = result.line_items.
    select { |line_item| line_item.line_item_type == 'custom' }.
    sum(&:amount)

This should work without n+1 queries.

Note that it's possible to write just one query and avoid this computation anyway but that's beyond the scope of your question :)

Upvotes: 4

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 120990

Rails was never known to be robust enough as ORM. Use plain SQL instead:

results =
  Order.connection.execute <<-SQL
    SELECT order.id, SUM(line_items.amount)
    FROM orders
      JOIN line_items
      ON (line_items.order_id = orders.id)
    WHERE orders.completed_at IS NOT NULL
    GROUP BY orders.id
    HAVING line_items.line_item_type = 'custom'
  SQL

That way you’ll get all the intermediate sums in a single query, which is way faster than performing all the calculations in ruby.

Upvotes: 2

Related Questions