Self referencing many to many

I have

schema "organizations" do
    many_to_many :partners, Organization, join_through: "suborganizations", join_keys: [from_organization_id: :id, to_organization_id: :id], on_delete: :delete_all
end

with its many to many migration:

def change do
    create table(:suborganizations, primary_key: false) do
      add :from_organization_id, references(:organizations, on_delete: :delete_all)
      add :to_organization_id, references(:organizations, on_delete: :delete_all)
    end
    
    create unique_index(:suborganizations, [:from_organization_id, :to_organization_id], name: :uniq_sub_org)
  end

When I create two records o1 and o2
and do

o1 |> Repo.preload(:partners) |> change |> put_assoc(:partners, [o2]) |> Repo.update!

o2 gets added.
But

o2 = Repo.get!(Organization, o2.id) |> Repo.preload(:partners)   

Shows nothing!

What am i doing wrong?

maybe because You have this key

o1 -> o2

but not this one

o2 -> o1

what does that even mean?

It means You may have a suborganizations link with o1.id, o2.id, but not the one with o2.id, o1.id

from_organization_id: :id, to_organization_id: :id

thats how the :join_keys are supposed to work.
What is the solution?

I think like kokolegorille mentioned, this is due to the symmetric relationship (which I imagine is intentional given the name “suborganizations”). You have mapping o1 -> o2 but you don’t have the mapping o2 -> o1.

In your example you are updating a record and getting in the db:

from | to
-----------
o1   | o2

And then with your Repo.get! you are doing the equivalent of select * from suborganizations where 'from' = o2, which doesn’t have any matching records, since o2only has a parent org and no suborgs.

What you might want is to have have two distinct associations in your changeset: one for parent orgs and one for child orgs. Then you can have a preloaders to populate either one.

2 Likes

I made a similar example (users, friendships)

iex> u2 = Accounting.get_user(2) |> Repo.preload(:friends)
[debug] QUERY OK source="users" db=1.8ms
SELECT u0."id", u0."name", u0."email", u0."password_hash", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) [2]
[debug] QUERY OK source="users" db=1.7ms
SELECT u0."id", u0."name", u0."email", u0."password_hash", u0."inserted_at", u0."updated_at", u1."id" FROM "users" AS u0 INNER JOIN "users" AS u1 ON u1."id" = ANY($1) INNER JOIN "friendships" AS f2 ON f2."from_friend_id" = u1."id" WHERE (f2."to_friend_id" = u0."id") ORDER BY u1."id" [[2]]

You can see in the query

ON f2."from_friend_id" = u1."id" WHERE (f2."to_friend_id" = u0."id")

BTW I used this relationship

many_to_many :friends, User, 
  join_through: "friendships", 
  join_keys: [from_friend_id: :id, to_friend_id: :id], 
  on_delete: :delete_all
1 Like

how is this different from:

many_to_many :partners, Organization, join_through: "suborganizations", join_keys: [from_organization_id: :id, to_organization_id: :id], on_delete: :delete_all
end

The relation I used is equivalent to yours. I just wanted to replicate your example.

I mean You are using a follower/following model.

If You want to get bidirectional link, then just build the 2 keys at the same time

so you are saying there’s no way of self referencing the same model using many to many?

Did you mean this?

Because of this (in the mentionned doc)

  schema "contacts" do
    belongs_to :user, User
    belongs_to :contact, User
  end

You may have contacts, but your contacts may not see You as contact… unless You create in a transaction both keys.

eg: u1 and u2

%Contact{user_id: u1.id, contact_id: u2.id}
%Contact{user_id: u2.id, contact_id: u1.id}

If You think of User and Post in a many to many, it’s easy because You can say a User has many posts, and a Post has many users.

But You want something like User has many users (or Org has many orgs) and vice versa.

is it possible using ecto?

Yes, I made this example of User

  schema "users" do
    field :name, :string
    field :email, :string
    field :password, :string, virtual: true
    field :password_hash, :string
    
    ## Accounting context
    
    many_to_many :friends, User, 
      join_through: "friendships", 
      join_keys: [from_friend_id: :id, to_friend_id: :id], 
      on_delete: :delete_all
    
    many_to_many :followeds, User, 
      join_through: "followings", 
      join_keys: [follower_id: :id, followed_id: :id], 
      on_delete: :delete_all
    
    many_to_many :followers, User, 
      join_through: "followings", 
      join_keys: [followed_id: :id, follower_id: :id], 
      on_delete: :delete_all
    
    timestamps()
  end

As You can see, friendships and followings are just join tables…

If I want to follow somebody I create a followings join key.

But if I want to create a friend, then I add 2 friendships join keys.

how is this any different from?:

many_to_many :partners, Organization, 
join_through: "suborganizations", 
join_keys: [from_organization_id: :id, to_organization_id: :id], 
on_delete: :delete_all

It is exactly the same, as stated above. I took your example. And the query above shows the same query as yours, but You could see it is not symetric

thats the point.
In this case I expect o1 --> o2 and o2 --> o1

but that’s not happening. thats what ive been trying to ask, how can that be done?

You want o1 -> o2 and o2 -> o1.

So why don’t You create the 2 keys in a transaction? It’s really a simple solution.

defeats the purpose of from_organization_id --> o1.id and to_organization_id--> o2.id

I expected o2 |> Repo.preload(partners) to include o1 as well with those join keys.

Sadly, I see there’s no other choice.