User Friendship Schema Design

Two options come to mind:

1. Store the friendship in sorted order, i.e. friend_a_id < friend_b_id. On the boundary of the context, always make sure to sort the ids before querying.
2. Store the relationship twice. When unfriending, make sure to delete both entries.

What exactly are you trying to do? Just for full context. What kind of queries you need to run in your case?

This is not necessarily an Elixir specific topic, so there should be ton of resources outside our world. I remember reading a few years ago about the Twitter schema, don’t remember exactly the details, but do remember that they were duplicating a lot of data by basically building and writing a timeline for each user..

Thats a good point about not really being Elixir specific. I think I will expand out my search for how others have accomplished this and try a few things first. I will come back with the schema I have tried and my actual query code/use cases. As I am new to Elixir and Ecto I trying to make sure I learn some of the idiomatic ways of approaching problems.

Here is Redis way, described in detail. Lots of logic to infer from there https://redislabs.com/ebook/part-2-core-concepts/chapter-8-building-a-simple-social-network/

Oh btw you might want to have a look at graph databases like OrientDB (very simple, friendly syntax) or Neo4j (market leader, but a bit more complex)

If you are working on relationships, a graph database is really what you want.

Thank you all for the help so far. On further reflection in terms of what I am trying to do I realized its not a true friendship model. I really just want to keep track of all of the users that have been invited and accepted by say User 1, then do the same for User 2, 3, etc. I dont need to keep track of if User 4 is connected to User 2 who is then connected to User 1. I will likely change the table name from Friendship but for now I am going to leave it and get the functionality I need working first. Its more like keeping track of which users are subscribed to a specific user.

So I have created the following code so far which gets me pretty close I think, however I am getting the error below because I put timestamp columns via my migration but put_assoc doesnt seem to add them. Any help getting the timestamps added and also comments on better ways to accomplish this would be most helpful!
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" violates not-null constraint

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]

    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

  def changeset_friend(changeset, friend_user) do
      changeset
      |> put_assoc(:friends, [friend_user])
  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
  import Ecto.Changeset

  schema "friendships" do

    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
  end
end

Users Context Module

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

  def add_friend(user, friend_user) do
    user
    |> Repo.preload(:friends)
    |> Ecto.Changeset.change()
    |> User.changeset_friend(friend_user)
    |> Repo.update()
  end

end

Hi, so I’m not sure if the error you are getting is for say, perhaps: not working on a reset database so there are entries that exist and therefore would have null entries for your not null constraints.

But, if that’s not the case, then I believe the reason is because you have named your “friendships” table as such on your many_to_many join_through call.

If you change it to naming the schema file, then you will be able to specify the timestamps as you do. Currently, you have removed the schema file by calling the table by its string name “friendships”. Thus you don’t technically have a schema (as far as Ecto is concerned, I think), so you cannot add the timestamps via the schema.

To use your implementation of the timestamps, I would try naming your schema file:

join_through: App.Users.Friendship

Then, your timestamps should get added.

Another option is to set your timestamps default at the database level (sounds more complicated). This is coming from Dashbit’s wonderful Little Ecto Cookbook resource.

In case that’s not the issue for you, then sorry (that’s just what had jumped out at me from looking through your code snippet).