Flop ordering by distinct column first and then by sort_by

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: 18: SELECT and Ecto.Query — Ecto v3.14.0.

You will need to wrap either the whole query or part of it in a sub query.

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.