Форум MySQL

Добрый вечер.

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

К примеру есть таблица:

id, user_id, user_to, messages, date (DATETIME)

Во время общении у посетителей накапливаются сообщения.
На странице "Сообщения" хочу отображать список последних сообщений со своими переписчиками.

К примеру переписка
С Васей у меня 15 сообщений
С Катей 28
С Настей 13

На странице Сообщения нужно вывести:

Вася: (последнее сообщение которое написал он или я)
Катя: (последнее сообщение которое написала она или я)
Настя: (последнее сообщение которое написала она или я)

Сортировка идет по date.

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

p.s. Принцип работы как VK (мои сообщения) как мне показалось удобно реализовано.
p.s.s ошибся с разделом форума.

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

Спасибо, думал об этом надеялся на более простой выход

Получается по Вашему варианту на сколько я понимаю создаем таблицу:

id, user_id, message

при переписке с Васей когда он мне отправил сообщение, в таблицу записыФвается его последнее сообщение и мое мной полученное. Записываем/Обновляем две записи

1 | Вася    | Последнее сообщение которое мне отправил Вася 2 | Я       | Последнее сообщение от Васи

Если я отправил Васе сообщение то:

1 | Вася    | Последнее сообщение от меня 2 | Я       | Последнее сообщение отправленное Васе

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

1 | Вася    | Последнее сообщение от меня или Васи 2 | Я       | Полученное сообщение от Кати 3 | Катя    | Последнее отправленное сообщение

Я верно Вас понимаю?

ну почти, только сообщение от Я не должно переходить к Васе
у вас должен быть некий идентификатор самого "разговора" естественно уникальный, по которому можно отличить переписку с Васей от переписки с Катей
в итоге в таблице должны быть поля
id_per - идентификатор переписки
id_user - идентификатор пользователя
mess - само сообщение

сейчас думаю над тем как лучше организовать инициацию переписки с новым пользователем
и получение id_per
идея уйти от классической модели from -> to что при необходимости может позволить участвовать в переписке неограниченное количество человек
кстати при такой организации возможна группировка по id_per с выборкой max() по времени добавления, сейчас еще не уверен в работоспособности и оптимальности такого запроса, но возможно таблица-буфер не понадобится, там уже надо экспериментальным путем определять

видимо да, придется так и сделать
При общении с пользователем самому диалогу задаем уникальный идентификатор
id | unique | user_id | msg

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

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

Вот думаю какой же уникальный идентификатор может быть...
Набор букв/цифр? или все таки продумать логику? к примеру
Диалог кого то первый - уникальный = 1
Диалог кого то второй - уникальный = 2
Диалог кого то третий - уникальный = 3
.....
Диалог кого то N - уникальный = N+1

В базу записываем:
ID | unique | message
1 | 1 | text
2 | 2 | text
......
x | N+1 | text



Так же при записи в таблицу последних сообщений придется все время проверять есть ли этот идентификатор или нет...

>>запись одна, выборка происходит по unique независимо от того кому эту выборку отдавать
хоть два собеседника хоть стопицот

Да спасибо так будет лучше

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

Так же при записи в таблицу последних сообщений придется все время проверять есть ли этот идентификатор или нет...
это ненужно, смотрите допустим вы вывели на странице 20 переписок последние сообщения оформлены в виде ссылок и у каждой ссылки свой идентификатор
для ответа, вы щелкаете по ссылке и вам этот идентификатор доступен на все 100%
только нужно сделать защиту от "левых" идентификаторов, ну это само собой разумеется и от логики переписки не зависит

ну вот немного набросал алгоритм
создаем три таблицы
init_post - таблица инициации переписки
p_id - идентификатор переписки (авто инкремент)
u_id - идентификатор пользователя инициатора переписки
оставим возможность добавления полей для расширения функционала

ac_post - таблица доступа к определенной переписке
p_id - идентификатор переписки
u_id - идентификатор пользователя имеющего доступ к переписке
в этой таблице вешаем первичный ключ на оба поля
ну и оставим возможность добавления полей для расширения функционала

post таблица сообщений
id - уникальный ключ (авто инкремент)
p_id - идентификатор переписки
u_id - идентификатор пользователя написавшего сообщение
mess - текст сообщения

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

рассмотрим процесс неявной инициации переписки
имеем свой идентификатор и идентификатор того пользователя кому хотим написать сообщение
проверяем есть ли в таблице ac_post эти идентификаторы и имеют ли они общий идентификатор переписки (это скорее всего придется проверять каждый раз, чтобы отсеять попытки несанкционированного доступа к переписке)
если идентификатор переписки есть то "красота" смело инсертим в таблицу post нашу писульку
если идентификатора нет, то запускаем механизм инициализации
тут все просто делаем инсерт в таблицу init_post и получаем идентификатор переписки с пылу с жару его пхаем в таблицу ac_post аж 2 раза, со своим идентификатором пользователя и с идентификатором того кому пишем
дальше уже на мой взгляд все предельно понятно :)

небольшое лирическое отступление по поводу расширения функционала:
например делаем явную инициализацию и открываем доступ постить всем кому не лень
(таблицу ac_post вообще не трогаем и она нас не трогает)
обзываем нашу переписку словом "стена" по аналогии с соцсетями и наслаждаемся процессом
только там стена одна, а у нас их может быть столько на сколько хватит "жирности" сервера
в итоге мы круче чем "в контакте" и "одноклассники" вместе взятые

вот запрос выборки последних сообщений при такой организации логики

$query="SELECT post.mess m, users.login l, post.mess_date d             FROM post             JOIN (SELECT max(mess_date) mess_date                  FROM post v                 JOIN (SELECT p_id FROM ac_post WHERE u_id={$_SESSION[user_id]}) s ON v.p_id=s.p_id                 GROUP BY v.p_id) p ON p.mess_date=post.mess_date             JOIN users ON post.u_id=users.user_id             ORDER BY post.mess_date ";

Valick, большое спасибо, очень интересно и познавательно но слишком сложно для решения моей задачки. Хотя может зря так считаю.
Ваш вариант алгоритма очень хорош, но я пошел немного другим путем, что Вы скажите на это вариант?:

Создаем таблицу messages_list:
id,
msg_unique,
user_id,
user_to,
messages,
date,
reply (default = 0)

Создаем функции (примеры):

<?php /* Проверяем есть ли переписка между пользователями */ function check_unique($user_ID, $user_to) {     $res = "SELECT msg_unique FROM messages_list WHERE ((user_id = '$user_ID' AND user_to = '$user_to') OR (user_id = '$user_to' AND user_to = '$user_ID')) ORDER BY date DESC";     if(count($res) == 0) {         $unique = max_unique(); // чуть ниже     } else {         foreach ($res as $row) {             $unique = $row->msg_unique;         }     }     return $unique; } /* Получаем максимальный unique */ function max_unique() {     $query_unique = "SELECT msg_unique FROM messages_list ORDER BY msg_unique DESC LIMIT 1";     if (empty($query_unique)) {         $unique = "1";     } else {         foreach ($query_unique as $query_unique_row) {             $unique = $query_unique_row->msg_unique + 1;         }     }     return $unique; } /* ID последнего сообщения при записи */ function last_reply_msg($msg_unique='') {     $msg_unique = intval($msg_unique);     $res = "SELECT * FROM messages_list WHERE msg_unique = '$msg_unique' ORDER BY date DESC LIMIT 1";     if(count($res) == 0) {         return false;     } else {         foreach ($res as $row) {             return $row->id;         }     }      } ?>

Допустим в таблице уже есть запись:

id | msg_unique | user_id | user_to | messages | date | reply 1  | 1          | 1(катя) | 2(вася) | text     | .... | 0

Когда Вася пишет Маше сообщение мы проверяем был ли у них диалог ( check_unique() ), если да то присваиваем его номер, если нет то присваиваем новый максимальный ( max_unique() )
Так же перед постом нового сообщения у предыдущего то есть пока что последнего мы ставим статус в столбец reply 1, что означает на него есть ответ.

Таблица принимает следующий вид:

id  | msg_unique | user_id | user_to | messages | date | reply 1   | 1          | 1(катя) | 2(вася) | text     | .... | 1 2   | 1          | 2(вася) | 1(катя) | text     | .... | 0

Теперь как же вывести лист последних сообщений:

<?php $res = "SELECT * FROM messages_list WHERE (user_id = '$user_ID' OR user_to = '$user_ID') AND reply = '0' GROUP BY `msg_unique` ORDER BY `date` DESC"); ?>

так мы получили последнее сообщение на которое нет ответа.

сам уже запутался, но вроде все работает.

ничего хорошего не могу сказать про ваш код
например
"SELECT msg_unique FROM messages_list ORDER BY msg_unique DESC LIMIT 1";
это верёвочная лестница над пропастью
не забывайте в реальных приложениях доступ к бд одновременно имеет большое количество пользователей, и логика изначально должна это подразумевать

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

Спасибо! Вы правы, верно пока пользователь будет писать сообщение, в новом диалоге между другими пользователями может оказаться тот же unique ))

Буду разбирать Ваш алгоритм.

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

буду очень благодарен zetrider[@]bk.ru

мне проще сюда прикрепить :)
там по ходу разберетесь какие таблицы создать

Да конечно! Спасибо, так будет проще понять

по поводу удаления сообщений (из другой вашей темы)
если это реализовывать при моем варианте, то нужно удалить строку из таблицы ac_post где есть сопоставление идентификатора пользователя идентификатору разговора (естественно запомнив этот идентификатор разговора) и проверить встречается ли этот идентификатор в таблице еще раз после удаления т.е имеет ли кто нибудь еще разрешение на просмотр этой переписки
и тут уже по обстоятельствам и желанию
можно удалить все сообщения из таблицы post которые имеют только что удаленный идентификатор
ну или перенести в архив это все, в общем на что хватит фантазии
___
это как раз наглядный пример организации таблиц в БД
вы поставили задачу, а у меня уже готовое решение :)

сделал dump так проще таблицы сделать

кстати эта логика больше похожа на чат, чем на обычные личные сообщения
но это смотря как оформить, если проводить инициацию разговора каждый раз - то это личка, если только для начала разговора с новым пользователем, то что-то типа лички/мгновенных сообщений, если позволить присоединяться к разговору, то это чат
___
добавил еще столбец to_id для идентификатора пользователя
но это уже больше для чата нужно, для отправки приватных сообщений

Сообщения имеют дату, а клиент имеет временную метку, GMT как раз для этого.

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

А чего объяснять, разве клиент при запросе просмотра сообщений не может отправить свою метку временную?

и как она поможет при выборе?
таблица есть, покажите запрос если знаете

Молча - запрашивать сообощения больше или равные врменной GMT-метки клиента.

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

[поправлено модератором]

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

[поправлено модератором]

таблица в первом посте, задание там же

[поправлено модератором]

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

Запросом ориентируясь по дате я смогу выбрать только сообщение которое отправил я или отправили мне
Я не смогу выбрать одно из сообщений которое было адресовано мне или я адресовал пользователю.
То есть: выбрать ID которые есть в столбце usrt_ID (отправитель) и/или user_TO(получатель) отсортировать по дате и вывести ОДНО сообщение которое отвечает условию. Учитывая что переписка может быть с разными пользователями и вывести нужно 15 блоков с последним сообщением.

К примеру лист переписки между посетителями выбираю так:

SELECT * FROM `messages`     WHERE (user_id = '$user_id' AND user_to = '$user_to')     OR (user_id = '$user_to' AND user_to = '$user_id')     ORDER BY date DESC LIMIT $show_msg