Settings View Source Self-referencing many to many

Ecto.Schema.many_to_many/3 is used to establish the association between two schemas with a join table (or a join schema) tracking the relationship between them. But, what if we want the same table to reference itself? This is commonly used for symmetric relationships and is often referred to as a self-referencing many_to_many association.

people-relationships

People relationships

Let's imagine we are building a system that supports a model for relationships between people.

defmodule MyApp.Accounts.Person do
  use Ecto.Schema
  
  alias MyApp.Accounts.Person
  alias MyApp.Relationships.Relationship

  schema "people" do
    field :name, :string

    many_to_many :relationships,
                 Person,
                 join_through: Relationship,
                 join_keys: [person_id: :id, relation_id: :id]

    many_to_many :reverse_relationships,
                 Person,
                 join_through: Relationship,
                 join_keys: [relation_id: :id, person_id: :id]

    timestamps()
  end
end

defmodule MyApp.Relationships.Relationship do
  use Ecto.Schema

  schema "relationships" do
    field :person_id, :id
    field :relation_id, :id
    timestamps()
  end
end

In our example, we implement an intermediate schema, MyApp.Relationships.Relationship, on our :join_through option and pass in a pair of ids that we will be creating a unique index on in our database migration. By implementing an intermediate schema, we make it easy to add additional attributes and functionality to relationships in the future.

We had to create an additional many_to_many :reverse_relationships call with an inverse of the :join_keys in order to finish the other half of the association. This ensures that both sides of the relationship will get added in the database when either side completes a successful relationship request.

The person who is the inverse of the relationship will have the relationship struct stored in a list under the "reverse_relationships" key. We can then construct queries for both :relationships and :reverse_relationships with the proper :preload:

iex> preloads = [:relationships, :reverse_relationships]
iex> people = Repo.all from p in Person, preload: preloads
[
  MyApp.Accounts.Person<
    ...
    relationships: [
      MyApp.Accounts.Person<
        id: ...,
        ...
      >
    ]
  >,
  MyApp.Accounts.Person<
    ...
    reverse_relationships: [
      MyApp.Accounts.Person<
        id: ...,
        ...
      >
    ]
  >
]

In the example query above, we are assuming that we have two "people" that have entered into a relationship. Our query illustrates how one person is added on the :relationships side and the other on the :reverse_relationships side.

It is also worth noticing that we are implementing separate parent modules for both our Person and Relationship modules. This separation of concerns helps improve code organization and maintainability by allowing us to isolate core functions for relationships in the MyApp.Relationships context and vice-versa.

Let's take a look at our Ecto migration:

def change do
  create table(:relationships) do
    add :person_id, references(:people)
    add :relation_id, references(:people)
    timestamps()
  end

  create index(:relationships, [:person_id])
  create index(:relationships, [:relation_id])

  create unique_index(
    :relationships,
    [:person_id, :relation_id],
    name: :relationships_person_id_relation_id_index
  )

  create unique_index(
    :relationships,
    [:relation_id, :person_id],
    name: :relationships_relation_id_person_id_index
  )
end

We create indexes on both the :person_id and :relation_id for quicker access in the future. Then, we create one unique index on the :relationships and another unique index on the inverse of :relationships to ensure that people cannot have duplicate relationships. Lastly, we pass a name to the :name option to help clarify the unique constraint when working with our changeset.

# In MyApp.Relationships.Relationship
@attrs [:person_id, :relation_id]

def changeset(struct, params \\ %{}) do
  struct
  |> Ecto.Changeset.cast(params, @attrs)
  |> Ecto.Changeset.unique_constraint(
    [:person_id, :relation_id],
    name: :relationships_person_id_relation_id_index
  )
  |> Ecto.Changeset.unique_constraint(
    [:relation_id, :person_id],
    name: :relationships_relation_id_person_id_index
  )
end

Due to the self-referential nature, we will only need to cast the :join_keys in order for Ecto to correctly associate the two records in the database. When considering production applications, we will most likely want to add additional attributes and validations. This is where our isolation of modules will help us maintain and organize the increasing complexity.

summary

Summary

In this guide we used many_to_many associations to implement a self-referencing symmetric relationship.

Our goal was to allow "people" to associate to different "people". Further, we wanted to lay a strong foundation for code organization and maintainability into the future. We have done this by creating intermediate tables, two separate functional core modules, a clear naming strategy, an inverse association, and by using many_to_many :join_keys to automatically manage those join tables.

Overall, our code contains a small structural modification, when compared with a typical many_to_many, in order to implement an inverse join between our self-referenced table and schema.

Where we go from here will depend greatly on the specific needs of our application. If we remember to adhere to our clear naming strategy with a strong separation of concerns, we will go a long way in keeping our self-referencing many_to_many association organized and easier to maintain.