Ordered many-to-many in Ecto?

Does Ecto2 have support for ordered many-to-many? (or one-to-many for that matter) To make it concrete, imagine a book with multiple authors (their order is crucial), a user profile with favorite topics (in order of preference), etc.

The simple solution of having an intermediate table (book_author, user_topic, etc) doesn’t encode order, so typically a 3rd field would be added to encode the position, eg: book_id, author_id, author_rank.

In https://github.com/elixir-ecto/ecto/issues/659 I see that Ecto can now apparently order_by on preloading one of the associates, but can it order_by on the association itself (to order_by author_rank in a book_author intermediate table?)

Does ecto have facilities to manage that?

1 Like

Short answer is no. You would need to use has_many :through instead of many_to_many. Another alternative is to have author_ids as an {:array, :integer} in the books table.

1 Like

Thanks Jose -

I assume you mean has_many directly linking to the intermediate table, as in has_many :books_tables, through: etc... and then preloading that books_table association to read its author_rank field? (as in the documentation for has_many/3 )

(I was indeed considering an author_ids array, but that loses me the straightforward SQL querying capacities - all books by an author, etc)

1 Like

Well PostgreSQL has fantastic syntax for it, easily wrapped in fragments. :slight_smile:

1 Like

Hi, @kmptkp. What’s your final solution to this problem? Recently I came across this problem and find a pretty good library: https://github.com/zovafit/ecto-ordered.