Context

When writing an application, you sometimes end up needing a numbered list ordered by some parameter list. For example, a leaderboard of people ordered by points each player has. To get that, you need to somehow take from the database. To have that, not only do you need to take a list of rows from the database along with the row number, but you also you need to somehow take out parts of the results in order.

Getting rows

There's row_number() in Ecto, but it works only in WindowApi. To make it work properly, I needed to join a subquery before filtering results by row number, so we start with something like code below.

subq = from(t in SomeTable,
  distinct: t.some_field,
  select: %{t_id: t.id, place: over(row_number(), order_by: [desc: :points, asc: :id])}
)

This gives us a list of places and IDs in these places. Basically a list of items. Now we need a real query.

after_place = 10

from(t in __MODULE__,
  join: st in subquery(subq),
  on: t.id == st.t_id,
  order_by: st.place,
  where: st.place > ^after_place,
  select: %{stats: t, place: st.place},
  limit: 30
)
|> Repo.all()

We need a subquery for Ecto/SQL to let us filter by places. And we need this filtering so we can batch getting the leaderboard.

You may also use rank() instead of row_number(), but rank() may return multiple people having the same place. This may be something you want, but it wasn't for me.

Summary

This solution has a problem where points may change value, and after loading later on, it may return one person who fell down in the leaderboard for page one and then later for page two. This is a problem mainly for LiveViews and loading when you load new data without removing old rows. In my experience it's a risk worth taking, as long as you ensure the correctness of your HTML IDs.