Self-Join in Rails
In designing a data model, you will sometimes find a model that should have a relation to itself. For example, you may want to store all employees in a single database model, but be able to trace relationships such as between manager and subordinates. This situation can be modeled with self-joining associations.
How employees are related to themselves:
- An employee may report to another employee (supervisor).
- An employee may supervise himself (i.e. zero) to many employees (subordinates).
A self-join is a join in which a table is joined with itself using a FOREIGN KEY which references its own PRIMARY KEY. This can be viewed as a join of two copies of the same table.
Let’s take a closer look at this from the SQL perspective. The syntax of the command for joining a table to itself is almost the same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. Table name aliases are defined in the FROM clause of the SELECT statement.
SELECT a.name AS 'employee', b.name AS 'manager'
FROM Employees a, Employees b
WHERE a.manager_id = b.id;
In the EMPLOYEE table displayed above, EMP_ID is the primary key. EMP_SUPV is the foreign key (this is the supervisor’s employee id).
Below is the model of Employee class in my Rails application. Three additional models are title, department, and location. Notice that Employee has many ‘subordinates’ and belongs to a ‘manager’. Both are logical names referencing each other within the same ‘Employee’ class. A FOREIGN KEY to ‘manager_id’ is mapping back to the ‘employees’ table. This is a one-to-many relationship association.
class Employee < ApplicationRecord
has_many :subordinates, class_name: "Employee",
foreign_key: "manager_id"
belongs_to :title
belongs_to :department
belongs_to :location
belongs_to :manager, class_name: "Employee", optional: true
end
In essence, a self-join is useful for comparing rows within a table or querying hierarchical data.
