Self Referencing many_to_many Updating Associations Issues

EDIT ** The code now works after some help

I started a thread a few days ago with some general questions and since I am now focused and have some running code I thought it would make sense to post fresh with more direct questions.

I have Users which can have friendships and the converse of that is a reverse_friendship using the Friendships table (many_to_many relationships). My attempted code is below but I am having 2 problems.

  1. how to add the associations with timestamps. If I don’t add timestamps to the table in the Friendship migration the inserts run fine
  2. put_assoc works (User 1 has User 2 as a friend and User 2 has User 1 as a reverse friend). However when I go to add another I get a warning about on_replace, I think thats because put_assoc is essentially trying to overwrite the entire friends/reverse friends. How can I add friend/reverse_friend associations and have it be additive (Add User 2 then User 3, etc to User 1 as a friend).

User Schema

defmodule App.Users.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field :name, :string
    field :phone_number, :string
    field :username, :string
    field :email, :string, null: false
 
    many_to_many :friends, User,
      join_through: "friendships",
      join_keys: [from_user_id: :id, to_user_id: :id]

    many_to_many :reverse_friends, User,
      join_through: "friendships",
      join_keys: [to_user_id: :id, from_user_id: :id]

    timestamps()
  end

  @doc false
  def changeset(user_or_changeset, attrs) do
    required_fields = [:username, :name, :email, :phone_number]

    user_or_changeset
    |> cast(attrs, required_fields)
    |> validate_required(required_fields)
  end

end

User Migration

defmodule App.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :name, :string, null: false
      add :username, :string, null: false
      add :email, :string, null: false
      add :phone_number, :string, null: false

      timestamps()
    end

    create unique_index(:users, [:username, :email, :phone_number])
  end
end

Friendship Schema

defmodule App.Users.Friendship do
  use Ecto.Schema
  alias App.Users.User
  import Ecto.Changeset

  @primary_key false
  schema "friendships" do
    belongs_to :from_user, User
    belongs_to :to_user, User

    timestamps()
  end

end

Friendship Migration

defmodule App.Repo.Migrations.AddCreateFriendshipTable do
  use Ecto.Migration

  def change do
    create table(:friendships, primary_key: false) do
      add :from_user_id, references(:users)
      add :to_user_id, references(:users)
      add :accepted, :boolean, default: false

      timestamps()
    end

    create unique_index(:friendships, [:from_user_id, :to_user_id])
  end
end

Users Context Module

defmodule App.Users do
  import Ecto.Query, warn: false
  alias App.Repo
  alias App.Users.User
  alias App.Users.Friendship

  def add_friend(user, friend_user) do
      %Friendship{}
      |> change()
      |> put_assoc(:from_user, user)
      |> put_assoc(:to_user, friend_user)
      |> Repo.insert()
  end

end
1 Like
  1. You have to define a Friendship schema and use it in many_to_many association instead of direct table name. Timestamps will be added automatically if you use Repo.insert or Repo.insert!, but if you want to use Repo.insert_all then values for timestamps must be provided manually.
  2. put_assoc manages the whole collection. Insert Friendship record this way:
  def add_friend(user, friend_user) do
    %Friendship{}
    |> change()
    |> put_assoc(:from_user, user)
    |> put_assoc(:to_user, friend_user)
    |> Repo.insert()
  end

*tip: add unique composite index for from_user_id and to_user_id fields.

1 Like

Thank you, that is SO helpful and that is making a lot more sense and is closer to what I have used for ecto in the past (with more conventional has_many/belongs_to relationships).

One question. When creating the Friendship schema what do I need to put in it? Since I am getting the error cannot put assoc from_user, assoc from_user not found. when I try it I am guessing something related to the many_to_many I have in the user schema definition has to go in there.

After trying a couple of things I figured it out. I updated the code above in my original post for anyone else who finds this thread. The moral of the story was to create a Friendship schema with some belongs_to calls, which created the schema to match the migration. NOTE I had to disable the primary key in the Friendship schema for this to work.

Thank you again fuelen, you really pointed me in the right direction.

This shouldn’t be needed, depending on how you insert data though. You can just make both a composite primary key + an unique index on both fields comprising the composite primary key. That should work.