How to properly set association to a self junction table?

1

Hi there,
I’m trying to figure out my association setting. Here is a MWE :
A users table, and a mentoring table. Each user can mentor any number of other users (mentees). And each mentee could be mentored by any number of other users (mentors).

  • Migration :
ROM::SQL.migration do
  change do
    create_table :users do
      primary_key :id
      column :name, String, null: false
    end

    create_table :mentoring do
      primary_key :id
      foreign_key :mentor_id, :users, null: false # eventually here unique: true if only one mentor allowed
      foreign_key :mentee_id, :users, null: false
    end
  end
end
  • seeds :
users : [
 {:id=>1, :name=>"Alice"},
 {:id=>2, :name=>"Bob"},
 {:id=>3, :name=>"Chuck"},
 {:id=>4, :name=>"Doug"},
 {:id=>5, :name=>"Erny"}
]
mentoring : [
 {:id=>1, :mentor_id=>1, :mentee_id=>2},
 {:id=>2, :mentor_id=>1, :mentee_id=>3},
 {:id=>3, :mentor_id=>2, :mentee_id=>3},
 {:id=>4, :mentor_id=>3, :mentee_id=>4},
 {:id=>5, :mentor_id=>2, :mentee_id=>5}
]
  • relations :
schema :users, infer: true do
  associations do
    has_many :mentees, through: :mentoring, foreign_key: :mentor_id, relation: :users, as: :mentees
    has_many :mentors, through: :mentoring, foreign_key: :mentee_id, relation: :users, as: :mentors
  end
end

schema :mentoring, infer: true do
  associations do
    belongs_to :mentee, foreign_key: :mentee_id, relation: :users
    belongs_to :mentor, foreign_key: :mentor_id, relation: :users # this seems to fail ! 
  end
end

Then :

  • users.combine(:mentees).to_a leads to the expected result :
    [
 {:id=>1, :name=>"Alice", :mentees=> [{:id=>2, :name=>"Bob", :mentor_id=>1}, {:id=>3, :name=>"Chuck", :mentor_id=>1}]},
 {:id=>2, :name=>"Bob", :mentees=> [{:id=>3, :name=>"Chuck", :mentor_id=>2}, {:id=>5, :name=>"Erny", :mentor_id=>2}]},
 {:id=>3, :name=>"Chuck", :mentees=>[{:id=>4, :name=>"Doug", :mentor_id=>3}]},
 {:id=>4, :name=>"Doug", :mentees=>[]},
 {:id=>5, :name=>"Erny", :mentees=>[]}
]
  • users.combine(:mentors).to_a leads to a curious result. Mentor numbers are correct but their identity is “replaced by” meetee identity. I think this is because only one belongs_ to statement (the first) is taken into account. Commenting out this first belongs_to statement leads to things changed : I can obtain mentors but not mentees.
    [
 {:id=>1, :name=>"Alice", :mentors=>[]},
 {:id=>2, :name=>"Bob", :mentors=>[{:id=>2, :name=>"Bob", :mentee_id=>2}]},
 {:id=>3, :name=>"Chuck", :mentors=> [{:id=>3, :name=>"Chuck", :mentee_id=>3}, {:id=>3, :name=>"Chuck", :mentee_id=>3}]},
 {:id=>4, :name=>"Doug", :mentors=>[{:id=>4, :name=>"Doug", :mentee_id=>4}]},
 {:id=>5, :name=>"Erny", :mentors=>[{:id=>5, :name=>"Erny", :mentee_id=>5}]}
]

Anyone to help me fixing this ? Any advice/guide on relation options usage (:source, :target, :relation, etc.) ?

Thanks !

2

The night was good for me. The solution is to tie 2 relations to the mentoring table, correctly aliased. See :

# relations/user_mentees.rb
schema :mentoring, as: :user_mentees, infer: true do
  associations do
    belongs_to :mentee, foreign_key: :mentee_id, relation: :users
  end
end

# relations/user_mentors.rb
schema :mentoring, as: :user_mentors, infer: true do
  associations do
    belongs_to :mentor, foreign_key: :mentor_id, relation: :users
  end
end

then,

# relations/users.rb
schema :users, infer: true do
  associations do
    has_many :mentees,
      through: :user_mentees,
      foreign_key: :mentor_id,
      relation: :users,
      as: :mentees

    has_many :mentors,
      through: :user_mentors,
      foreign_key: :mentee_id,
      relation: :users,
      as: :mentors
  end
end

I now enjoy seeing users.combine(:mentees).to_a together with users.where(name: 'Bob').combine(:mentees).to_a in one hand, and users.combine(:mentors).to_a together with users.where(name: 'Bob').combine(:mentors).to_a in another hand, working like a charm.