A case against many_to_many

On the Slack Channel for Elixir there’s often the situation of people coming from other languages/frameworks to elixir and especially ecto to ask about “How to model many-to-many relationships”.

Most of the times the usual answer comes as a surprise to people, as usage of Ecto.Schema.many_to_many is almost actively discouraged. I’ll try to bring some insights into why this is the case and what’s the better alternative.

many_to_many in ecto #

For quite some time ecto didn’t even come with many_to_many relationships and people instead modeled them explicitly using plain belongs_to/has_many relationships on all tables involved. But due to popular demand explicit support was eventually added. (1)

There’s one caveat though. It was added with the intent of hiding away the implementation detail of needing a join-table to create the relationship between the two many-to-many schemas. Because of that the join table can only support two foreign key columns and nothing else on the table. Those two are everything needed for modeling the relationship in the db and the only amount of information, which can be used without additional APIs for retrieving it.

This is a serious limitation for almost all many-to-many relationships out there. I’ll list a few examples:

Many To Many in ecto #

All of the above are better modeled with an explicit schema for the join table. This way additional fields can be added to the relationship without a problem (or any new/special api to learn). For quick access and preloading purposes you can still have relationships between the outer schemas of the relationship as well.

# Standard belongs_to/has_many
Company has_many CompanyUsers
CompanyUser belongs to Company
CompanyUser belongs to User
User has_many CompanyUsers

# But also has_many through
Company has_many Users through CompanyUsers
User has_many Companies through CompanyUsers

Documentation and Actual code examples

Conclusion #

many_to_many in ecto is probably not going anywhere. But now you’re aware of it’s limitations. Unless you really don’t care about the join table and you’re quite sure this won’t change in the future it’s better to just model the relationship explicitly and use has_many‘s through option to get all the convenience of loading relationships via the join schema if that’s all you need.

1: http://pages.plataformatec.com.br/ebook-whats-new-in-ecto-2-0


Now read this

Ecto: When to cast

People starting out with ecto are often confused by all the methods of getting changes applied to a changeset and therefore the database. Especially the options around assocications are often problematic. To bring some clarity into the... Continue →