Non Umemoto Non Umemoto - 2 years ago 81
Ruby Question

Ruby on Rails where query with relations

I am trying to use where query with relationships.

How can I query using where with relations in this case?

This is model

has_many :projects
has_many :reasons, through: :projects

belongs_to :user
has_many :reasons

belongs_to :project

This is the codes which doesn't work

# GET /reasons
def index
reasons = current_user.reasons
updated_at = params[:updated_at]

# Filter with updated_at for reloading from mobile app
if updated_at.present?

# This one doesn't work!!!!!!!!!!!!
reasons = reasons.includes(:projects).where("updated_at > ?",

# Get all non deleted objects when logging in from mobile app
reasons = reasons.where(deleted: false)

render json: reasons


This is correct thanks to @AmitA.

reasons = reasons.joins(:project).where("projects.updated_at > ?",

Answer Source

If you want to query all reasons whose projects have some constraints, you need to use joins instead of includes:

reasons = reasons.joins(:project).where("projects.updated_at > ?",

Note that when both includes and joins receive a symbol they look for association with that precise name. That's why you can't actually do includes(:projects), but must do includes(:project) or joins(:project).

Also note that the constraints on joined tables specified by where must refer to the table name, not the association name. That's why I used projects.updated_at (in plural) rather than anything else. In other words, when calling the where method you are in "SQL domain".

There is a difference between includes and joins. includes runs a separate query to load the dependents, and then populates them into the fetched active record objects. So:

reasons = Reason.where('id IN (1, 2, 3)').includes(:project)

Will do the following:

  1. Run the query SELECT * FROM reasons WHERE id IN (1,2,3), and construct the ActiveRecord objects Reason for each record.
  2. Look into each reason fetched and extract its project_id. Let's say these are 11,12,13. Then run the query SELECT * FROM projects WHERE id IN (11,12,13) and construct the ActiveRecord objects Project for each record.
  3. Pre-populate the project association of each Reason ActiveRecord object fetched in step 1.

The last step above means you can then safely do:


And no query will be initiated to fetch the project of the first reason. This is why includes is used to solve N+1 queries. However, note that no JOIN clauses happen in the SQLs - they are separate SQLs. So you cannot add SQL constraints when you use includes.

That's where joins comes in. It simply joins the tables so that you can add where constraints on the joined tables. However, it does not pre-populate the associations for you. In fact, Reason.joins(:project), will never instantiate Project ActiveRecord objects.

If you want to do both joins and includes, you can use a third method called eager_load. You can read more about the differences here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download