Modeling many to many relationship among same type

This might not be a specific ecto question as much as a general database setup question. I want users to have relationships to one another. Every example I can find for many to many in ecto involves types from different tables (e.g., users and products). I’m just not sure how to set this up.

Right now in the join table follows I have a column for followed and a column for “follower”. The migration is just:

def change do 
  create table(:follows) do 
    add(:follower, references(:user), primary_key: true)
    add(:followed, references(:user), primary_key: true)
  end
end

The User schema includes many_to_many :follows, User, join_through: "follows". Is this the right way to set it up?

Hi, Ecto has a guide for this now: Self-referencing many to many.

Hope it helps :heart:

1 Like

That’s perfect! Thank you so much.

1 Like

Just my $0.02 here, but I would avoid using many_to_many here (I avoid it in general honestly). You are going to want to store additional columns on that join table, and you’ll need an ecto schema to represent it. At a minimum you’ll likely want to know when a user followed another user, and that gets easier if you have an ecto schema for it.

You can always do a has_many :followers, through: [:follows, :follower] to get back the convenient association.

3 Likes

Not sure I understand how that would work. So the User schema would be

schema "users" do 
...
has_many :followers, through: [:follows, :follower]
has_many :follows

? Are Follows a separate context in this setup?
Then you have separate tables for follows and follower? I’m sure I’m thinking of that wrong because that seems like a good way for them to be decoupled and potentially out of sync.

If you’re trying to do a follower type system à la Twitter, then the Ruby on Rails Tutorial book walks through that setup toward the end.

And like @benwilson512 said, you don’t need the self-referencing setup. Although, it is important to note, you can very easily store additional columns on a join table in a many_to_many (even self-referencing) and is shown in the Ecto guide(s). Additionally, The Little Ecto Cookbook from Dashbit is a great resource.

But, if you do need to self reference, then the Ecto guide I mentioned is a great example (hopefully!).

:heart:

1 Like

In essence, you’d have a separate table – say, called user_to_user – that has columns like user_from, user_to (both IDs and referencing the users table), and various other metadata as well. Which is what @benwilson512 suggests; the associations between the users themselves usually require metadata at a later stage and people get bitten by this because they didn’t architect their DB schema to allow it from the start.

1 Like

Maybe a graph database would solve your problem better?

@entone, haha. I’m not sure my struggles would be helped by trying to learn yet another technology from scratch, lol. The problem is that I’ve never really done database work so I don’t have a background knowledge of relational database structure to lean on and translate to Ecto. Ecto is basically teaching me database stuff.

@f0rest8 , thanks for the tip about RoR tutorial. I actually did the Learn Enough courses a few years ago but had forgotten about it as my life went a different direction. I still had all the code though and was able to piece it together.

Even though I haven’t finished all the CRUD functions what I have now at least works with respect to querying seed data with no follows. (i.e., it returns empty lists instead of the myriad errors I’ve had all day.) So for those who might stumble on this thread later:

defmodule MyApp.Relationship do 
def get_followers(arg) do
    Relationship
    |> where([r], ^arg.user_id == r.followed)
    |> Repo.all()
  end

def get_followers(arg) do
    Relationship
    |> where([r], ^arg.user_id == r.followed)
    |> Repo.all()
  end

  def datasource() do
    Dataloader.Ecto.new(Repo, query: &query/2)
  end

  def query(queryable, _) do
    queryable
  end
end
...
defmodule MyApp.Relationships.Relationship do 
@attrs [:following, :followed]

  schema "relationships" do
    belongs_to(:following, User)
    belongs_to(:followed, User)
    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @attrs)
  end
end
...
defmodule MyApp.Accounts.User do 
  schema "users" do
  ...
    has_many :active_relationships, Relationship, foreign_key: :following_id
    has_many :passive_relationships, Relationship, foreign_key: :followed_id
    has_many :followers, through: [:passive_relationships, :followed]
    has_many :following, through: [:active_relationships, :following]
  end
end
...
defmodule MyAppWeb.Schema do 
  object :user do 
    field :followers, list_of(:user) do
      resolve(dataloader(Relationship))
    end

    field :following, list_of(:user) do
      resolve(dataloader(Relationship))
    end
...
defmodule MyApp.Repo.Migrations.AddRelationshipsTable do
  use Ecto.Migration

  def change do
    create table(:relationships) do
      add(:followed_id, :id)
      add(:following_id, :id)

      timestamps()
    end

    create(index(:relationships, [:followed_id]))
    create(index(:relationships, [:following_id]))
    create(unique_index(:relationships, [:followed_id, :following_id]))
  end
end
1 Like

This looks perfect, nicely done!

1 Like