Is it possible to implement Many-To-Many association on the same entity/table using ecto

Question

I have a User table with a unique primary key, and would like to setup a many-to-many association to another User entity in order to achieve a "social network" kind of thing. From this Many-to-many on same table i understand this is possible, but how can i structure the ecto schema for the same?

Currently using a join_through as described here with a schema with some extra fields to further define the association such as a flag blocked in case a user blocks another, but i had an issue setting up the has_many on the join table since both scenarios point to the same User schema. till i began to wonder whether this is really possible with ecto.

Answer 1

It is surely possible, you just need to instruct Ecto how to get to the respective fields.

For migrations

create table(:users) do
  add(:name, :string)
end

create table(:friendship) do
  add(:me_id, references("users"))
  add(:friend_id, references("users"))
end

And the respective schemas

schema "users" do
  field(:name, :string)

  many_to_many(:users, User,
    join_through: "friendship",
    join_keys: [me_id: :id, friend_id: :id]
  )
end

  schema "friendship" do
    field(:me_id, :integer)
    field(:friend_id, :integer)
  end

You might do the following

iex|1> Repo.insert! %User{name: "john"}
iex|2> Repo.insert! %User{name: "mary"}
iex|3> Repo.insert! %Friendship{me_id: 1, friend_id: 2}

Resulting in

iex|4> Repo.one(from u in User, 
...|4>   where: [id: 1], preload: [:users])

%User{
  __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
  id: 1,
  name: "john",
  users: [
    %User{
      __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
      id: 2,
      name: "mary",
      users: #Ecto.Association.NotLoaded<association :users is not loaded>
    }
  ]
}