rails eager load has_many through join table

Question

My development environment is on Rails 4.1 and postgresql

I've 3 models with has_many through relationship:

class Item < ActiveRecord::Base
  has_many :item_parts
  has_many :parts, through: :item_parts
end

class Part < ActiveRecord::Base
  has_many :item_parts
  has_many :items, through: :item_parts
end

class ItemPart < ActiveRecord::Base
  belongs_to :item
  belongs_to :part 
end

The item_parts join table has a unit_count attribute to keep track of how many parts the item contains.

When in the view I iterate through all parts of a specific item I also need to get the unit_count value from the join table.

This give me the n+1 query issue.

I tried to eager load the join table:

item.parts.includes(:item_parts)

ItemPart Load (0.5ms)  SELECT "item_parts".* FROM "item_parts" WHERE "item_parts"."part_id" IN (24, 12, 3, 26)

but when after I do:

part.item_parts.where(item_id: item.id).first.unit_count

I got following sql query for every part; the eager loading didn't work

ItemPart Load (0.5ms)  SELECT "item_parts".* FROM "item_parts" WHERE "item_parts"."part_id" = $1 AND "item_parts"."item_id" = $2  [["part_id", 24], ["item_id", 18]]

Any suggestions?

Answer 1

I expect this is because you are using the "count" method which, according to:

http://dev.mensfeld.pl/2014/09/activerecord-count-vs-length-vs-size-and-what-will-happen-if-you-use-it-the-way-you-shouldnt/

"is not stored internally during object life cycle, which means, that each time we invoke this method, SQL query is performed again"

Try using .length or .size methods instead.

Note: (to quote the article)

length – collection.length

• Returns length of a collecion without performing additional queries… as long as collection is loaded
• When we have lazy loaded collection, length will load whole colletion into memory and then will return length of it
• Might use all of your memory when used in a bad way
• Really fast when having a eagerly loaded collection

size – collection.size

• Combines abilities of both previous methods;
• If collection is loaded, will count it’s elements (no additional query)
• If collection is not loaded, will perform additional query

Answer 2

Having looked again, I'm not sure why you want to add the count to the join table.

@items = Item.includes(:parts)

@items.each do |item|
   parts_count_per_item = item.parts.size
end

or

@parts = Part.includes(:items)

@parts.each do |part|
   items_count_per_part = part.items.size
end

Answer 3

Following this link I found the solution for my case: Rails Eager Loading and where clause

I didn't known the Active Record Query Method: Select (http://apidock.com/rails/v4.1.8/ActiveRecord/QueryMethods/select)

It seems that I wasn't able to eager load the join table because after includes the item_parts I use the where clause during the iteration of the item parts.

It seems that the where clause make a new query to the db every time it is used during the iteration.

Instead select method doesn't hit the db every time but works on the Active Record collection loaded into memory.

So I changed this:

item.parts.includes(:item_parts)
part.item_parts.where(item_id: item.id).first.unit_count

with this:

item.parts.includes(:item_parts)
part.item_parts.select{|item_part| item_part.item_id == item.id}.first.unit_count

and then I have a single query to load item_parts:

ItemPart Load (0.5ms)  SELECT "item_parts".* FROM "item_parts" WHERE "item_parts"."part_id" IN (24, 12, 3, 26)