Rails 6.1.1 allows `where` to reference associations via joined table alias name...

Rails 6.1 has added a feature wherein we can specify an alias name of the model in the where clause.

Let’s say we have an Employee model as below:

class Employee < ActiveRecord::Base
  has_many :subordinates, class_name: "Employee", foreign_key: :manager_id
  belongs_to :manager, class_name: "Employee"
end

Employees without a manager will have manager_id as nil.

Before

Let’s take a scenario where we need to fetch all employees whose manager name is Sam.

There can be two ways to fetch this data.

Employee.find_by(name: "Sam").subordinates
SELECT "employees".* FROM "employees" WHERE "employees"."name" = $1 LIMIT $2  [["name", "Sam"], ["LIMIT", 1]]
SELECT "employees".* FROM "employees" WHERE "employees"."manager_id" = $1 /* loading for inspect */ LIMIT $2  [["manager_id", "e4f2c753-6057-4952-9947-d1a543b2d1c7"], ["LIMIT", 11]]
=> #<ActiveRecord::AssociationRelation [#<Employee id: "3e584548......

Employee.joins(:manager).where(manager: { name: "Sam" })
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "manager")
LINE 1: ..._employees"."id" = "employees"."manager_id" WHERE "manager"....

The second query throws an error of missing FROM-clause entry.

It expects manager to reference the employees table, which Rails was not able to figure out.

After

To fix the above issue, Rails 6.1.1 added a fix that allows the alias name to be used as a reference for the employees table in the where clause.

Employee.find_by(name: "Sam").subordinates
SELECT "employees".* FROM "employees" WHERE "employees"."name" = $1 LIMIT $2  [["name", "Sam"], ["LIMIT", 1]]
SELECT "employees".* FROM "employees" WHERE "employees"."manager_id" = $1 /* loading for inspect */ LIMIT $2  [["manager_id", "e4f2c753-6057-4952-9947-d1a543b2d1c7"], ["LIMIT", 11]]
=> #<ActiveRecord::AssociationRelation [#<Employee id: "3e584548......

Employee.joins(:manager).where(manager: { name: "Sam" })
SELECT "employees".* FROM "employees" INNER JOIN "employees" manager ON manager."id" = "employees"."manager_id" WHERE "manager"."name" = $1 /* loading for inspect */ LIMIT $2  [["name", "Sam"], ["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Employee id: "3e584548......

This use-case is mostly useful for self-referential models and aliased tables.