CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Umeumeume
Umeumeume

Reputation: 1988

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

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))

Upvotes: 2

Views: 2763

Answers (1)

AmitA
AmitA

Reputation: 3245

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:

  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:

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.

Upvotes: 3

Related Questions