How to associate a new many-to-many record to existing records only by ID?

Let’s say I have Tags and Comments with a many-to-many relationship.

I want to create a new Comment record and associate it to three existing Tag records, but I only have the Tag ID’s to hand.

Is it possible to do something like (in pseudo code):

  comment_data: ...,
  tags: [1, 2, 3]
|> Repo.insert()

I can get my example to work if I pass in the Tag structs, but that requires a database call to get them. I feel like it should be possible to pass in the ID’s as that’s all that’s being added to the underlying join table…?

Any help would be greatly appreciated!!

Welcome to the forum!

Yes you should be able to do it with put_assoc/4

If the comment is already persisted to the database, then put_assoc/4 only takes care of guaranteeing that the comments and the parent data are associated. This extremely useful when associating existing data, as we will see in the “Example: Adding tags to a post” section.

adding tags to a post

Thank you! And thank you for the reply!

I could only get put_assoc working when passing a full struct, like so:

  comment_data: ...,
  tags: [%Tag{data: ...}, %Tag{data: ...}, %Tag{data: ...}]
|> Repo.insert()

When only passing the ID it would try to insert a new record into the associating table (Tags in this case), but I only want to associate to existing Tag records. :frowning_face:

My apologies, I didn’t think my answer through as you specifically didn’t want to make another DB call if you already know the tag ids.

In that case, once you create your comments and have their ids, you can Repo.insert_all/3 directly into the many-to-many table.

It expects a schema module (MyApp.User) or a source (“users”) or both ({“users”, MyApp.User}) as the first argument. The second argument is a list of entries to be inserted, either as keyword lists or as maps.

So in you case something like:

Repo.insert_all("many_to_many_table", [%{comment_id: 1, tag_id: 1} , %{comment_id: 1, tag_id: 2} ]) 

Structs and schemas are very helpful conveniences but Ecto does let you work closer to the db without needing them, too.