Foreign Key References to the Same Table

FuzzyWuzzy

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!

4 comments

/phoenix #ecto #foreign-key

0 1483 4

2021-01-02 22:36:33 UTC

First 4 of 4 Posts

al2o3cr

Before diving into the Ecto plumbing, take a step back. What structure are you looking for in the database? Once you understand what rows you’re expecting to see it will be easier to wire the associations together.

A general note on the posted schemas:

belongs_to :mentee_user_role_id, MyApp.Accounts.UserRole, foreign_key: :mentee_user_role
# should likely be
belongs_to :mentee_user_role, MyApp.Accounts.UserRole, foreign_key: :mentee_user_role_id

The first argument to belongs_to is the name of the association as used in the rest of the application, while the value passed in foreign_key should be the name of a database column.

HOWEVER

That’s still going to fail, because that value for foreign_key doesn’t match the schema:

add :user_role_id_1, references(:user_roles, on_delete: :delete_all), null: false

Post #1

FuzzyWuzzy

Thanks for the response! This is my first Ecto project, so I’m still wrapping my head around it. I’ve already thought of the structure of the database. I’ve attached an ERD showing the structure I want to achieve, which will hopefully make it more clear.

The problem I’m having is actually translating this in a usable way to Ecto. I haven’t been able to find much/any documentation on referencing the same table (let alone the same column).

I’ve tried updating the schema per your recommendations, but am still having issues. Do you have any further suggestions? I think I’m missing something that is not immediately obvious.

schema "user_roles" do
  has_many :mentees, MyApp.Accounts.UserRelationship, foreign_key: :mentee
  has_many :mentors, MyApp.Accounts.UserRelationship, foreign_key: :mentor
end

schema "user_relationships" do
  belongs_to :mentee, MyApp.Accounts.UserRole, foreign_key: :user_role_id_1
  belongs_to :mentor, MyApp.Accounts.UserRole, foreign_key: :user_role_id_2
end

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

Post #2

FuzzyWuzzy

I was just able to get this work by using the following:

schema "user_roles" do
  has_many :user_relationships_1, MyApp.Accounts.UserRelationship, foreign_key: :user_role_id_1
  has_many :user_relationships_2, MyApp.Accounts.UserRelationship, foreign_key: :user_role_id_2
end

schema "user_relationships" do
  belongs_to :user_role_1, MyApp.Accounts.UserRole, foreign_key: :user_role_id_1
  belongs_to :user_role_2, MyApp.Accounts.UserRole, foreign_key: :user_role_id_2
end

I’m still not quite understanding how this is working and/or if this is entirely correct. UserRelationships should have two foreign keys on the columns user_role_id_1 and user_role_id_2 to the ID field of UserRole. I think I’m still missing something. Any clarification would be tremendously helpful.

Post #3

al2o3cr

foreign_key is always going to be a column name, not an association name. That seems to be the problem with the code you posted; the has_many associations should be foreign_key: :user_role_id_1 etc