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

Question

[Totobi]

Здравствуйте, пробую делать простой чат.

Но не могу понять что я сделал не так. Если быть точнее то не понимаю как правильно написать запрос или возможно Я изначально неправильно спроектировал таблицу, пожалуйста посоветуйте как быть в данной ситуации.

+----+-------------+------------+--------+--------------+------------+
| id | message     | date       | status | from_user_id | to_user_id |
+----+-------------+------------+--------+--------------+------------+
|  1 | sfgsfdgsfdg | 1693310921 |      0 |           15 |         20 |
|  2 | aboba       | 1693317654 |      0 |           20 |         15 |
|  3 | 123         | 1693318102 |      0 |           19 |         15 |
|  4 | 321         | 1693318124 |      0 |           15 |         19 |
+----+-------------+------------+--------+--------------+------------+

SELECT
        messages.*,
        users.login,
        image.img
    FROM 
         messages
    LEFT JOIN
          users ON messages.to_user_id = users.id
    LEFT JOIN
           image ON messages.to_user_id = image.obj_id AND image.obj_type = 'user'
     WHERE
          messages.date = (SELECT MAX(date) FROM messages WHERE from_user_id = :id AND to_user_id = :id)

Вот запрос который пробовал выполнять, но это не то что мне надо:

скрин

Comments

[Игорь]

запрос надо сделать с order by date по убыванию и группировкой по пользователям

вообще как по мне так тяжеловат запрос, особенно вложенный селект глаз режет..так прям совсем нельзя

[Totobi]

Игорь, Я добавил поле с ид для диалога, и на счет подзапроса, а как делают в реально практике? Разбить на кучу мелких запросов и все в массив спаковать?

Answer 1

[pantsarny]

Select message from messages where from_user_id in ($user_id, $peer_id) and to_user_id in ($user_id, $peer_id) order by date desc limit 1

Comments

[Totobi]

А чем является peer_id?

[pantsarny]

Totobi, идентификатором собеседника

[Totobi]

Ваш метод рабочий, но все таки Я решил сделать для диалогов отдельное поле с уникальным ид и достаю данные подобным запросом. Можете сказать данный способ применим или он слишком нагружен или вообще не нужен?

SELECT
	messages.message
FROM
	messages
JOIN (
	SELECT
    	MAX(date) AS last_message, dialog_id
    FROM
    	messages
    WHERE
    	from_user_id = 15 OR to_user_id = 15
    GROUP BY
    	dialog_id
	) tmp ON messages.dialog_id = tmp.dialog_id AND messages.date = tmp.last_message
WHERE
    messages.from_user_id = 15 OR messages.to_user_id = 15

[pantsarny]

Totobi, я не понимаю какие данные вы хотите получить

[Totobi]

pantsarny, последнее сообщение в диалоге и выводить его в блоке списка диалогов который на скриншоте выше.

[pantsarny]

кажется вы добавили dialog_id для того чтобы идентифицировать диалог
Но в диалоге по прежнему у вас может быть только 2 собеседника
выносите пиров в отдельную таблицу - сможете делать неограниченное количество участников диалога

[pantsarny]

Totobi, я понял, вам нужен список диалогов , а именно
Имя собеседника, фото собеседника, текст последнего сообщения и дата
Сейчас подумаю

[Totobi]

pantsarny, ну если брать в учёт что чат только на 2х собеседников, то данный запрос корректен?

[Totobi]

pantsarny, он выводит то что мне надо, но Я хотел бы делать все правильно изначально, что бы потом не сталкиваться с проблемами и не переписывать код из-за одно запроса или неправильно построенной таблицы.

[pantsarny]

Totobi, с таблицей все ок, сейчас поможем с запросом

[Totobi]

pantsarny,

SELECT
    m.message,
    m.date,
    u.login AS interlocutor_name,
    i.img AS interlocutor_img
FROM messages m
JOIN (
    SELECT MAX(date) AS max_date, dialog_id
    FROM messages
    WHERE from_user_id = 15 OR to_user_id = 15
    GROUP BY dialog_id
) t ON m.dialog_id = t.dialog_id AND m.date = t.max_date
JOIN users u ON
    CASE
        WHEN m.from_user_id = 15 THEN m.to_user_id = u.id
        WHEN m.to_user_id = 15 THEN m.from_user_id = u.id
    END
LEFT JOIN image i ON u.id = i.obj_id AND i.obj_type = "user"
WHERE m.from_user_id = 15 OR m.to_user_id = 15

Я дошел до такого варианта. Он рабочий вроде, но надо больше тестов.

Answer 2

[Даниил]

Сообщения:

+----+---------+------------+--------+--------------+------------+
| id | message | date       | status | from_user_id | to_user_id |
+----+---------+------------+--------+--------------+------------+
| 1  | ...     | 1693310921 | 0      | 15           | 20         |
| 2  | ...     | 1693317654 | 0      | 20           | 15         |
| 3  | ...     | 1693318102 | 0      | 19           | 15         |
| 4  | ...     | 1693318124 | 0      | 15           | 19         |
+----+---------+------------+--------+--------------+------------+

Юзеры:

+----+---------+
| id | login   |
+----+---------+
| 15 | user1   |
| 19 | user2   |
| 20 | user3   |
+----+---------+

Тогда запрос будет примерно такой:

SELECT
    m1.*,
    u1.login AS from_user_login,
    u2.login AS to_user_login
FROM
    messages m1
JOIN
    (
        SELECT
            LEAST(from_user_id, to_user_id) AS user1_id,
            GREATEST(from_user_id, to_user_id) AS user2_id,
            MAX(date) AS max_date
        FROM
            messages
        GROUP BY
            LEAST(from_user_id, to_user_id),
            GREATEST(from_user_id, to_user_id)
    ) m2 ON LEAST(m1.from_user_id, m1.to_user_id) = m2.user1_id
        AND GREATEST(m1.from_user_id, m1.to_user_id) = m2.user2_id
        AND m1.date = m2.max_date
JOIN
    users u1 ON m1.from_user_id = u1.id
JOIN
    users u2 ON m1.to_user_id = u2.id;

Могу ошибаться,редко составляю такие громоздкие запросы.
Но суть вроде передал верно.

P.S я бы может более костыльно сделал но:
Сделать таблицу last_msg
и писать в нее id(unic auto),to_user,from_user,message
И просто при отправке сообщения опред юзеру обновлять данную таблицу и хранить в ней последнее сообщение всегда.А выводить по to_user где to_user = id просматривающего.

Answer 3

[ThunderCat]

SELECT m.*, u.login, i.img
    FROM messages m
    LEFT JOIN users u
    ON m.to_user_id = u.id
    LEFT JOIN image i 
    ON m.to_user_id = i.obj_id 
    WHERE m.date > :lastdate # надо выбирать все что позже уже полученных сообщений
    AND image.obj_type = 'user' 
    AND m.from_user_id = :fid  # айди "от юзера"
    AND m.to_user_id = :tid #айди "к юзеру"
    ORDER BY m.date  # по возрастанию все старше последнего полученного

Answer 4

[Totobi]

ThunderCat , Даниил, pantsarny
вот запрос к которому Я пришел и он достает то что мне надо. Но Я столкнулся с проблемой, в PDO нельзя везде установить одно название для бинда значения и Я сделал вот так (код после текста), но мне кажется так неправильно делать. Можете что-то посоветовать?

public function dialogueList($id)
    {
        try
        {
           
            $query = "
            SELECT
                m.message,
                m.date,
                u.login AS interlocutor_name,
                i.img AS interlocutor_img
            FROM messages m
            JOIN (
                SELECT MAX(date) AS max_date, dialog_id
                FROM messages
                WHERE from_user_id = :id1 OR to_user_id = :id2
                GROUP BY dialog_id
            ) t ON m.dialog_id = t.dialog_id AND m.date = t.max_date
            JOIN users u ON
                (CASE
                    WHEN m.from_user_id = :id3 THEN m.to_user_id = u.id
                    WHEN m.to_user_id = :id4 THEN m.from_user_id = u.id
                END)
            LEFT JOIN 
                image i ON u.id = i.obj_id AND i.obj_type = 'user' 
            WHERE m.from_user_id = :id5 OR m.to_user_id = :id6
            ";
            $stmt = $this->db->prepare($query);
            $stmt->bindValue(":id1", $id, PDO::PARAM_INT);
            $stmt->bindValue(":id2", $id, PDO::PARAM_INT);
            $stmt->bindValue(":id3", $id, PDO::PARAM_INT);
            $stmt->bindValue(":id4", $id, PDO::PARAM_INT);
            $stmt->bindValue(":id5", $id, PDO::PARAM_INT);
            $stmt->bindValue(":id6", $id, PDO::PARAM_INT);
            $stmt->execute();
            $data = $stmt->fetchAll();

            return $data;
        }
        catch(PDOException $e)
        {
            echo $e;
        }
    }

Comments

[ThunderCat]

не очень понимаю, зачем такая сложность? Нужно получить последний месаж от или к пользователю?

SELECT m.*
    FROM messages m
    WHERE m.from_user_id = ? 
    OR m.to_user_id = ?
    ORDER BY m.date DESC
    LIMIT 1

Если к готовому датасету нужны джоины типа картинок и имен - просто добавляем джоины. Зачем и откуда появился dialog_id? Если диалог это именно 2 пользователя, то зачем он? Если это чат на много пользователей, то и структура должна быть другая.

Но Я столкнулся с проблемой, в PDO нельзя везде установить одно название для бинда значения

Есть вариант неименованных плейсхолдеров, как я выше написал, тогда в $stmt->execute() просто передается массив, из которого по порядку берутся значения и ставятся вместо "?", типа
$stmt->execute([$id, $id]);

[Totobi]

ThunderCat, нужно получить любое последнее сообщение из диалога, ваше/собеседника и дату сообщения. Но имя и аватар в списке диалогов должны быть собеседника.

[Totobi]

ThunderCat,
вот такой список мне нужен, на скрине выведены уже реальные данные. А на счет сложности запроса, Я собирал его по кусочкам из того что читал в интернете, мне действительно не хватает опыта, если вам не трудно не могли бы Вы написать как правильно достать эти данные или указать на ошибки запроса?

[ThunderCat]

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

Так?

[Totobi]

ThunderCat, да, извините за неясность вопроса.

[Totobi]

ThunderCat, если нужны еще какие-то данные с БД или какие-то данные которые более прояснят для вас ситуацию Я могу предоставить без проблем.

[ThunderCat]

Во первых, таким образом вы получите не все диалоги, например не будет диалогов где текущий юзер что-то написал, но ответа не получил. Во вторых - прям по описанию строите запрос примерно так:

SELECT 
         m.message, 
         max(m.date) message_date,  #последняя дата из выборки
         u.login, 
         i.img
    FROM messages m
    LEFT JOIN users u
    ON m.from_user_id = u.id #выбираем имя писавшего
    LEFT JOIN image i 
    ON m.from_user_id = i.obj_id #берем картинку писавшего
    AND i.obj_type = 'user' 
    AND m.to_user_id = :id #выбираем только наш айди "к юзеру" 
    GROUP BY m.from_user_id # группируем по "написавшим" юзерам, получая уникальные строки
    ORDER BY m.date DESC #сортируем по дате, получая самые последние сообщения

Дальше надо смотреть по структуре, и возможно что-то подправить

[Totobi]

ThunderCat, DOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'all_mods.m.message' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in C:\OpenServer\domains\all-mods\assets\php\classes\Read.php:734 Stack trace: #0 C:\OpenServer\domains\all-mods\assets\php\classes\Read.php(734): PDO->prepare() #1 C:\OpenServer\domains\all-mods\view\messages.php(29): Read->dialogueList() #2 {main}

[Totobi]

ThunderCat, Я пофиксил ошибку, там была проблема с sql_mode.