In Flop when I have a query which uses an explicit distinct clause, then it sorts by the distinct column first, and then by the field provided by sort_by. This throws off the pagination in cursor-based pagination. How do I ensure the result sets are distinct but sorted by the sort_by field?
Postgres requires the ORDER BY clause to start with columns used in DISTINCT ON, which is why Ecto is automatically adding an order_by when you use distinct. See PostgreSQL: Documentation: 17: SELECT and Ecto.Query — Ecto v3.12.4.
You will need to wrap either the whole query or part of it in a sub query.
1 Like
I’m sort of struggling with this. This is my query. Flop is sorting variants.starts_at_utc. When I wrap this in a subquery, I still need to join with variants in the outer query since Flop needs to access variants.starts_at_utc, so I need to add DISTINCT on the outer query as well so that multiple Products don’t show up (since products can have multiple variants and I only want one record for each product selected). So the subquery approach isn’t working for me.
Are there any strategies to get around this?
from(p in Product,
distinct: p.id,
inner_join: variants in assoc(p, :variants),
as: :variants,
left_join: favorites in assoc(p, :favorites),
left_join: favorite_user in assoc(favorites, :user),
as: :favorite_user,
left_join: favorite_product in assoc(favorites, :product),
where: account.id == ^account_id and is_nil(p.deleted_at) and is_nil(variants.deleted_at),
where: p.is_giftcard == false and p.type_id == "product_type_event",
preload: [
variants: variants,
favorites: {favorites, user: favorite_user, product: favorite_product}
]
)
I ended up doing this:
distinct: [{^direction, variants.starts_at_utc}, p.id],
Basically, I specify the sort direction so it’s the first in the order by clause. Flop later comes on and does the same thing so it’s a little redundant but no harm done. I get the distinct values and the right sort by “overriding” Flop appending the order by clause.
