Как получить количество непрочитанных сообщений и последнее сообщение для каждого пользователя в SQL-запросе?

У меня есть такая таблица сообщений:

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

Это должно быть рассчитано для каждого пользователя, который отправляет сообщения пользователю, и наоборот.

Как добиться такого результата?

Задумывались об использовании группы по user_id?

user3647971 26.10.2018 01:43

Этот код не работает и выдает ошибку о группе по

hadi 26.10.2018 02:13

Ой, плохо, это в MySQL. Вам придется адаптироваться

user3647971 26.10.2018 02:20
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 Порядок по и группа по были отменены
user3647971 26.10.2018 02:24

ваши коды не работают, т.к. вы должны добавить body в группу по. Я пробовал все эти способы, но эти решения не работают. Спасибо, мужик

hadi 26.10.2018 02:30
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 Хорошо, моя последняя попытка.
user3647971 26.10.2018 02:48

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

user3647971 26.10.2018 02:49
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
7
189
2

Ответы 2

Вы можете использовать оконные функции. Думаю, это логика:

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 значение равно нулю

hadi 26.10.2018 01:58

Вы можете включить счетчик как вложенный запрос:

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 на пользователя
hadi 26.10.2018 02:07

Другие вопросы по теме