How to fix sorting of received data from DB?

Question

PostgreSQL

There is a request like this:

posts_and_images = current_user.posts_and_images.select('DISTINCT ON (post_id) post_id, id, user_id, image_id, position').order(:post_id, position: :desc)

You need to sort the received data only by position, but in fact, now the received data is sorted by post_id, and sorting by position is ignored.
Moreover, if you cheat in this way: Then the error is:
.order(position: :desc, post_id: :desc)

PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1

Exactly the same error, if you do not specify "post_id" in ORDER at all. "post_id" must always be in ORDER and must always come first because DISTINCT is used.
How to fix?

Answer 1

First of all, you need to understand what you generally want to receive from the request? Formulate, please, in simple words and sound.
In your query, without sorting, the database cannot figure out how to select unique post_ids. By adding this field to the ORDER BY, the query starts to pass, however, the following position qualifications are discarded. But it also makes sense! After all, sorting is specified only when the same values ​​go to the previous field, and here all the values ​​\u200b\u200bare different in the post_id field (due to distinct).
Hence you need to use nested queries. First we find unique posts, and then we sort them by position using an external select.

Answer 2

Isn't there an error in the code? For :post_id, you need to write :desc or :asc, right?
Or try the following options:

Thing.all.order("updated_at DESC, price ASC")
Model.order(foo: :asc, bar: :desc)
Model.order(:updated_at).order(:price)