6

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.

Community
  • 1
  • 1
Robert Strobl
  • 295
  • 2
  • 12

2 Answers2

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)
Joel Blum
  • 7,750
  • 10
  • 41
  • 60
  • I would need this to be dynamic though, where I can specify the `number_of_people` as an argument instead of hardcoding it – Robert Strobl Jan 09 '17 at 08:07
1

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

ClassyPimp
  • 715
  • 7
  • 20
  • 1
    This also only creates a "WHERE" statement and doesn't put it into the "ON" condition. – Robert Strobl Jan 09 '17 at 08:06
  • 1
    Ok I got your question. The second answer is right, as of hard coding the value one dirty hack is to create an class << self level attr_accessor on your model class and assign value before making query like `has_many :reservations_for_x, ->{number_of_people: Restaurant.number_of_people_to_query}, class_name: 'Reservation'` and before eager loading assign your criteria to class attribute. – ClassyPimp Jan 09 '17 at 08:52
  • That works, but only in a single threaded environment. I am concerned about race conditions given that this uses a class variable. – Robert Strobl Jan 09 '17 at 15:45
  • Didn't test, but it appears that class level instance variables are thread safe (http://stackoverflow.com/questions/9558192/thread-safety-class-variables-in-ruby/9558452#9558452). Alternatively you can use `Thread.current[:number_of_people_to_query] = 2`, and define getter and setter on your class for it. – ClassyPimp Jan 10 '17 at 09:50