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

I’m following your guide, which is great - thanks, but am left wondering how you might handle adding multiple records vs a single call to the Relationship changeset.

I would use Ecto.Multi, something like:

Ecto.Multi.new()
|> Ecto.Multi.insert(table_name, changeset(struct, attrs))
|> Repo.trasaction()

# So, with the 'relationship' naming:
Ecto.Multi.new()
|> Ecto.Multi.insert(
  :relationships,
  Relationship.changeset(%Relationship{}, attrs)
)
|> Repo.transaction()

If I’m not mistaken, what’s great about it is that you don’t really have to do anything tricky. I haven’t tested this but I think this is a good guide on Ecto.Multi from Elixir School.

** Forgot to include Repo.transaction()

Just came across this guide and the following one on polymorphism – both great! However I’m having a little trouble puzzling out the appropriate way to combine the two concepts…

For example, if I wanted to model discrete types of relationships between two people (e.g. coworkers, friends, or parent/child), what would be a good way to go about it?

My first instinct would be to have a single table with a “relationship_type” field, but it seems like the proper “Ecto” way might be to create multiple joins tables for each relationship type?

Curious to hear your thoughts.

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…