Many_to_many - association with existing records

Questions / Help
1

I’m just working through Programming Ecto.

I understand how I can add new many-to-many assocs with cast_assoc or put_assoc. But those work with the assoc as a whole. I just want to connect two exisiting records. There is no mention how to do that in the book and not in the Ecto documentation. Which I find odd.

In the book’s Music-DB example there is this table:

create table(:albums_genres) do
  add(:album_id, references(:albums))
  add(:genre_id, references(:genres))
end
end

and this schema:

schema "albums_genres" do
  belongs_to(:albums, Album)
  belongs_to(:genres, Genre)
end

To add a new genre to an album the “normal” way would be (preload required)

put_assoc(album_changeset, [new_genre | album.genres])

I thought I could just

%AlbumGenre{albums_id: album.id, genres_id: genre.id} |> Repo.insert()

But no:

** (Exqlite.Error) table albums_genres has no column named albums_id

…and album_id is not in the Struct.

So this fix in the Schema: (EDIT: better fix: :albums → :album , :genres → :genre)

schema "albums_genres" do
  belongs_to(:album, Album)
  belongs_to(:genre, Genre)
end

And I can do

%AlbumGenre{album_id: portrait.id, genre_id: live.id} |> Repo.insert()

This seems such an common thing to do, yet Ecto and the documentation and the Standard-book seem to not want me to do that. Why? Is this evil somehow?

2

You could, but in your migration fields are names as album_id and genre_id (without s in the end)

3

Good that you did not read to end, so I now understood how to more easily fix that. :wink:

Instead of setting the foreign keys I can just

schema "albums_genres" do
  belongs_to(:album, Album)
  belongs_to(:genre, Genre)
end

(:albums → :album , :genres → :genre)

4

This naming magic is also an issue for me sometimes, you can alter the belongs to key by defining it manually with foreign_key option.

Another thing is that it might be cleaner to use a many_to_many relationship in cases of these mapping tables.

5

These are many-to-many:

schema "albums" do
  many_to_many(:genres, Genre, join_through: "albums_genres")
end

schema "genres" do
  many_to_many(:albums, Album, join_through: "albums_genres")
end
1 Like
6

Just found out, that many-to-many does not support any extra columns in the join-table.

So this is not a fit for my use case anyway.

https://kobrakai.de/kolumne/a-case-against-many_to_many/

7

You can define a schema for the join table and define the extra fields that way. I have done it.

8

I can add a field

schema "albums_genres" do
    belongs_to(:album, Album)
    belongs_to(:genre, Genre)
    field(:role, :string)
end

And also put data there

%AlbumGenre{album_id: portrait.id, genre_id: live.id, role: "TESTROLE"} |> Repo.insert()

But how do I get the role-data for an Album and Genre. The join-table does not seem to exist for Ecto.

9

Sadly many_to_many is limited in this regard, but at the same time you can use it while at the same time have other relations:

schema "albums" do
  many_to_many(:genres, Genre, join_through: "albums_genres")
  has_many(:album_genres, AlbumGenre)
end

schema "genres" do
  many_to_many(:albums, Album, join_through: "albums_genres")
  has_many(:album_genres, AlbumGenre)
end

This way you have bidirectional access to the relations and can use either cast_assoc or put_assoc.

2 Likes
10

Hey thats sneaky - and works. Thank god, my head was just breaking reading the has_many/through documentation.

11

You will get used to it very fast. Actually the easiest way I understood it is the fact that only belongs_to requires and actual foreign key field, all other relationships are purely virtual, they are based on join queries, so you can have a mix of them from multiple directions.

As a word of advice, I would discourage using in production extensively put_assoc and cast_assoc as they tend to be confusing and makes code unreadable at some point, instead opt to use discreet operations and things like Ecto.Multi.

4 Likes