У нас есть несколько диалогов с разными людьми - нужно вывести последнее сообщение каждого диалога. (как в ВК)

У меня есть 1 таблица с диалогами (в запросе не участвует):

`user_dialogue`
id | name
1 | Первый диалог
2 | Второй диалог

У нас есть таблица с участниками диалога (предположим, мы id_user = 1, т.е. состоим в 2 диалогах):

`user_dialogue_members`
id | id_user | id_dialogue
1 | 1 | 1
2 | 5 | 1
3 | 1 | 2
4 | 15 | 2
...

У нас есть таблица непосредственно с сообщениями:

`user_dialogue_messages`
id | id_user | message | id_dialogue 
1 | 1 | Привет, как дела ? | 1
2 | 5 | Нормально | 1
3 | 1 | Привет, привет | 2
...

В результате мы должны получить вывод диалогов (поскольку мы id_user = 1):
- Нормально (от 5 участника)
- Привет, привет (наше сообщение)

В общем последнее сообщение каждого диалога, не важно, написали его нам или мы.

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

SELECT *
FROM `user_dialogue_members` AS `members`
LEFT JOIN `user_dialogue_messages` AS `messages`
ON `members`.`id_dialogue` = `messages`.`id_dialogue`
WHERE `members`.`id_user` = :id_user
GROUP BY `messages`.`id_dialogue`
ORDER BY `messages`.`id` DESC

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

_____________
если помог, не скупись на карму =)

В принципе тут можно сделать вложенный запрос с MAX(`messages`.`id`) as 'id'

Мне вот только интересно как создаются диалоги, если в нем есть пользователи 1,2,3
а потом пользователь 1 вышел из диалога и захотел создать отдельный диалог с пользователем 2 или даже не выходил.
И как отслеживать что сообщение прочитано, опять отдельной таблицей?
И что если 1 пользователь удалил у себя сообщение из диалога, у других то оно должно остаться.

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2

Slays, вам нужно делать примерно также как в этих сообщениях
http://phpforum.su/index.php?act=Print&cli...imin=15&limit=1
http://phpforum.su/index.php?act=Print&cli...imin=30&limit=1


_____________
Люди, имеющие низкий уровень квалификации, делают ошибочные выводы, принимают неудачные решения и при этом неспособны осознавать свои ошибки в силу низкого уровня своей квалификации

Почти получилось, запрос

SELECT
 (SELECT `message` FROM `user_dialogue_messages` WHERE `id_dialogue` = `members`.`id_dialogue` ORDER BY `id` DESC LIMIT 1 ) AS `message`
FROM `user_dialogue_members` AS `members`                        
WHERE `members`.`id_user` = :id_user

выводит правильные сообщения, но как мне вывести из таблицы подзапроса `user_dialogue_messages` другие столбцы, помимо `message` ?

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

_____________
если помог, не скупись на карму =)

я бы скорее всего делал одним из следущих вариантов:

SELECT * FROM (
	SELECT messages.*
	FROM `user_dialogue_members` as `members` INNER JOIN `user_dialogue_messages` as `messages` USING(`id_dialogue`)
	WHERE members.id_user = :id_user
	ORDER BY messages.id DESC
) as `tmp`
GROUP BY `id_dialogue`
ORDER BY `id` DESC

SELECT messages.*
FROM (
	SELECT MAX(user_dialogue_messages.id) as `mid`
	FROM `user_dialogue_members` as `members` INNER JOIN `user_dialogue_messages` USING(`id_dialogue`)
	WHERE members.id_user = :id_user
	GROUP BY members.id_dialogue
	ORDER BY NULL
) as `tmp`
	INNER JOIN `user_dialogue_messages` as `messages` ON (tmp.mid = messages.id)
ORDER BY tmp.mid DESC

скорость работы будет сильно зависеть от кол-ва диалогов и сообщений в них, так что погоняй тесты, какой из вариантов лучше для конеретно твоей ситуации...

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

SELECT `messages`.*
FROM `user_dialogue_messages` AS `messages`
LEFT JOIN `user_dialogue_members` AS `members`  
ON `messages`.`id_dialogue` = `members`.`id_dialogue`                       
WHERE `messages`.`id` IN (SELECT MAX(`id`) FROM `user_dialogue_messages` GROUP BY `id_dialogue`)
     && `members`.`id_user` = :id_user
ORDER BY `messages`.`id` DESC

Alchemist, спасибо, сейчас посмотрю твои вариант

_____________
если помог, не скупись на карму =)

Alchemist как тебе мой вариант, если оценивать на глаз ) ?


_____________
если помог, не скупись на карму =)

На глаз - не слишком хорошо. Тебе стоит почитать о том как работают SQL запросы и MySQL оптимизатор.

1) "неоптимальный" JOIN.

LEFT JOIN `user_dialogue_members` AS `members`
и
WHERE ... && `members`.`id_user` = :id_user

LEFT JOIN говорит "возьми все строки из таблицы слева и подбери им все подходящие строки из таблицы справа, а если таких нет - подставь пустую строку", но при этом в WHERE ты задаешь условие для полей правой таблицы, которое сразу же "убивает" все пустые строки.

Т.е. ты фактически эмулируешь INNER JOIN, но не даешь MySQL выполнить ни одной "inner" оптимизации.


2) "неоптимальный" подзапрос. `user_dialogue_messages` - это самая большая таблица из тех что у тебя имеются. Представь себе, что у тебя:

`user_dialogue_members` - 100 rows
`user_dialogue` - 500 rows (AVG ~ 10 dialogs/man (2 men/dialog))
`user_dialogue_messages` - 10 000 rows (AVG ~ 20 messages/dialog)

В этом случае подзапрос пройдет все 10к строк в таблице `messages` и вернет тебе 500 строк, из которых в итоге тебе пригодятся только 10 !
Очень неэффективно.


3) WHERE `messages`.`id` IN (:subquery:)
есть известный баг/ограничение на использование подзапросов в IN() конструкции внутри WHERE: "...The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery."

В переводе: "[В некоторых случаях] при использовании [независимых] подзапросов в IN конструкции оптимизатор переписывает его как зависимый подзапрос."
Другими словами подзапрос будет выполняться не один раз для всего внешнего запроса, а по отдельности для каждой строки из внешнего запроса.


Вобщем как-то так.

Но главная прелесть (или ужас) MySQL заключается в том, что все эти "претензии" являются теоретическими, и на самом деле в твоей рабочей БД и твоих рабочих условиях этот запрос может бежать быстрее любого представленного наверху. А может наоборот, любой из предыдущих будет быстрее твоего...

Поэтому все подобные запросы надо тестировать в "условиях приближенных к реальным"

Alchemist, Очень круто объяснил, спасибо, буду разбираться!

_____________
если помог, не скупись на карму =)