Как сделать вывод последнего сообщения между двумя юзерами?

Question

[cannabioid1337]

У меня есть чат, в котором сообщение - это три значения: id, from_id (отправитель), to_id (получатель), msg (сообщение).
Оформляю интерфейс, и мне необходимо создать список диалогов (с кем общался юзер за все время, и кто ему отправлял сообщения).
Я придумал такой запрос:
select * from `messages` where to_id = ? order by id desc. ( ? = айдишник юзера, который получит свои диалоги)
Он неплох, но имеется нюанс, не будут выводиться диалоги, где юзер написал первым, ведь мы получаем только те сообщения, где написали НАМ.

А если использовать этот вариант:
select * from `messages` where to_id = ? or from id = ? order by id desc. ( ? = айдишник юзера, который получит свои диалоги)
Мы получим дупликаты. Как поступить?

Answer 1

[ThunderCat]

сообщение - это три значения: id, from_id (отправитель), to_id (получатель), msg (сообщение).

Точно три??? А то я плохо считаю на пальцах... А еще неплохо было бы дату сообщения как то хранить, и собсно по ней сортировать...

Мы получим дупликаты. Как поступить?

Дубликаты чего?

Как поступить?

Зависит от того что вам нужно, в приведенных запросах вы получаете всю выборку, так как лимит на количество записей у вас не обозначен. Последний запрос с ограничением в одну запись я думаю подойдет, но я бы все же рекомендовал добавить дату (чисто по логике- хотелось бы знать кто и что когда кому отправлял) и уже по дате делал ордер.

Answer 2

[ComodoHacker]

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

Answer 3

[alexalexes]

Каждый уважающий себя разработчик должен пройти путь создания своего велосипеда - мессенджера.
От простой почтовой модели данных, где в записи сообщений есть отправитель и получатель, до модели данных, содержащий беседки (они же чаты, они же диалоги - называйте как хотите).
Но вопрос не в этом. Давайте попробуем что-то сделать с существующей моделью.
Время сообщения у нас нет - не проблема. Время летит вперед, и id увеличиваются в ту же сторону - по возрастанию. Значит id достаточно, чтобы различать, какое сообщение пришло раньше, а какое позже.
Отсутствует сущность беседки - тоже не проблема. Будем считать совокупность сообщений, где отправитель, в которых может быть как сам автор, так и собеседник, или получатель также может быть как сам автор, так и собеседник одной беседкой. Такую совокупность сообщений можно выделить условием: (m2.to_id = m.to_id and m2.from_id = m.from_id or m2.to_id = m.from_id and m2.from_id = m.to_id) - что и будет выделять сущность беседки в упрощенном виде. Для выделения всех сообщений, где участвует пользователь from_id будет выражение: :from_id in (m.from_id, m.to_id). Осталось сообразить, как составить подзапрос с count, чтобы выделить самые поздние сообщения.
Где-то такой запрос получится:

select * from messages m
 where  :from_id in (m.from_id, m.to_id)
    and (select count(*)
              from messages m2
             where  (m2.to_id = m.to_id and m2.from_id = m.from_id
                   or   m2.to_id = m.from_id and m2.from_id = m.to_id) 
               and m2.id > m.id
) < 1
order by id desc

Если понадобится выделить сообщения одной беседки, то скорее всего будет такой запрос:

select * from messages m
 where (m.to_id = :to_id and m.from_id = :from_id
 or        m.to_id = :from_id and m.from_id = :to_id)
order by id asc

Answer 4

[Сергей]

Я бы сделал по другому - более гибкий вариант.

таблица 1 (таблица сообщений): id, id_perep, id_login, data, msg
id_login - id_login оправителя
id_perep - id из таблицы 3
data - дата отправки сообщения
msg - текст сообщения

таблица 2: (таблица доступа к переписке): id_login, id_perep, data_sort, count_new_msg
id_login - id login того, кому разрешен доступ к переписке
id_perep - id переписки из таблицы 3, к которой разрешен доступ
count_new_msg - количество непрочитанных сообщений для id_login

таблица 3: (таблица переписок): id, count_msg

После отправки сообщения необходимо обновить поле "data_sort" на текущую дату (по нему будет сортировка для вывода), а также надо обновить (прибавить +1) сообщений всем тем, у кого есть доступ к переписке (в таблице 2)
Запрос, если необходимо вывести все переписки для user 1:

SELECT * FROM `table2` WHERE `id_login` = 'user 1' ORDER BY `data_sort` DESC;

Запрос, если необходимо вывести сообщения к определенной переписке:

SELECT * FROM `table` WHERE `id_perep` = '$id_perep' ORDER BY `id` DESC; // $id_perep - id переписки