New Ecto "How-To" Guide: Self-referencing many to many

Learning Resources Guides/Tuts/Tips/Info
,
5

On one of my early design choices for an earlier version of Metamorphic I used a separate relationship_type that was associated to a relationship through a has_many / belongs_to association.

# relationship.ex
...
belongs_to :relationship_type, RelationshipType, type: :binary_id
...

# relationship_type.ex
...
has_many :relationships, Relationship
...

# person.ex
...
many_to_many :relationships,
   Person,
   join_through: Relationship,
   join_keys: [person_id: :id, relation_id: :id],
   join_where: [confirmed_at: {:not, nil}],
   on_delete: :delete_all

many_to_many :reverse_relationships,
   Person,
   join_through: Relationship,
   join_keys: [relation_id: :id, person_id: :id],
   join_where: [confirmed_at: {:not, nil}],
   on_delete: :delete_all
...

Then, in another iteration, I used an Ecto.Enum field of type on the relationship table and got rid of the relationship_type table:

#relationship.ex
...
field :type, Ecto.Enum,
      values: [:academic, :family, :friend, :professional, :romantic],
      default: :friend
...

# user.ex
...
many_to_many :relationships,
     Accounts.User,
     join_through: Relationships.Relationship,
     join_keys: [user_id: :id, relation_id: :id],
     join_where: [confirmed_at: {:not, nil}],
     on_delete: :delete_all

many_to_many :reverse_relationships,
     Accounts.User,
     join_through: Relationships.Relationship,
     join_keys: [relation_id: :id, user_id: :id],
     join_where: [confirmed_at: {:not, nil}],
     on_delete: :delete_all
...

In the latest design choice, for the latest version of Metamorphic, I dropped the many_to_many association style and enum and I simply used a label field in the user_connection table to change the “type” of relationship to be simpler and more like a “tag” that people can update to their liking and the user_connection took on more similar a role as the first version’s relationship table.

# user_connection.ex
...
field :label, Encrypted.Binary
belongs_to :user, User
belongs_to :reverse_user, User
...

# user.ex
...
has_many :user_connections, UserConnection
...

I think it really depends on what you’re going for and how you want to structure everything for your particularly use case and future design choices when developing further… if/when I do it again, I would probably go with the many_to_many association for users and then the “label” style for the field.

So, for your example… questions might be:

  • is the “relationship_type” more of a label or will there need to be more information pertaining directly to each relationship_type?
  • if there’s more info per relationship_type, does it change frequently like a profile or address? (if so, then maybe an embedded schema is what you want)
  • do you want to be able to easily associate other tables to the relationship_type in the future? (if so, then maybe the separate table makes more sense)

I think you might do something like the person example for users…

#person.ex (or substitute user/User here)

many_to_many :relationships,
   Person,
   join_through: Relationship,
   join_keys: [person_id: :id, relation_id: :id],
   join_where: [confirmed_at: {:not, nil}],
   on_delete: :delete_all

many_to_many :reverse_relationships,
   Person,
   join_through: Relationship,
   join_keys: [relation_id: :id, person_id: :id],
   join_where: [confirmed_at: {:not, nil}],
   on_delete: :delete_all

# relationship.ex

field :type, Ecto.Enum,
      values: [:child, :coworker, :friend, :parent],
      default: :friend

# migration
...
create table(:relationships) do
    add :person_id, references(:people)
    add :relation_id, references(:people)
    # ... have to still add your enum and other fields etc
    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
  )
...

This would give you a separate table relationships with a Relationship struct where you can have everything related to a relationship there. Then, users are “connected” to each other through that relationship table and it simply has an Ecto.Enum field to designate the type of relationship.

These are my initial thoughts. I feel like it really just depends… :slight_smile: