Foreign Key References to the Same Table

Questions & Help Questions
, ,
1

Hey everyone,

I’m having trouble trying to model relationships between multiple users. I’m trying to model a user_role that keeps track of a whether a given user is a mentee (someone being mentored) or a mentor. I then have a user_relationship that keeps track of the relationship between two users.

Migrations

create table(:user_roles) do
  ...  
  add :user_id, references(:users, on_delete: :delete_all), null: false
  ...
end

create table(:user_relationships) do
  ...  
  add :user_role_id_1, references(:user_roles, on_delete: :delete_all), null: false
  add :user_role_id_2, references(:user_roles, on_delete: :delete_all), null: false
  ...
end

Schemas

  schema "users" do
    ...
    has_many :user_roles, MyApp.Accounts.UserRole
    has_many :user_relationships, through: [:user_roles, :user_relationships]
    ...
  end

  schema "user_roles" do
    ...
    belongs_to :user, MyApp.Accounts.User
    has_many :relationships_as_mentee, MyApp.Accounts.UserRelationship, foreign_key: :mentee_user_role
    has_many :relationships_as_mentor, MyApp.Accounts.UserRelationship, foreign_key: :mentor_user_role
    ...
  end

  schema "user_relationships" do
    ...
    belongs_to :mentee_user_role_id, MyApp.Accounts.UserRole, foreign_key: :mentee_user_role
    belongs_to :mentor_user_role_id, MyApp.Accounts.UserRole, foreign_key: :mentor_user_role
    ...
  end

I’ve tried all sorts of iterations mostly based on the advice in this post, but still wasn’t able to figure this out. When I run my seeds.exs file and try to insert a new UserRelationship, I’m seeing the following:

** (RuntimeError) casting assocs with cast/4 for :mentee_user_role_id field is not supported, use cast_assoc/3 instead

Anyways, I haven’t been able to figure this out and would greatly appreciate some insight. Does anyone have any tips/suggestions? Thanks!