Поиск последнего сообщения из таблицы, сгруппированной пользователем в MySQL
У меня есть таблица личных сообщений на моем сайте, и какое-то время у меня были отдельные папки входящих и отправленных сообщений. Я хочу объединить входящие / отправленные только показ последних сообщений от определенного пользователя.
TLDR: я хочу показать последнее сообщение, сгруппированное по каждому пользователю.
Пример таблицы
| id | fromuser | fromid | touser | toid | message | timestamp |
--------------------------------------------------------------------------------
| 1 | user1 | 1 | user2 | 2 | Hello.. | 2015-01-01 00:00:00 |
| 2 | user1 | 1 | user3 | 3 | okay... | 2015-01-02 00:00:00 |
| 3 | user3 | 3 | user1 | 1 | not.... | 2015-01-03 00:00:00 |
| 4 | user2 | 2 | user3 | 3 | New.... | 2015-01-04 00:00:00 |
| 5 | user2 | 2 | user1 | 1 | With..... | 2015-01-05 00:00:00 |
--------------------------------------------------------------------------------
Результат, который я ищу, когда пользователь 1
| id | fromuser | fromid | touser | toid | message | timestamp |
--------------------------------------------------------------------------------
| 3 | user3 | 3 | user1 | 1 | not.... | 2015-01-03 00:00:00 |
| 5 | user2 | 2 | user1 | 1 | With..... | 2015-01-05 00:00:00 |
--------------------------------------------------------------------------------
Используя приведенный ниже код, я могу заставить его показать одно сообщение каждому пользователю или от него, но оно показывает самое старое сообщение, а не самое новое.
mysql_query("SELECT m1.*, users.id AS userid, users.username
FROM pm AS m1, pm AS m2, users
WHERE ((m1.fromuser='".$_SESSION['userName']."' AND users.id=m1.toid)
OR (m1.touser='".$_SESSION['userName']."' AND users.id=m1.fromid))
AND m2.id=m1.id ORDER BY timestamp DESC");
Решение
Попробуйте это:
Выбрать *
из сообщений м
где не существует (
выберите 1
из сообщений мм
где (mm.fromuser = m.fromuser или mm.fromuser = m.touser) И (mm.touser = m.touser или mm.touser = m.fromuser)
и mm.timestamp> m.timestamp
)
и m.fromuser = ‘user1’ или m.touser = ‘user1’;
Вероятно, было бы лучше, если идти вперед, помечать диалоги между двумя пользователями как отдельный разговор, тогда каждое сообщение относится к разговору, и, таким образом, становится намного проще находить разговоры, в которые вовлечен пользователь, и информацию о сообщениях, связанных с разговор. Тем не мение.
Попробуйте это вместо этого. Тьфу.
select m.*
from messages m
left join messages m2
on ((m.fromuser = m2.fromuser and m.touser = m2.touser)
or (m.fromuser = m2.touser and m.touser = m2.fromuser))
and m.timestamp < m2.timestamp
where (m.fromuser = 'user1' or m.touser = 'user1')
and m2.id is null;
Это может быть лучше, чем not exists версия, даже если мне удастся исправить это.
эта скрипка на самом деле работает
Другие решения
Других решений пока нет …