How to correctly update many-to-many associations with put assoc

Phoenix Forum Questions / Help
,
1

Hello everyone!
I have a tagging system, and I pass new tags via put assoc, then the link is added to the join table, but from there the old links are removed because I have the “delete” on_replace option set. By link, I mean a row in the join table (user_id; tag_id).
As I understood from other threads on the forum and documentation, I need to pass the existing tags along with the new ones explicitly to re-link, and I plan to do it as follows:

  1. Get the id of the attached tags to the current user from the join table.
  2. Get the tags from the tags table using the obtained tag id.
  3. Pass the received tags to the put assoc along with the new tags.

I would like to know if this is the best way, because in this case, we are accessing two database tables every time, or is there another way?

Thanks in advance!

1 Like
2

There might be a better way for tags.

https://hexdocs.pm/ecto/constraints-and-upserts.html

1 Like
3

I think Ecto is great but in this case it makes everything needlessly complicated. When I was a beginner I had the same troubles understanding and I couldn’t believe that so much communication with the database was apparently necessary. The guide is done well and it illustrates the problems step by step but the issue is that all that information isn’t really essential. The problem is simple: give the database a list of tags and return them with ids, even if they’re already there. No need to consider double insertions, race conditions, constraints and put_assoc and cast_assoc. You only need to understand one function and a few options to achieve this thing.

Here is my modified version from the guide. I didn’t test it, but I think Repo.all(...) is unnecessary and the tags can be upserted and returned in one go.

def upsert_tags(names) do
  maps =
    Enum.map(names, &%{
      name: &1,
      inserted_at: {:placeholder, :now},
      updated_at: {:placeholder, :now}
    })

  now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)

  Repo.insert_all(
    MyApp.Tag,
    maps,
    placeholders: %{now: now},
    on_conflict: [set: [name: name]],
    conflict_target: :name,
    returning: true
  )
  |> then(fn {_count, tags} -> tags end)
end

PS: I’m only talking about upserting tags, not the associations between the tags and any other table.

2 Likes
4

Oh, I already use this, but when I add new tags with

Ecto.Changeset.put_assoc(:tags, parse_tags(params))

Links to previous tags from the Join table are removed.
For example, I have in join table next rows:
1 1 (user id/tag id)
1 2
1 3
1 4
1 5
(User one has 5 tags)
When I add tags “tag6” tag7", I got

1 1 (user id/tag id)
1 2
1 3
1 4
1 5
1 6
1 7
But if i add tag1 again and after that i add new tag (tag 8), i got

INSERT INTO "tags" AS i0 ("tag","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT ("tag") DO UPDATE SET "tag" = $4 RETURNING "id" ["tag8", ~N[2021-10-30 11:29:27], ~N[2021-10-30 11:29:27], "tag8"]
[debug] QUERY OK db=0.2ms idle=1766.2ms
begin []
[debug] QUERY OK source="posts_tags" db=0.3ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 7]
[debug] QUERY OK source="posts_tags" db=0.2ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 6]
[debug] QUERY OK source="posts_tags" db=0.2ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 5]
[debug] QUERY OK source="posts_tags" db=0.2ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 4]
[debug] QUERY OK source="posts_tags" db=0.2ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 3]
[debug] QUERY OK source="posts_tags" db=0.1ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 2]
[debug] QUERY OK source="posts_tags" db=0.2ms
DELETE FROM "posts_tags" AS u0 WHERE ((u0."user_id" = $1) AND (u0."tags_id" = $2)) [1, 1]
[debug] QUERY OK db=0.4ms
INSERT INTO "posts_tags" ("tags_id","user_id") VALUES ($1,$2) [8, 1]

[debug] QUERY OK db=9.7ms
commit []

That is, all my tags from the joins table are removed and a new tag is added. I don’t want my joiner table to delete tags

5

Just a guess off the top of my head, have you tried wrapping in a transaction with Ecto.Multi?

1 Like