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.
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
Well, hello Jose! Wasn’t expecting you chime in yourself. 
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!
@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
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. 
2 Likes