Unexpected limiting behavior from Repo.all with joins

Questions / Help
, , ,
1

So, in my fairly typical Elixir chat module, I have topics, messages, and users. Topics have many messages, users have many messages, etc etc

I have a fairly simple Repo.all for getting messages off of a Topic by Topic UUID, however when I add a limit: ^limit clause anywhere in the query, I end up with results ordered by users.

This query retrieves the last 20 messages properly ordered:

    Repo.all from t in Topic,
      where: t.uuid == ^topic_uuid,
      join: m in assoc(t, :messages),
      join: u in assoc(m, :user),
      order_by: [asc: m.id],
      select: %{username: u.username, message: m.message}

If user Alice has 20 messages and user Bob has messages interspersed between those messages, adding limit: 20 anywhere in the above query gives me only Alice’s last 20 messages.

Do I need a subquery for this, or am I just limiting wrong due to the join assoc?

1 Like
2

Can you run Ecto.Adapters.SQL.to_sql(:all, Repo, queryable) to get the raw SQL that will be executed (or get it from the debug log)?

1 Like