Many-to-many association with multiple self-joins in ActiveRecord

Question

I am trying to implement multiple relations between records of the same model via self-joins (based on @Shtééf's answer). I have the following models

create_table :relations, force: true do |t|
  t.references :employee_a
  t.string     :rel_type
  t.references :employee_b
end

class Relation < ActiveRecord::Base
  belongs_to :employee_a, :class_name => 'Employee'
  belongs_to :employee_b, :class_name => 'Employee'
end

class Employee < ActiveRecord::Base
  has_many :relations, foreign_key: 'employee_a_id'
  has_many :reverse_relations, class_name: 'Relation', foreign_key: 'employee_b_id'

  has_many :subordinates, through: :relations, source: 'employee_b', conditions: {'relations.rel_type' => 'manager of'}
  has_many :managers, through: :reverse_relations, source: 'employee_a', conditions: {'relations.rel_type' => 'manager of'}
end

With this setup I can successfully access the lists of subordinates and managers for each record. However, I have difficulties to create relations in the following way

e = Employee.create
e.subordinates.create
e.subordinates #=> []
e.managers.create
e.managers #=> []

The problem is that it does not set type of relations, so I have to write

e = Employee.create
s = Employee.create
e.relations.create employee_b: s, rel_type: 'manager of'
e.subordinates #=> [#<Employee id:...>]

Am I doing something wrong?

score 8 · Accepted Answer · edited May 23 '17 at 11:44

You can use before_add and before_remove callback on the has_many association :

class Employee < ActiveRecord::Base
  has_many :relations, foreign_key: 'employee_a_id'
  has_many :reverse_relations, class_name: 'Relation', foreign_key: 'employee_b_id'

  has_many :subordinates, 
           through: :relations, 
           source: 'employee_b', 
           conditions: {'relations.rel_type' => 'manager of'}
           :before_add => Proc.new { |employe,subordinate| employe.relations.create(employe_b: subordinate, rel_type: 'manager of') },
           :before_remove => Proc.new { |employe,subordinate| employe.relations.where(employe_b: subordinate, rel_type: 'manager of').first.destroy }

  has_many :managers,  
           through: :reverse_relations, 
           source: 'employee_a', 
           conditions: {'relations.rel_type' => 'manager of'}
           :before_add => Proc.new { |employe,manager| employe.reverse_relations.create(employe_a: manager, rel_type: 'manager of') },
           :before_remove => Proc.new { |employe,manager| employe.reverse_relations.where(employe_b: subordinate, rel_type: 'manager of').first.destroy }

This should works and make you able to use employe.managers.create
You may want to use build instread of create in the callback
Also you can read this question about this solution

score 3 · Answer 2 · answered Jun 21 '11 at 14:07

In order to create a multiple many-to-many self-join association, I would recommend that it might make more sense to have multiple tables to manage the connection. That way it's very clear from a data standpoint as to exactly what is going on, and it's also clear from a logic standpoint. So something along these lines:

create_table :manage_relation do |t|
  t.references :employee_id
  t.references :manager_id
end
create_table :subordinate_relation do |t|
  t.references :employee_id
  t.references :subordinate_id
end

class Employee < ActiveRecord::Base

  has_many :subordinates, 
           :through => :subordinate_relation, 
           :class_name => "Employee", 
           :foreign_key => "subordinate_id"
  has_many :managers, 
           :through => :manage_relation, 
           :class_name => "Employee", 
           :foreign_key => "manager_id"

  belongs_to :employee, 
             :class_name => "Employee"
end

This way it doesn't get any more convoluted than necessary from a coding standpoint, and you can access it using the standard collections and it will appropriately set up your connections for you without you having to manage them. So, both of these collections should work..

employee.managers
employee.subordinates

And you could not have to manage any other variables. Make sense? It adds a table, but improves clarity.

And I'm not entirely certain that belongs_to is necessary, but I didn't have time to set up the full exercise on my local install. You could try it with and without and see what kind of results you get. — Christopher WJ Rueber, Jun 21 '11 at 14:08
Also, if you prefer to go down this route, you should check out this question. It shows more along the lines of what you were doing, though I still find the clarity to be a little lacking myself (fyi- the question is answered in the question): http://stackoverflow.com/questions/6426383/rails-associations-using-data-on-the-association-table — Christopher WJ Rueber, Jun 21 '11 at 15:45

Harish Shetty · Answer 3 · 2011-06-22T05:16:46.620

I would redo your models as follows:

class ManagerRelation < ActiveRecord::Base
  belongs_to :manager, :class_name => 'Employee'
  belongs_to :subordinate, :class_name => 'Employee'
end

class Employee < ActiveRecord::Base
  has_many :manager_relations,     :class_name => "ManagerRelation",
               :foreign_key => :subordinate_id
  has_many :subordinate_relations, :class_name => "ManagerRelation", 
               :foreign_key => :manager_id

  has_many :managers,     :source => :manager,     
               :through => :manager_relations

  has_many :subordinates, :source => :subordinate, 
               :through => :subordinate_relations
end

Now you can do the following:

employee.managers
employee.subordinates    
employee.managers << employee2    
employee.subordinates << employee3

Note: It is usually a sign for one to leave the company when they are made to report to two managers :-)

Thanks for your answer. I was trying to avoid additional tables for every relation type, but it is probably the best way of doing this. — Andrei, Jun 28 '11 at 16:37

tamersalama · Answer 4 · 2011-06-25T07:06:23.587

Given the presented relation

create_table :relations, force: true do |t|
  t.references :employee_a
  t.string     :rel_type
  t.references :employee_b
end


class Employee < ActiveRecord::Base

  has_many :subordinate_relations, :class_name => "Relation", :conditions => {:rel_type => 'manager of'}, :foreign_key => :employee_a
  has_many :subordinates, :through => :subordinate_relations, :source => :subordinate, :foreign_key => :employee_b

  has_many :manager_relations, :class_name => "Relation", :conditions => {:rel_type => 'manager of'}, :foreign_key => :employee_b
  has_many :managers, :through => :manager_relations, :source => :manager, :foreign_key => :employee_a

end


class Relation < ActiveRecord::Base

  belongs_to :manager, :class_name => "Employee", :foreign_key => :employee_a
  belongs_to :subordinate, :class_name => "Employee", :foreign_key => :employee_b

end


e = Employee.create
e.subordinates.create #Employee ...
e.subordinates #[<Employee ...]

e2 = Employee.create
e2.managers.create #Employee
e2.managers #[<Employee ...]

Although the solution works - I'm a bit confused by tying the associations with "rel_type". In this case - I'd say the rel_type is redundant and the relation should be mapped as follows:

create_table :relations do |t|
    t.reference :manager
    t.reference :subordinate
end

In such case, the association mapping should be a tad simpler.

Many-to-many association with multiple self-joins in ActiveRecord

4 Answers4