FuzzyWuzzy
Foreign Key References to the Same Table
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!
Marked As Solved
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. ![]()
Also Liked
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
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
As to your specific error, in future it helps to post the code that’s giving the error (whatever’s calling cast in the case of the message above).
Popular in Questions
Other popular topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #javascript
- #code-sync
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance








