Self Referencing Association in Phoenix / Ecto

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.


1 Like

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

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), &(&
#> ["Student John", "Student Jane"]

student = Repo.get(User, 2) |> Repo.preload(:teacher)
#> "Teacher"

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!


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

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), &(&
#> ["Student John", "Student Jane"]

thanks again!


@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. :slight_smile:

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.path, d.path,]

Which, for the example was probably like this:

   query = from u in User,
      join: s in assoc(u, :students),
      select: [,,,]
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