I use MyTable.eager_load(:myjointable) as part of a query that fetches many rows. Now I want to specify a condition in the join clause created by eager_load.
I don’t want it to be in the where clause, for performance reasons.
Specifying a condition in the join can be done with custom SQL, e.g. joins("left outer join myjointable on mytable.id = myjointable.mytable_id AND myjointable.created_at > ...".
Unfortunately if I do this ActiveRecord forgets about the columns on myjointable, and I am back to N+1 queries when I try to use mytableresults.first.myjointable.a_column.
If I add select("mytable.*, myjointable.*") Rails doesn’t know how to instantiate the MyTable models and I get:
ActionView::Template::Error (No route matches {:action=>”show”, :controller=>”admin/mytables”, :id=>#}, possible unmatched constraints: [:id])
I see this question, where a static condition is applied as follows:
class Parent
has_many :children
has_many :grades, through: :children
has_many :highest_grade, ->{where(level: 'A+')}, class_name: "Grade", through: :children
end
Parent.eager_load(:highest_grades)
This is almost what I want (it looks like Rails ends up moving the where clause into a join when passing the association to eager_loads):
# 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` = 'A+'...
This is perfect except I need to be able to specify the criteria on the association dynamically.
How can I have:
- eager loading
- a condition on the join clause, not the where clause
- the condition specified dynamically
- no confusion from ActiveRecord about what columns to load into memory
- not explicit listing every single column
I don’t mind Arel or custom SQL, first prize is to just have small amounts that I can mix into normal ActiveRecord.
5