Self Referencing many_to_many Updating Associations Issues

Questions / Help
, , ,
1

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
2
  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.

2 Likes
3

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.

4

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.

5

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.