Eager-load for custom joins in ActiveRecord

Question

I have a table of restaurants that have many reservations. Now I would like to query all restaurants and in case they have reservations for 2 people it should preload these associations. In case there are no reservations for 2 it should still return the restaurants, but with an empty association.

For that I tried the following query:

Restaurant.eager_load(:reservations).where("number_of_people = ?", 2)

However, this doesn't work, since it discards all restaurants that do have reservations but none of them is for 2 people.

So what I would like to do is moving that condition into the join condition. Something like:

Restaurant.joins('LEFT OUTER JOIN \"reservations\" ON \"reservations\".\"restaurant_id\" = \"restaurants\".\"id\" AND \"reservations\".\"number_of_people\" = ?', 2)

That would give the result I need, however this is not preloading the "reservations" associations but causing an N+1 issue.

It doesn't seem like eager_load accepts custom queries. I found these threads: https://github.com/rails/rails/issues/12270 and JOIN ON ... AND conditions when eager loading association in ActiveRecord but there is no solution offered.

Answer 1

I think Deepak is right. Try this:

 #retaurant.rb
 has_many :reservations_for_two, ->{number_of_people: 2}, class_name: 'Reservation'

And then:

  Restaurant.preload(:reservations_for_two) #(for two separate queries)

OR

   Restaurant.eager_load(:reservations_for_two) #(one join query)

Answer 2

Try Restaurant.joins(:reservations).includes(:reservations).where("reservations.number_of_people = ?", 2)