Как вывести последние сообщение?

Question

[Speakermen]

Доброго времени суток. Пытаюсь получить последние сообщение из диалога с каждым юзером в итоге получаю не последнее, а первые при GROUP BY, Подскажите плиз. Думаю что нужно на основе времени выводить sender и receiver повторяются message.created_at

#CREATE DATABASE test;
USE test;

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255)
);

INSERT INTO users (username) 
	VALUES 
    ('User 1'),
    ('User 2'),
    ('User 3'),
    ('User 4');
    
CREATE TABLE message (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    sender INTEGER NOT NULL,
    receiver INTEGER NOT NULL,
    text_message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (sender)
        REFERENCES users (id)
        ON DELETE CASCADE,
    FOREIGN KEY (receiver)
        REFERENCES users (id)
        ON DELETE CASCADE
);

INSERT INTO message (sender, receiver, text_message) 
	VALUES 
    #(1, 2, 'Hi User 2 part 1');
    #(1, 3, 'Hi User 3 part 1');
    #(1, 2, 'Hi User 2 part 2');
    #(1, 3, 'Hi User 3 part 2');
    (2, 1, 'Hi User 1');
    
SELECT 
    *
FROM
    message;
    
SELECT 
    *
FROM
    message
        INNER JOIN
    users ON message.sender = users.id;
    
SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    users ON message.sender = users.id
WHERE
    message.sender = 1
        AND message.receiver = 2
        OR message.sender = 2
        AND message.receiver = 1
ORDER BY message.created_at;
 
# Не верно(
SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    users ON message.sender = users.id
WHERE
    message.sender = 1
        OR message.receiver = 1
GROUP BY message.sender, message.receiver
ORDER BY message.created_at;

Должно получится:

text_message
Hi User 2 part 2
Hi User 3 part 2
'Hi User 1

Comments

[Speakermen]

Пока так буду рад за более быстрый запрос

SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    (SELECT 
        message.sender,
            message.receiver,
            MAX(message.created_at) AS max_created_at
    FROM
        message
    WHERE
        message.sender = 1
            OR message.receiver = 1
    GROUP BY message.sender , message.receiver) AS last ON message.created_at = last.max_created_at
        AND message.sender = last.sender
        AND message.receiver = last.receiver
        INNER JOIN
    users ON message.sender = users.id;

[Akina]

Пытаюсь получить последние сообщение из диалога с каждым юзером

А почему для пары 1-2 показываете ДВЕ записи, а не одну?

[Speakermen]

Akina, это уже история переписки

SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    users ON message.sender = users.id
WHERE
    message.sender = 1
        AND message.receiver = 2
        OR message.sender = 2
        AND message.receiver = 1
ORDER BY message.created_at;

Answer 1

[Akina]

Если ориентироваться на пример, а не на описание задания, то

WITH
cte AS ( SELECT *, 
                ROW_NUMBER() OVER (PARTITION BY sender, receiver ORDER BY created_at DESC) rn
         FROM message )
SELECT u1.username sendername,
       u2.username receivername,
       cte.text_message,
       cte.created_at
FROM cte 
JOIN users u1 ON cte.sender = u1.id
JOIN users u2 ON cte.receiver = u2.id
WHERE cte.rn = 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a7a647...

Comments

[Speakermen]

Спасибо огромное буду изучать msql глубже чтобы так же писать