DISTINCT ON requires matching ORDER clause

[rangerscience]

Per Postgres docs - and experimentation! ;) - specify that the DISTINCT ON clause must have a matching ORDER BY, such that the columns used in the distinct clause are the left-most in the ORDER clause. So you end up needing to do:

Model.distinct_on(:col).order(:col)

It would be nice (I think?) if this was handled automagically. (Or is this supposed to be automagically handled by AREL?)

Bonus - This plays weird with other things that apply ordering (ActiveAdmin). My guess (and hope) is that there's a way to say "no, this ordering always is left-most", so that when something like AA adds their ordering, it doesn't break the DISTINCT ON.

[ezekg]

I think you could use a CTE to retain current order and then use distinct_on with the CTE rows:

cte      = Model.with(cte: Model.some_scope_not_ordered_by_col)
distinct = cte.from('cte as models').reorder(nil)
                                    .distinct_on(:col)