JOIN ON ... AND conditions when eager loading association in ActiveRecord

Question

Please notice, this is not a question about adding a WHERE condition to an association but a rather advanced question about how to alter the JOIN clause when using eager_load.

Lets say I have these models

class Parent
  has_many :children
  have_many :grades, through: :children
end

class Child
  belongs_to :parent
end

class Grade
  belongs_to :child
end

So to eager load the parents I would do:

Parent.eager_load(:grades)

But if I wanted all the parents - but only to eager load the highest scoring like so:

LEFT OUTER JOIN grades ON grades.child_id = children.id AND grades.level = 'A+'

I have tried using includes(:children).joins("LEFT OUTER JOIN grades ON grades.child_id = children.id AND grades.level = 'A+'") but since Rails does not build the association objects it will cause an extra query for each parent.

I have not found any references on using eager_load with a custom SQL string and have dug through the source without getting any wiser.

This is not for an actual app but rather out of curiosity if it is possible. — max, Nov 15 '15 at 03:45

score 5 · Accepted Answer · edited Dec 22 '21 at 14:30

If I am right, you only want to eager load the subset of the associations. Well, in order to do so, you need to outsmart the rails . You can basically create another association such as "highest_grade" to fetch the grades with level A+ using a where clause inside the ruby lambda. This can be achieved by using the following snippet of code

class Parent
  has_many :children
  has_many :grades, through: :children
  has_many :highest_grade, ->{where(level: 'A+')}, class_name: "Grade", through: :children
end

Now you need to eager load the highest_grade association.

 Parent.eager_load(:highest_grades)

This will essentially load all the parents along with only those grades whose level is A+.

It generates the following query.

 SQL (0.3ms)  SELECT `parents`.`id` AS t0_r0, `parents`.`created_at` AS    t0_r1, `parents`.`updated_at` AS t0_r2, `grades`.`id` AS t1_r0,    `grades`.`child_id` AS t1_r1, `grades`.`created_at` AS t1_r2, `grades`.`updated_at` AS t1_r3, `grades`.`level` AS t1_r4 FROM `parents` LEFT OUTER JOIN `children` ON `children`.`parent_id` = `parents`.`id` LEFT OUTER JOIN `grades` ON `grades`.`child_id` = `children`.`id` AND `grades`.`level` = '

If you want to understand how this works. Please use this link as a reference

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

EDIT

This does not solve the problem stated.

You cannot alter the JOIN generated by calling eager_load like you want. The WHERE clause is your only option, and it has the exact same effect as the condition on your JOIN. Here it is, even if you didn't want it:

Parent.eager_load(:grades).where("grades.level = 'A+'")

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 15 '15 at 10:16

Wizard of Ogz

12,543
2
41
43

Nope. The where clause applies to the whole query so it would not include the Parents without a a+ grade. Thats why Im using a LEFT OUTER JOIN. – max Nov 15 '15 at 10:19
Hmm, i'll have to try that one out. Thanks – max Nov 15 '15 at 10:23
Sorry, I deleted my last comment because I realized it still didn't accomplish what you need – Wizard of Ogz Nov 15 '15 at 10:25
It looks impossible to do. I'd love to know if you figure it out. – Wizard of Ogz Nov 15 '15 at 10:35

JOIN ON ... AND conditions when eager loading association in ActiveRecord

2 Answers2

EDIT

Linked