Ruby on Rails where query with relations

Question

I am trying to use where query with relationships.

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

This is model

User
  has_many :projects
  has_many :reasons, through: :projects

Project
  belongs_to :user
  has_many :reasons

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 > ?", Time.at(updated_at.to_i))



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

    render json: reasons
  end

---Update---

This is correct thanks to @AmitA.

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

AmitA · Accepted Answer · 2016-04-30 01:48:39Z

3

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 > ?", Time.at(updated_at.to_i))

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:

Run the query SELECT * FROM reasons WHERE id IN (1,2,3), and construct the ActiveRecord objects Reason for each record.
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.
Pre-populate the project association of each Reason ActiveRecord object fetched in step 1.

The last step above means you can then safely do:

reasons.first.project

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.

edited Apr 30, 2016 at 1:48

answered Apr 30, 2016 at 1:18

AmitA

3,2451 gold badge27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Bezek Over a year ago

Pretty good explanation. I did just want to add on, it is possible to do a join using includes, and it doesn't always do an extra query to solve N + 1 queries. If you use includes and then add a nested condition in the where that filters based off the included table, you will get a left outer join. guides.rubyonrails.org/…

Umeumeume Over a year ago

Thank you very much for your great answer. I also tried it with nesting relations, and some part worked, but some part didn't work. Would you mind to see this related question? @AmitA stackoverflow.com/questions/36952179/…

Collectives™ on Stack Overflow

Ruby on Rails where query with relations

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related