Query deeply nested relations in rails

We have a lot of through relations in a model. Rails correctly joins the relations, however I am struggling in figuring out how to apply a where search to the joined table using active record.

For instance:

class Model
  has_one :relation1
  has_one :relation2, through: :relation1
  has_one :relation3, through: :relation2
end

If all the relations are different models, we easily query using where. The issue arise rails starts aliasing the models.

For instance, Model.joins(:relation3).where(relation3: {name: "Hello"}) wont work, as no table is aliased relation3.

Is it possible using active record, or would I have to achieve it using arel or sql?

I am using rails 6.0.4.

Solution 1:

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.