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.