Ecto: How to deal with many-to-many relations with more properties on them?

[mikl]

So if we have a Group entity and a User entity, and you have group memberships binding them together, but this group_users table also has metadata like membership level and join date, what is the right way to model this in Ecto?

Simply adding a many_to_many :users, Accounts.User, join_through: "group_users" to the Group entity (and the reverse binding on the User entity) seems like an obvious way to do it, but might make it tricky to access the extra fields.

Alternatively, one could add a GroupMembership schema that represents the join table.

What would be your recommendation?

[pdgonzalez872]

I prefer adding the JoinTable module. You get the timestamps for free and you may want to do more on those depending on your needs. Seems less magical to me.

[dimitarvp]

@mikl

Alternatively, one could add a GroupMembership schema that represents the join table.

I would go for that because it explicitly states your intent and makes the code more readable and intuitive to read through. And gives you the place to put the extra metadata as well.

[mythicalprogrammer]

You can do what I’ve stated here:

Design Question: How do you handle many-to-many relationships with additional fields

I actually do many_to_many and has_many at the same time for one table. An example would be the actor schema will have a many_to_many association with movies and also have a has_many to the join table between actor schema and movie schema. The movie table will have a many_to_many with actor schema and a has_many with the join table between actor and movie. The join table will have two has_many association, one for actor schema and the other for movie schema. It works out pretty great. The many…

Add a has_many on top of the many_to_many.

The has_many refer to the join table and just preload the join table to access the fields in the join table. Unless I’m misreading your post.

[cmkarlsson]

I asked the same thing a while back. This thread might give you some pointers: Many-to-many association table with extra columns

[chrisjowen]

@mikl

Simply adding a many_to_many :users, Accounts.User, join_through: "group_users" to the Group entity (and the reverse binding on the User entity) seems like an obvious way to do it, but might make it tricky to access the extra fields.

I’m curious with this that you state you might find it ‘tricky’, is there even a way of doing this? I have a lot of these Join table schemas for the additional metadata but would love an alternative

[mikl]

@chrisjowen

is there even a way of doing this?

I’m pretty new to Elixir, so I’m not really one to give an authoritative answer here, but I don’t know of a way, other than having to write custom queries to access the extra fields.