lcabrini
Ecto: Limit per item in related table
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.
Marked As Solved
lcabrini
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.
Also Liked
LostKobrakai
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.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Deploy Phoenix to your own VPS with PaaS ease: push-to-deploy, zero downtime, auto SSL, managed Postgres. 14-day free trial at potions.io
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"