Сортировка списка сообщений по дате создания, с DISTINCT и LEFT JOIN
В настоящее время у меня есть список сообщений, отправленных от определенного пользователя другим пользователям. Этот список содержит информацию об идентификаторе получателей, а также время отправки сообщения (вместе с другими столбцами).
Мне нужно отсортировать этот список по времени, когда сообщение было отправлено, а также сохранить сообщения уникальными для получателей. Кроме того, мне нужно оставить информацию о присоединении из базы данных пользователей, которая содержит столбцы с именами и фамилиями получателей.
Для этого я использую следующий запрос SQL:
SELECT
DISTINCT ON (uc.to_user_id)
uc.id,
uc.to_user_id,
uc.created_at,
u.first_name,
u.last_name
FROM user_communication uc
LEFT JOIN "user" u ON (u.id = uc.to_user_id)
WHERE uc.from_user_id = :user_id
Где user_id — это параметр, который был передан в PHP.
Однако этот запрос не сортирует список сообщений по времени их отправки. Сообщения не могут быть упорядочены по date_created из-за использования DISTINCT для uc.to_user_id.
В настоящее время вывод выглядит так:
"communications": [
{
"id": "1da9f6ea-8b4d-11e6-8698-2bd5d7c3680b",
"created_at": "2016-10-05 22:43:20",
"user": {
"user_id": "a1cc4284-60cd-11e6-a419-e7a2176c4be3",
"first_name": "Test",
"last_name": "Account"}
},
{
"id": "05d6c44a-1be7-11e8-be13-abe8f9af8d9c",
"created_at": "2018-02-27 17:52:48",
"user": {
"user_id": "c84d2ff0-f537-11e6-b318-f7c577b9597c",
"first_name": "test",
"last_name": "test"}
},
{
"id": "16cc5d64-1be7-11e8-87dd-7fa156fb7904",
"created_at": "2018-02-27 17:53:16",
"user": {
"user_id": "c8c6ff00-452f-11e6-91e3-cbb55bd856a3",
"first_name": "test",
"last_name": "account"}
}
]
Этот список в настоящее время сортируется буквенно-цифрово по user_id (который является uc.to_user_id).
Мне интересно, что может быть лучше, чтобы справиться с этим.
Решение
SELECT *
FROM
(
SELECT DISTINCT ON (uc.to_user_id)
uc.id,
uc.to_user_id,
uc.created_at,
u.first_name,
u.last_name
FROM user_communication uc
LEFT JOIN "user" u ON (u.id = uc.to_user_id)
WHERE uc.from_user_id = :user_id)
ORDER BY created_at
Другие решения
Возможно, вы также можете поделиться своей схемой для этих двух таблиц, но просто упростите столбцы до того, что нам нужно увидеть (то есть нам просто нужно несколько столбцов, чтобы мы могли сосредоточиться на основной проблеме).
SELECT
uc.to_user_id,
uc.id,
uc.to_user_id,
uc.created_at,
u.first_name,
u.last_name
FROM user_communication uc
LEFT JOIN "user" u ON (u.id = uc.to_user_id)
WHERE uc.from_user_id = :user_id
GROUP BY uc.to_user_id
ORDER BY uc.to_user_id, uc.created_at
Какие результаты вы получаете из вышеперечисленного? Мне любопытно, как выглядят дублирующиеся строки, от которых вы хотите избавиться DISTINCT в этом случае.
С GROUP BY затем вы можете суммировать или считать определенные атрибуты в зависимости от того, что вы хотите сделать. Возможно, это не поможет вашей проблеме, поэтому вы можете удалить ее.
Чтобы получить только самое последнее сообщение для каждого получателя, вы можете использовать оконные функции в соответствии с:
SELECT * /* Be better to explicitly list your columns again. */
FROM (
SELECT uc.id
, uc.to_user_id
, uc.created_at
, u.first_name
, u.last_name
, ROW_NUMBER() OVER (PARTITION BY uc.to_user_id ORDER BY uc.created_at DESC) as rn
FROM user_communication uc
LEFT JOIN "user" u ON (u.id = uc.to_user_id)
WHERE uc.from_user_id = :user_id
) s1
WHERE s1.rn = 1
Я не уверен, как PHP играет с оконными функциями, но это должно дать вам представление о том, как решить эту проблему.
detector