Ecto: Limit per item in related table

Lateral joins can do what you want to do in a single query. With lateral joins you can run a subquery per row of the parent.

Thanks. I heard about lateral joins, but never dug into it. I guess this is the time then.

So, after a toying around a bit with this, I came up with this:

subquery = from b in Book, where: parent_as(:language).id == b.language_id, order_by: [desc: :reads], limit: 2
query = from l in Language, as: :language, inner_lateral_join: b in subquery(subquery), select: b
query |> Repo.all

This is almost there. I have verified that I get a maximum of 2 books per language. I could preload the languages to get the language name. However, I wanted books to be mapped to languages so that I can easily iterate over languages and then in an inner loop iterate over the books that “belong to” that language.

I’m sure that toying around with Elixir a bit I could figure out some way to arrange the data the way I need it. But if there is a clean way of having Ecto do that, I’d obviously prefer that.

I got it working. Just in case it would help somebody down the road, here is what I did:

subquery = from b in Book, where: parent_as(:language).id == b.language_id, order_by: [desc: b.reads], limit: 2
query = from l in Language, as: :language, inner_lateral_join: b in subquery(subquery), select: l, preload: [books: b]
query |> Repo.all

I had simply left out the preload, that was all.