Как исправить сортировку полученных данных из БД?

Question

[Антон]

Есть такой запрос:

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)

Нужно отсортировать полученные данные только по position, но на деле сейчас полученные данные сортируются по post_id, а сортировка по position игнорируется.

Притом если схитрить таким образом:
.order(position: :desc, post_id: :desc)

То ошибка:

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

Точно такая же ошибка, если вообще не указывать "post_id" в ORDER. "post_id" должно всегда находиться в ORDER и всегда должно быть первым, так как используется DISTINCT.

Как исправить?

Comments

[Павел Грудинкин]

position это поле posts_and_images или юзера?, если первое попробуйте ('posts_and_images.position DESC')

[Павел Грудинкин]

и в селекте так же posts_and_images.position

Answer 1

[jarosluv]

Прежде всего нужно понять, что вы вообще хотите получить от запроса? Сформулируйте, пожалуйста, простыми словами и озвучьте.

В вашем запросе без указания сортировки база данных не может понять, по какому принципу отбирать уникальные post_id. Добавив это поле в ORDER BY, запрос начинает проходить, однако следующие уточнения по поводу позиции отбрасываются. Но это тоже логично! Ведь сортировка уточняется только когда по предыдущему полю идут одинаковые значения, а тут по полю post_id все значения разные (благодаря distinct).

Следовательно, вам нужно использовать вложенные запросы. Сначала находим уникальные посты, а потом внешним селектом сортируем по position.

Comments

[Антон]

Прежде всего нужно понять, что вы вообще хотите получить от запроса? Сформулируйте, пожалуйста, простыми словами и озвучьте.

Есть множество записей (posts_and_images), принадлежащих конкретному пользователю. Исходный массив выглядит так - множество записей с одинаковыми "post_id", а также остальными атрибутами.
Хочу сделать следующие - чтобы оставалась одна уникальная по "post_id" запись, но отсортированная по "position".

На текущий момент реализация уперлась в такой замечательный (на самом деле нет) костыль:

.sort_by { |item| item. position }.reverse.uniq! { |item| item. post_id }

Разумеется, тут вынимаются все записи, а затем уже производится работа над Hash'ом. Это очень неоптимизированное решение, но я не знаю как иначе реализовать свою идею.

[jarosluv]

Антон, есть 2 варианта решения задачи.

Если у вас position для каждого post_id начинает свой отсчёт с нуля и они уникальны в рамках post_id, а также не содержат null-значений, то очевидно мы можем просто-напросто выбирать именно эти записи через условие where: `select * from posts_and_images where position = 0`.

Если position может иметь любое значение, например для каких-то post_id начинается с нуля, а для каких-то с 2 и идти не подряд, то нужно использовать оконные функции: `select * from (select *, row_number() over (partition by post_id order by position) as number from posts_and_images) ordered where number = 1`.

Answer 2

[artem_music]

Разве нет ошибки в коде? Для :post_id ведь надо прописать :desc или :asc, разве нет?
Или попробуйте следующие варианты:

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

Comments

[Антон]

Я не знаю как не указывать "post_id" в ORDER. Я не хочу сортировать по post_id, но если я уберу это поле, то получу ошибку:

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