Many-to-many where joining model needs to be updated also

Questions / Help
#1

I’m wondering what the syntax will be if I have to save a many-to-many association to the database, which also involves updating the joining model. Basically, I have a multi-room chat app, where users and rooms are associated through the rooms_users table, and this table also has a ‘status’ attribute (for online, offline, etc.).

Supposing the schema are:

  schema "users" do
    # The rooms the user is currenly logged into
    many_to_many :rooms, Elemental.TxChat.Room, join_through: Elemental.TxChat.RoomUser, on_delete: :nothing
    timestamps()

  end

schema "rooms_users" do
    belongs_to :room, Elemental.TxChat.Room
    belongs_to :user, Elemental.TxChat.User
    belongs_to :app, Elemental.TxChat.App
    field :status, :integer # offline, online, etc.

    timestamps
  end

schema "rooms" do
    field :name, :string
    # The user id that created this room
    field :created_by, :integer
    field :created_from_app, :integer

    many_to_many :users, Elemental.TxChat.User, join_through: 
      Elemental.TxChat.RoomUser, on_delete: :delete_all

    timestamps()

    @fields ~w(name created_by created_from_app)
  end

How can such an association be saved and retrieved? I came across some examples that do something like user |> change |> put_assoc(:rooms, [room]) |> Repo.update! but the problem here is handling of the intermediate model.

#2

It is likely you don’t want to use many_to_many then because it was designed for cases you don’t really care about the intermediate table. There are a couple different ways you could do this. For example, you could only care about the RoomUser and insert it directly. Assuming you have a room and a user:

room_user = %RoomUser{user: user, room: room, any_other_parameter_you_care_about: "value"}
Repo.insert!(room_user)

If you need to change multiple entries at once, you can rely on Ecto.Changeset and use functions like put_assoc/cast_assoc if you want to.

1 Like
#4

Well, hello Jose! Wasn’t expecting you chime in yourself. :smile:

Pardon my ignorance, but are you suggesting that I do away with many_to_many and instead treat RoomUser as a separate model? And if I do that, it appears to me that I’ll end up writing separate get and get_by statements when looking for data in the database. Is that it?

Also, you said there are a couple of way to do it. I’m sorry but I can’t find the second in your reply (possibly because I don’t know Ecto that well). Care to point it out, please?

And many thanks in advance!

#5

@dotslash I am glad to help!

You can still keep the many_to_many around if you want to. Because you are using many_to_many to read the data, it doesn’t necessarily imply you need to use many_to_many to write the data too! For example, if you have both the room and user entries and you want to associate them, doing a single Repo insert with RoomUser may be the simplest approach.

However, even if you decide to remove the many_to_many, you can still use preload to load all room_users with the user:

from r in Room, preload: [room_users: :user]

Or use has_many :through to do it at once:

# In your schema
has_many :users, through: [:room_users, :user]

# In your queries
from r in Room, preload: :users

Oops, that was a left-over of the original reply. I will edit it. Sorry about that!

4 Likes
#6

Let me just say it’s times like these that I really admire guys like you. I thought I’d get no help on this! Many, many thanks. :relaxed:

2 Likes