Hi All,
I’m trying to wrap my head around how to do self referencing associations in Phoenix. I have done this in Rails but now am trying to do the same in Phoenix.
Basically the setup is like this:
I have a lot of Users that can all login, but have different roles. It is a teaching app, so the main roles are student and teacher. A teacher has_many students and a student belongs_to a teacher. But these are both in the Users table. hence the self-referencing association.
Can someone point me to the right direction in setting this up? It seems that it is a pretty common thing so just wondering if there are any best practices to do this.
Thank!
2 Likes
If your users table has a teacher_id column which indicates the teacher each student belongs to, then:
In your model, you do:
defmodule User do
schema "users" do
field :name
belongs_to :teacher, User
has_many :students, User, foreign_key: :teacher_id
end
end
That’s it.
Repo.insert!(%User{id: 1, name: "Teacher"})
Repo.insert!(%User{id: 2, name: "Student John", teacher_id: 1})
Repo.insert(%User{id: 3, name: "Student Jane", teacher_id: 1})
teacher = Repo.get(User, 1) |> Repo.preload(:students)
Enum.map(teacher, &(&1.name))
#> ["Student John", "Student Jane"]
student = Repo.get(User, 2) |> Repo.preload(:teacher)
student.teacher.name
#> "Teacher"
11 Likes
Awesome thanks!
I was on my way. I actually forgot the belongs_to in the users schema.
Also, thanks a lot for the preload examples. I was wondering how to implement that exactly and this makes it really clear!
2 Likes
OK, after trying this out in my Phoenix app I have a couple of comments to add to the code. So that others can benefit from this as well:
I was working on a Phoenix app called Studentmanager, so the model was actually called: Studentmanager.User and that needs to be added in the association as well.
defmodule Studentmanager.User do
schema "users" do
field :name
belongs_to :teacher, Studentmanager.User
has_many :students, Studentmanager.User, foreign_key: :teacher_id
end
end
And here there is a slight correction needed to get the names correctly, teacher.students will give a list that Enumerable can handle.
teacher = Repo.get(User, 1) |> Repo.preload(:students)
Enum.map(teacher.students, &(&1.name))
#> ["Student John", "Student Jane"]
thanks again!
6 Likes
@atimberlake How would you write the SQL Join for the Repo.all(query) to join the teachers to students to get something like:
| id | name | student_id | Student name |
|----|----------------|------------|--------------|
| 1 | Ms. Smith | 10 | Bob |
| 1 | Ms. Smith | 11 | Sally |
| 1 | Ms. Smith | 12 | Rich |
| 2 | Mr. Washington | 13 | Kelly |
| 3 | Mr. Washington | 14 | Sandra |
Or the reverse where the Teacher is listed for each student (Pretty much the same but flipped)
| id | name | teach_id | teacher_name |
|----|--------|----------|----------------|
| 10 | Bob | 1 | Ms. Smith |
| 11 | Sally | 1 | Ms. Smith |
| 12 | Rich | 1 | Ms. Smith |
| 13 | Kelly | 2 | Mr. Washington |
| 14 | Sandra | 2 | Mr. Washington |
That is all in how your select is given, you’d probably put them into a tuple if order is important. 
1 Like
I think I came up with something like this in the end:
query = from f in FileRecord,
where: not is_nil(f.hash),
join: d in assoc(f, :duplicates),
select: [f.id, f.path, d.path, d.id]
Repo.all(query)
Which, for the example was probably like this:
query = from u in User,
join: s in assoc(u, :students),
select: [u.id, u.name, s.id, s.name]
Repo.all(query)
1 Like
Hi All, I’m just revisiting this post and having the following question: If we extend User, adding a Mentor association in the same way as Teacher, can a preload() fetch both in one SQL query once they’re in the same table?
defmodule User do
schema "users" do
field :name
belongs_to :teacher, User, foreign_key: :teacher_id
belongs_to :mentor, User, foreign_key: :mentor_id
has_many :students, User, foreign_key: :teacher_id
has_many :mentees, User, foreign_key: :mentor_id
end
end
