Форум MySQL

Здравствуйте

есть таблица сообщений message
id - номер сообщения
sender - отправитель
resiver - получатель
date - дата
text - текст

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

соответственно он может быть как получатель так и отправитель

эту задачу я уже решил на 50%
добавил поле users в котором хранится sender+resiver (сумма ИД пользователей переписки)

такой запрос
SELECT * FROM `message` WHERE sender=1 OR resiver=1 GROUP BY users
возвращает мне по одному первому сообщению из всех переписок пользователя 1

но как сделать чтобы это были последние сообщения?

похоже что решил

SELECT
id,
resiver,
sender,
MAX (date) max_date
MAX(CONCAT(`date`, `text`)) text
FROM `message`
WHERE sender=1 OR resiver=1
GROUP BY users
ORDER BY max_date DESC

.

Неправильно решили.

а почему не написали как правильно? надо попросить?
если вы знаете более разумное решение, то буду очень вам признателен если сюда его напишете

да ошибку я уже увидел - resiver и sender возвращаются не те что в последней записи
получается надо что-ли все в конкат засунуть с разделителями а потом уже разбивать на пхп строку..

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

при попытке расширить количество получателей (которые, кстати орфографически корректно пишутся как receiver, а лексически как recipient ) сообщения до нескольких, вся Ваша идея с суммами ключей рушится, как карточный домик.

к сожалению исправление орфографических ошибок не помогло решить задачу, буду благодарен за предложение по сути вопроса

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

Я Вам ошибки логики агрегатного группирующего запроса исправить предложил.
Еще раз. Нельзя в списке SELECT упоминать поля, которые не перечислены в GROUP BY и не входят при этом в агрегатные функции (такие как MAX COUNT и им подобные).
(Да, из этого правила есть неформальное исключение, но Вы сперва научитесь решать задачу честно, а потом рассчитывайте на неформальные послабления языка)

Орфография - так.. всего лишь указание, что читать противно.


>напомню что суть вопроса как сделать вышеупомянутый скл запрос
ну это Ваша суть - моя совсем другая.

>а почему не написали как правильно?

Потому что надо тратить время на составление запроса. А вы выше написали, что решение нашли и оно вас удовлетворило. Значит, вам оно не сильно и надо. Поэтому и написала одной фразой. Заинтересуетесь, напишу дальше, нет - мне свое время дорого.

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

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

Никакого дополнительного поля users, я бы не делала. В нем нет смысла.
Допустим, у нас пользователь с идентификатором 1.
Входящие сообщения, например, можно так:

SELECT mess1.id, mess1.date, mess1.text, mess1.receiver, mess1.sender FROM message mess1 WHERE mess1.receiver =1 AND mess1.date IN ( SELECT MAX( date ) FROM `message` GROUP BY sender )

Исходящие:

SELECT mess1.id, mess1.date, mess1.text, mess1.receiver, mess1.sender FROM message mess1 WHERE mess1.sender =1 AND mess1.date IN ( SELECT MAX( date ) FROM `message` GROUP BY receiver )

Можно попробовать соединить эти два запроса в один.
Явный вариант - через UNION.

спасибо за понятный человеческий ответ:)
суть я понял, тоже над этим думал, но вложенный запрос неохота делать

насчет объединения, то я раньше так и делал, но потом пришла мысль добавить поле users для оптимизации запроса, так как на мой взгляд с объединением будет работать медленнее

вот еще получилось одно интересное решение

SELECT m.* FROM message m LEFT JOIN message m2 ON (m2.users=m.users AND m2.date > m.date) WHERE (m.sender=1 OR m.resiver=1) AND m2.id IS NULL GROUP BY m.id ORDER BY m.date DESC

в нем можно даже поле users не использовать

надо будет протестировать что работает быстрее, с вложенным запросом или с left join

>спасибо за понятный человеческий ответ:)
>суть я понял, тоже над этим думал, но вложенный запрос неохота делать

если думали, почему в первом посте не написали?

>вот еще получилось одно интересное решение
>

SELECT m.* >FROM message m >LEFT JOIN message m2 ON (m2.users=m.users AND m2.date > m.date) >WHERE (m.sender=1 OR m.resiver=1) AND m2.id IS NULL >GROUP BY m.id >ORDER BY m.date DESC

>в нем можно даже поле users не использовать

Как в нем можно не использовать users, если по этому полю вы объединяете две копии одной таблицы?

>

SELECT mess1.id, mess1.date, mess1.text, mess1.receiver, mess1.sender FROM message mess1 WHERE mess1.sender =1 AND mess1.date IN ( SELECT MAX( date ) FROM `message` GROUP BY receiver )

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

>лучше бы тут не применять date в качестве ключа отбора.
>Если первичный ключ - автоинкрементный, и создает монотонную последовательность, лучше положиться на него, кмк.
>Дата... она ведь может и совпасть...

Согласна. Только через первичный ключ сложнее выборка будет...

не понял?
если я тупо заменю у Вас date на id ?

SELECT mess1.id, mess1.date, mess1.text, mess1.receiver, mess1.sender   FROM message mess1   WHERE mess1.sender =1   AND mess1.id   IN (         SELECT MAX( id )           FROM `message`           GROUP BY receiver     )

ну ведь можешь подсказать если захочешь :)

вот такой запрос получился

SELECT m.* FROM message m WHERE m.id = (SELECT MAX(id) FROM message WHERE (sender=1 OR receiver=1) AND users=m.users) ORDER BY m.date DESC

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

SELECT max(`date`) max_date, MAX(CONCAT(`date`,' ',`id`,' ',`receiver`,' ',`sender`,' ',`text`)) content FROM `message` WHERE sender=1 OR receiver=1 GROUP BY users ORDER BY max_date DESC

SELECT m.* FROM message m LEFT JOIN message m2 ON ((m2.sender=1 OR m2.receiver=1) AND m2.users=m.users AND m2.date > m.date) WHERE (m.sender=1 OR m.receiver=1) AND m2.id IS NULL ORDER BY m.date DESC

в любом случае поле users нужно

еще появилась мысль вместо дополнительного поля users добавить поле last в котором просто обозначать последнее сообщение в теме

SELECT * FROM message WHERE (sender=1 OR receiver=1) AND last=1 ORDER  BY date DESC

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

UPDATE message SET last=0 WHERE (sender=1 AND receiver=2) OR (sender=2 AND receiver=1)

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

всем спасибо за интерес к теме, думаю что вопрос полностью исчерпан

Вы явно меня с кем-то спутали.
Мой ответ был адресован Лене.

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

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

Смотрите, в аттаче - ситуация, когда дата совпала.
Получается один и тот же пользователь за одну секунду запостил два сообщения. И эти два сообщения моим запросом выведены как максимальные.
Чтобы уменьшить вероятность совпадения, можно сделать тип поля даты int, и забивать это поле временем с микросекундами - microtime(), и тогда забить два сообщения за одну микросекунду это надо будет еще постараться.

дело же не в вероятности?
если вопрос в том чтобы получить последнюю созданную запись в таблице - этому акценту автоинкрементный первичный ключ (вместо поля date) удовлетворяет целиком и полностью.
Тогда хоть усоздавайся этих сообщений - все равно совпадений не будет.

>если вопрос в том чтобы получить последнюю созданную запись в таблице

нет, здесь вопрос не в том, чтобы получить последнюю запись. В том, чтобы получить запись с максимальной датой, а значит, надо ориентироваться на дату, а не на первичный ключ.
Поэтому и придумываются всякие варианты, чтобы на поле, которое не уникально, вероятность совпадений была мала.

PS. Вообще-то здесь с самого первого поста идея как бы неправильная. Надо не по максимальной дате вытягивать, а по последнему посещению пользователя. Фиксируем последний заход пользователя на сайт и при следующем его заходе показываем ему только те сообщения других пользователей, которых он не читал. Зачем ему видеть последнее сообщение(с максимальной датой), если он его уже читал в прошлый раз.
Тогда и будет ориентация на первичный ключ.

>>если вопрос в том чтобы получить последнюю созданную запись в таблице
>
>нет, здесь вопрос не в том, чтобы получить последнюю запись. В том, чтобы получить запись с максимальной датой, а значит, надо ориентироваться на дату, а не на первичный ключ.

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



>Поэтому и придумываются всякие варианты, чтобы на поле, которое не уникально, вероятность совпадений была мала.

>PS. Вообще-то здесь с самого первого поста идея как бы неправильная.

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

Сдаюсь :)
Оставим ваш вариант за 10.12.2010 в 17:35.

лучше бы за 09.12.2010 в 17:27 :)
За него хоть не так совестно. :)