У меня есть такая таблица сообщений:
message_id | body | from_user_id | to_user_id | is_read | sent_date
---------------------------------------------------------------------------
1 | hello | 23 | 31 | false | 2 min ago
---------------------------------------------------------------------------
2 | thank you | 28 | 31 | true | 4 min ago
---------------------------------------------------------------------------
3 | how are you? | 31 | 28 | false | 1 min ago
---------------------------------------------------------------------------
4 | a-ha | 29 | 31 | false | 6 min ago
Я хочу получить количество непрочитанных сообщений и последнее сообщение для текущего пользователя (например, user_id 31)
желаемый результат:
from_user_id | to_user_id | body | sent_date | unread_message_count
-------------------------------------------------------------------
23 | 31 | hello | 2 min ago | 1
-------------------------------------------------------------------
31 | 28 | how are you? | 1 min ago | 0
-------------------------------------------------------------------
29 | 31 | a-ha | 6 min ago | 1
Я использовал код ниже:
WITH ranked_messages AS (
SELECT
messages.from_user_id, messages.to_user_id,
messages.body, messages.sent_date,
ROW_NUMBER() OVER
(PARTITION BY messages.from_user_id
ORDER BY messages.sent_date DESC) AS message_rank
FROM
messages
WHERE
messages.to_user_id = 31 OR
messages.from_user_id = 31
)
SELECT *
FROM ranked_messages
WHERE message_rank = 1;
но я хочу также unread_message_count
на пользователя.
для примера:
select
count(*) as unread_message_count
from
messages
where
to_user_id = 31 and
is_read = false and
from_user_id = 23
Это должно быть рассчитано для каждого пользователя, который отправляет сообщения пользователю, и наоборот.
Как добиться такого результата?
Этот код не работает и выдает ошибку о группе по
Ой, плохо, это в MySQL. Вам придется адаптироваться
SELECT count(message_id) as unread_messages_count, body FROM messages WHERE to_user_id = 31 AND is_read = false GROUP BY to_user_id ORDER BY sent_date DESC LIMIT 1
Порядок по и группа по были отменены
ваши коды не работают, т.к. вы должны добавить body
в группу по. Я пробовал все эти способы, но эти решения не работают. Спасибо, мужик
SELECT COUNT(b.message_id) as unread_messages_count, b.to_user_id, (SELECT a.body FROM messages a WHERE a.to_user_id = b.to_user_id ORDER BY a.sent_date DESC LIMIT 1) as last_message FROM messages b WHERE b.is_read = false GROUP BY b.to_user_id
Хорошо, моя последняя попытка.
Создайте запрос, который получает и группирует количество сообщений для каждого пользователя, а затем подзапрос тела в результаты
Вы можете использовать оконные функции. Думаю, это логика:
WITH ranked_messages AS (
SELECT m.from_user_id, m.to_user_id, m.body, m.sent_date,
SUM(is_read::int) OVER (PARTITION BY from_user_id) as cnt
ROW_NUMBER() OVER (PARTITION BY m.from_user_id ORDER BY m.sent_date DESC) AS message_rank
FROM messages m
WHERE 31 IN (m.to_user_id, m.from_user_id)
)
SELECT *
FROM ranked_messages
WHERE message_rank = 1;
В этом коде есть ряд ошибок. Когда ошибки будут устранены. Для cnt значение равно нулю
Вы можете включить счетчик как вложенный запрос:
WITH ranked_messages AS (
SELECT messages.from_user_id, messages.to_user_id, messages.body, messages.sent_date,
select count(*) as unread_message_count from messages where (to_user_id = 31 or from_user_id = 31) and is_read = false,
ROW_NUMBER() OVER (PARTITION BY messages.from_user_id ORDER BY messages.sent_date DESC) AS message_rank
FROM messages
WHERE messages.to_user_id = 31 OR messages.from_user_id = 31
)
SELECT * FROM ranked_messages WHERE message_rank = 1;
(select count(*) as unread_message_count from messages where (to_user_id = 38 or from_user_id = 38) and is_seen = false)
вычисляет все unread_message_count. Я хочу unread_message_count на пользователя
Задумывались об использовании группы по user_id?