Many-to-many on the same table in Phoenix

Question

Using Ecto v2.2.6, Phoenix 1.3

I have a table of people (who happen to be related to each other), and another table of relations for those people. The relations table lists the id of the parent in one column, and the id of the child in another.

mix phx.gen.json Accounts Person persons name:string age:integer
mix phx.gen.json Accounts Relationship relationships parent_id:references:persons child_id:references:persons

Now, I am about to add belongs_to relationships in the relationships schema (which currently looks like this)

  schema "relationships" do
    field :parent_id, :id
    field :child_id, :id
    timestamps()
  end

But without the ability to set the id explicitly, it would looks like this (which doesn't seem to work)

  schema "relationships" do
    belongs_to :person UserRelations.Accounts.Person 
    belongs_to :person UserRelations.Accounts.Person 
    timestamps()
  end

How can I write the relationships schema so that I capture these belongs_to relationships?

Edit

I have set the schema up like this, as per suggestion:

  schema "relationships" do
    belongs_to :parent UserRelations.Accounts.Person 
    belongs_to :child UserRelations.Accounts.Person 
    timestamps()
  end

I have tried to do something similar with the Person schema as well:

  schema "persons" do
    field :age, :integer
    field :name, :string
    many_to_many :parent_of, UserRelations.Accounts.Person, join_through: "relationships"
    many_to_many :child_of, UserRelations.Accounts.Person, join_through: "relationships"
    timestamps()
  end

However, when I try to access these relationships (I am doing this through an absinthe/graphql schema), I see that it is looking for a person_id somewhere:

[error] Task #PID<0.400.0> started from #PID<0.395.0> terminating
** (Postgrex.Error) ERROR 42703 (undefined_column): column f2.person_id does not exist

@Dogbert Attempted to implement that. Please see edit above. — Mark Karavan, Oct 08 '17 at 03:50
I think you need to add `join_keys: [parent_id: :id, child_id: id]` to the first `many_to_many` and the reverse (`[child_id: :id, parent_id: :id]`) to the second one. — Dogbert, Oct 08 '17 at 04:44
@Dogbert Perfect, It worked exactly as stated. If you turn those two comments into an answer, I'll mark as correct. — Mark Karavan, Oct 08 '17 at 17:42

score 5 · Accepted Answer · edited May 03 '20 at 03:13

The name of the two belongs_to relationships need to be different. For example:
```
belongs_to :child, UserRelations.Accounts.Person 
belongs_to :parent, UserRelations.Accounts.Person 
```
With these names, Ecto will infer the foreign key correctly: child_id and parent_id.
For the many_to_many, you need to provide join_keys to both as Ecto can't infer it from the name of the relationship and the schema module.

For the first one, you need to add:
```
, join_keys: [parent_id: :id, child_id: :id]
```
And for the second one:
```
, join_keys: [child_id: :id, parent_id: :id]
```

Many-to-many on the same table in Phoenix

Edit

1 Answers1