How do i specify conditions in my "includes" clause of my Rails query?

Question

I'm using Rails 5. I have this model

class MyObjectTime < ActiveRecord::Base
    ...
  has_many :user_my_object_time_matches

and then in my controller I have this for querying some of that model

  @results = MyObjectTime.joins(:race,
                            "LEFT JOIN user_my_object_time_matches on my_object_times.id = user_my_object_time_matches.my_object_time_id #{additional_left_join_clause}")
                     .where( search_criteria.join(' and '), *search_values )
                     .limit(1000) 
                     .order("my_objects.day DESC, my_objects.name")
                     .paginate(:page => params[:page])
                     .includes(:user_my_object_time_matches)

My question is, although I want all the MyObjectTime objects returned, subject to certain criteria, I don't want all of the "user_my_object_time_objects" attached to each model, only ones satisfying certain criteria (e.g. those whose user_my_object_time_match.user_id field is null or equal to "30"). How do I specify criteria to force only certain user_my_object_time_matches included for each of my MyObjectTime models?

Viktor Nonov · Accepted Answer · 2017-03-07T19:01:08.123

includes is used for eager loading, so I assume you want load MyObjectTime records along with user_my_object_time_matches associations, so you don't have to query the database anymore.

The way that includes work is this:

MyObjectTime.where(search_criteria.join(' and '))includes(:user_my_object_time_matches)

That would query the database twice:

load all MyObjectTime records, subject to the search_criteria
```
SELECT * FROM my_object_times WHERE (....)
```

load all associated UserMyObjectTimeMachine records

SELECT * FROM user_my_object_time_machines WHERE my_object_time_id IN ( [ids returned by the previous query ])

So if you want to add additional filtering on the second query, then in the model MyObjectTime you can add another association:

class MyObjectTime < ActiveRecord::Base
  ...
  has_many :user_my_object_time_matches
  has_many :user_my_object_time_matches_with_null_user, -> { where("user_id IS NULL or user_id = 30") }, class_name: "UserMyObjectMatch"

and then when you call

MyObjectTime.where(search_criteria.join(' and ')).includes(:user_my_object_time_matches_with_null_user)

the second query will look like this:

SELECT * FROM user_my_object_time_machines WHERE (user_id IS NULL OR user_id = 30) AND my_object_time_id IN ( [ids returned by the previous query ])

With that your example would look like this:

@results = MyObjectTime.joins(:race)
                       .where( search_criteria.join(' and '), *search_values )
                       .limit(1000) 
                       .order("my_objects.day DESC, my_objects.name")
                       .paginate(:page => params[:page])
                       .includes(:user_my_object_time_matches_with_null_user)

With no eager loading

If the condition is not the same everytime (user_id IS NULL OR user_id = 30), then I don't know a good way to eager load everything. So you can just use normal left join, but then query would be performed when you try to get the association records:

 @results = MyObjectTime.joins(:race,
                            "LEFT JOIN user_my_object_time_matches on my_object_times.id = user_my_object_time_matches.my_object_time_id AND (user_my_object_time_matches.user_id IS NULL or user_my_object_time_matches.user_id = 30)")
                     .where( search_criteria.join(' and '), *search_values )
                     .limit(1000) 
                     .order("my_objects.day DESC, my_objects.name")
                     .paginate(:page => params[:page])
                     .includes(:user_my_object_time_matches)

You can find good chunk of documentation in the Eager Loading of associations here:

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

This fundamentally changes the results that are being returned, right? that's not what I want. In your query, I will only get results in which "MyObjectTime" is linked to a record taht has "user_my_object_time_matches.user_id IS NULL OR user_my_object_time_matches.user_id = 30" but I only wish to remove those results from the objects taht are returned, not block the parent object from being returned altogether. — Dave, Mar 06 '17 at 20:43
Yes, you are right - it will change the result. Let me edit the answer — Viktor Nonov, Mar 07 '17 at 14:41
Awesome, so this definitely gets me closer. The last thing is in your answer you have hard-coded "30" due to what I listed in my example. In real life, the value I want to screen by will be the id of the user in my session, e.g. "current_user.id". Is there a way to pass a parameter to the "user_my_object_time_matches_with_null_user" you have in the MyObjectTime model? — Dave, Mar 07 '17 at 15:34
I see. I don't know of a way to pass a parameter (current_user in your case) to the has_many scope through **includes** method, but as an option I see you making the current_user accessible in the model. You use Rails 5, so check this answer for how to do it: http://stackoverflow.com/questions/2513383/access-current-user-in-model#2513456 and then you would be able to change the scope to: `has_many :user_my_object_time_matches_with_null_user, -> { where("user_id IS NULL or user_id = ?", Current.user.id) }` — Viktor Nonov, Mar 07 '17 at 16:12
Great link. I believe I have followed everything from your example, but I'm noticing taht when I try and access that field, 'puts "first: #{@results[0].user_my_object_time_matches_with_null_user}"', I get teh error, "uninitialized constant MyObjectTime::UserMyObjectTimeMatchesWithNullUser". — Dave, Mar 07 '17 at 17:41
Sorry, I missed very important detail in my answer - there should class_name attribute in the has_many relationship: `has_many :user_my_object_time_matches_with_null_user, -> { where("user_id IS NULL or user_id = 30") }, class_name: "UserMyObjectTimeMatch"`. The reason behind it is to show Rails which class to use. Will edit the answer too. — Viktor Nonov, Mar 07 '17 at 19:00

How do i specify conditions in my "includes" clause of my Rails query?

1 Answers1