Ecto: Limit per item in related table

Questions / Help
,
1

I hope this is the right place to ask this. I’m ultimately using Phoenix, but the question itself is Ecto-specific.

I have two tables: one holds languages (Ga, Twi, Fante, Ewe, Nzema, etc). The other one holds books which has a reference to the language it is written in, along with some other information. There is also a field called read, which is simply just the number of people who have read the book.

I want to be able to render a list of all the languages that have at least one book and for each of those languages show the 10 most read books. I got lazy and thought I could do something like the following:

book_query = from b in Book, join: l in Language, on: b.language_id == l.id, order_by: [desc: :read], limit: 10
Repo.all(from l in Language, preload: [books: ^book_query])

Of course, I should have understood that limit: 10 is going to apply across the book_query, not be applied per language, as I wanted. The problem is I’m a bit stumped as to how to solve this without resorting to running multiple queries, which I would like to avoid.

2

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.

3 Likes
3

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

4

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.

5

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.

1 Like