Many to many – once again… (extra data on the join table)

In a many-to-many relationship, let’s say users and chatrooms for a good example, there’s a join table users_chatrooms and appropriate Ecto.Schema entries:

many_to_many :users, PhxApp.Accounts.User, join_through: PhxApp.Accounts.UserChatroom


many_to_many :chatrooms, PhxApp.Chatrooms.Chatroom, join_through: PhxApp.Accounts.UserChatroom

but let’ say I need to store additional information like whether given user is the “owner” or a “moderator” of a given chatroom and the obvious place to store this kind of info is on the join table. So I have a role_id column on that table meant to store this information. Now, how does one do the CRUD stuff in such setup? Let’s say in the simplest case first - the user who creates the chatroom is always the owner but the role_id has to be set explicitly rather than being a DB default?


It’s totally doable… but not with many_to_many.

With two has_many, and one should be has_many through.

1 Like

I see. Any suggested readings (other than Ecto docs) on how does one do such things “properly” in Phoenix (Ecto)?

You can find some docs here: Ecto.Schema — Ecto v3.7.1

I blogged about a bit of the background to why things are the way they are:

1 Like

Tnx. I found your blog post already before and it gives a good background but am looking for something lower level. More of “how does one work with it”. I know the Ecto docs are there so explicitly excluded them in the question :wink: Found also this thread: Ecto associations and the purpose of has many through and many to many But still - do I need to take low level care of things (transactional creating of join records for example like the OP there does) or are there some built-in mechanisms / “best practices” that I’d be best aware of before I create lots of completely unneeded code :wink:

Can you explain how your insert/update code looks like right now?

It doesn’t [exist yet]. I first modelled the relations using has_many, then I realised that I had no idea how to store extra data on the join table. I found some bits about using has_many through instead but still asked here, seeking either confirmation or some light-shed. Now that you guys confirmed that many_to_many is not the way to go, I want to continue and write what you asked about. Preferably being equipped with some “correct” examples to look up to.

So if there’s no code: Do you know how you receive the data to be inserted? If there’s no code yet you can almost ignore the fact that there is a join table between users and chatrooms. You can just model it as User <> ChatroomUser <> Chatroom. Ecto will probably take care of the rest if relationships between schemas are setup.

In the simplest example of “create” I receive the chatroom metadata (like name for example) from the web form. Since the user creating it will be the owner, this role needs to be assigned when creating the join record. But in the linked thread the OP creates the join record “manually” within a Multi transaction. Sure that will work but is this what one has to do?

So the user already exists. So essentially we don’t need to handle the many to many anymore to begin with given you’re not actually affecting both ends. All you insert is the chatroom and the first user of it:

%Chatroom{name: "chatroom_name", chatroom_users: [%{user_id:, role: :owner}]} |> Repo.insert()

One important thing ecto doesn’t do, but people somehow expect is having an API to create relationships to already existing records. Just set the :record_id on wherever you need it. There is Ecto.build_assoc, but usually it doesn’t make things simpler.


Yes, that’s the simplest use case. There will be other, like “a non-yet-existing user has been invited to join the chatroom via e-mail” and then both the user and the chatroom association need to be created with a specific role set by the inviting user. In such case though I find it more “okay” to create the user first and then the rest. Or to manually build a transaction around this process. I somehow found it hard to believe that I model the associations and then still have to go that low level even in the simplest case

TNX. And I don’t need to build a transaction, do I? All will be taken care of, right?

You don’t. Ecto will handle that for you when using the schema/changeset assoc handling. It’ll still be a transaction in the end.

1 Like