Self referenced many_to_many - inserting and modifying data issue

Hi

I’m playing around with Elixir and Ecto 2.0 and I’m facing an issue with a many_to_many relation for a self referenced model. There are a table for animals, and another one that stores the relation between them (brothers and sisters).

I have the following tables:

create table(:animals) do
  add :name, :string
  add :info, :string
  timestamps()
end

create table(:animal_siblings) do
  add :animal_id, references(:animals)
  add :animal_sibling_id, references(:animals)
  timestamps()
end

I’ve been trying to figure out how to resolve this in the schema to be able to insert and modify the data, but I’m not sure how to proceed in order to start inserting the data with the many to many relation. This is what I have right now:

defmodule TestApp.Animal do
  use TestApp.Web, :model

  schema "animals" do
    field :name, :string
    field :info, :string

    many_to_many :siblings, TestApp.Animal, join_through: TestApp.AnimalSibling, join_keys: [animal: :id, animal_sibling: :id]

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :info])
    |> cast_assoc(:siblings)
    # |> put_assoc(:siblings, build_siblings(params))
    |> validate_required([:name])
  end

  defp build_siblings(params) do
    (params["siblings"] || [])
      |> Enum.map(fn(sibling) ->TestApp.Repo.get(TestApp.Animal, sibling.sibling_id)
    end)
  end
end

defmodule TestApp.AnimalSibling do
  use Ecto.Schema

  schema "animal_siblings" do
    belongs_to :animal, TestApp.Animal
    belongs_to :animal_sibling, TestApp.Animal
    timestamps()
  end
end

Thanks in advance!

Can you be more precise in your question? For example, can you confirm you can correctly load the siblings association? And then, is your question mainly a question of how to associate an animal to their siblings? If so, look into cast_assoc or put_assoc in the changeset.

The What’s new in Ecto 2.0 ebook also has examples on many to many: pages.plataformatec.com.br/ebook-whats-new-in-ecto-2-0

Hi Jose.

Yes, I can load the sibling associations using > preload/2.

Well, it was just kind on main question about how to insert the data.

Let’s say we want to insert a new animal with a sibling for the animal with id 4. Actually I’m inserting new data as the following:

sibling = TestApp.Repo.get(TestApp.Animal, 4)
animal = TestApp.Animal.changeset(%TestApp.Animal{}, %{name: "name", info: "info"}) 
animal_with_sibling = Ecto.Changeset.put_assoc(animal, :siblings, [sibling])
TestApp.Repo.insert(animal_with_sibling)

But although is inserting correctly the new entry for an animal, is inserting a sibling with just the id 4 (animal_id column) but null in the other sibling id (animal_sibling_id column).

I already read the ebook, but I wonder if being a self referenced many to many, makes this work different than between different models.

I don’t see any reason why that wouldn’t work. The join table should be properly filled as well as one of the keys in the respective structs.

Well, I figured out how to fix this. I had to add to the many_to_many the > join_keys option:

many_to_many :siblings, TestApp.Animal, join_through: TestApp.AnimalSibling, join_keys: [animal_id: :id, animal_sibling_id: :id]

Notice that preload a sibling using preload/3 only works one side. For example, for a sibling relation relation (animal_id = 4 and animal_sibling_id = 10), preload/3 will only retrieve the sibling when requesting an TestApp.Animal with id 4. I’m not sure that is correct.

Thank you jose.

Notice that preload a sibling using preload/3 only works one side. For example, for a sibling relation relation (animal_id = 4 and animal_sibling_id = 10), preload/3 will only retrieve the sibling when requesting an TestApp.Animal with id 4. I’m not sure that is correct.

This is exact my problem. I have a model Experiment which can have mutual exclusions.
I created a many_to_many association like here, but it has one way relation only. So not mutual at all.
For now my solution is to make another many_to_many and swap keys in join_keys, but I have two associations: my_exclusions and other_exclusions, which looks dirty :slight_smile:

@qgadrian did you find a better solution for the problem?

1 Like

I’m not sure, but I’ve definitely found there are no super nice examples of self referential many_to_many associations, especially when compared to all the other incredible examples out there. :blush:

I had to do something similar to @dsnipe to get the behavior I was looking for. Although, I’m pretty new to Elixir, and programming in general, so I also think I’m just missing the correct way to do it.

My implementation looks like:

many_to_many :relationships, MyApp.Accounts.Person, join_through: MyApp.Relationships.Relationship, join_keys: [person_id: :id, relation_id: :id], on_delete: :delete_all

many_to_many :reverse_relationships, MyApp.Accounts.Person, join_through: MyApp.Relationships.Relationship, join_keys: [relation_id: :id, person_id: :id], on_delete: :delete_all

:heart: :heart: :heart:

1 Like

If anyone wants to submit a pull request to many_to_many function docs to add this example, it would be certainly be very welcome!

2 Likes

Working on it! :blush: