Ruby on Rails Where Query with Nesting Relations

Query deeply nested relations in rails

In a simple query where a table is only referenced once there is no alias and the table name is just used:

irb(main):023:0> puts City.joins(:country).where(countries: { name: 'Portugal'})
City Load (0.7ms)  SELECT "cities".* FROM "cities" INNER JOIN "regions" ON "regions"."id" = "cities"."region_id" INNER JOIN "countries" ON "countries"."id" = "regions"."country_id" WHERE "countries"."name" = $1  [["name", "Portugal"]] 

In a more complex scenario where a table is referenced more then once the scheme seems to be association_name_table_name and association_name_table_name_join.

class Pet < ApplicationRecord
  has_many :parenthoods_as_parent, 
    class_name: 'Parenthood',
    foreign_key: :parent_id
  has_many :parenthoods_as_child, 
    class_name: 'Parenthood',
    foreign_key: :child_id
  has_many :parents, through: :parenthoods_as_child
  has_many :children, through: :parenthoods_as_child
end

class Parenthood < ApplicationRecord
  belongs_to :parent, class_name: 'Pet'
  belongs_to :child, class_name: 'Pet'
end

irb(main):014:0> puts Pet.joins(:parents, :children).to_sql
# auto-formatted edited for readibility
SELECT "pets".*
FROM   "pets"
       INNER JOIN "parenthoods"
               ON "parenthoods"."child_id" = "pets"."id"
       INNER JOIN "pets" "parents_pets"
               ON "parents_pets"."id" = "parenthoods"."parent_id"
       INNER JOIN "parenthoods" "parenthoods_as_children_pets_join"
               ON "parenthoods_as_children_pets_join"."child_id" = "pets"."id"
       INNER JOIN "pets" "children_pets"
               ON "children_pets"."id" =
"parenthoods_as_children_pets_join"."child_id" 

For more advanced queries you often need to write your own joins with Arel or strings if you need to reliably know the aliases used.

Ruby on Rails where query with nesting relations

First, you're referencing reasons in a very haphazard way. User has_many reasons through projects, so why not go that route?

current_user.joins(:reasons).where(reasons.updated_at > ?", updated_at)

Secondly, and more specific to your error: your relation definition has_many :summaries, through: :projects, source: :reasons seems to be broken since projects don't have any summaries.

Consider adding a has_many :summaries, through: :reasons to your Project model, then use has_many :summaries, through: :projects in User.

Rails how to query nested association returning all nested associations

This should work for you:

Receipt.joins(invoice: { order: :user }).where(users: { id: user_id })

Yes, you need to use joins to generate a proper query (join all tables along the way and add your user restriction).

Ruby on Rails where query with relations

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.

ActiveRecord conditional query with nesting

Considering that extra_data as a JSONB or JSON column, you can do something like below

Model.where("CAST(extra_data ->> 'users_count' AS NUMERIC) > ?", 1000)

Postgres JSON operator ->> will return the result in the form of text and converting the value as an integer and compare with the value.

Reference - https://www.postgresql.org/docs/9.5/functions-json.html
https://edgeguides.rubyonrails.org/active_record_postgresql.html#json-and-jsonb

Query records with nested relationships 2-levels deep

I figured it out:

First, add this relationship to the Project model:

has_many :task_assignments, through: :tasks

Then use this query:

Project.includes(:task_assignments).where("task_assignments.user_id = #{user_id}").references(:task_assignments)

Joining Nested Associations (Multiple Level)

You can joins the relations together and apply where clause on the joined relations:

Admin::Accbook
  .joins(purchase: :offer)
  .where(offers: { seller_id: 123 })

---

A thing to know, where uses the DB table's name. joins (and includes, eager_load, etc) uses the relation name. This is why we have:

Admin::Accbook
  .joins(purchase: :offer)
  #                 ^^^^^ relation name
  .where(offers: { seller_id: 123 })
  #      ^^^^^^ table name

---

Related Topics