Запрос базы данных для получения последнего сообщения от каждого пользователя (например, сообщения facebook)
Мне нужно получить последнее сообщение между зарегистрированным пользователем и любым пользователем, с которым он когда-либо общался, как в facebook. это будет похоже на facebook.com/messages страница. Вот что я делал до сих пор
База Данных
Пользовательская таблица
- cwid
- имя пользователя
- фамилия и имя
- ссылка на изображение пользователя
Сообщение
- идентификатор
- cwid1 (идентификатор отправителя)
- cwid2 (идентификатор получателя)
- сообщение (содержание сообщения)
- время сообщения (метка времени)
- чтение пользователя (перечисление 'Y' 'N')`
Запрос У вошедшего в систему пользователя есть идентификатор =1
SELECT
user_table.cwid,
message.cwid1,
message.cwid2,
message.message,
message.userread,
MAX(message.messagetime),
user_table.first_name,
user_table.last_name,
user_table.user_image_link
FROM message
INNER JOIN user_table
ON message.cwid1 = user_table.cwid
WHERE (cwid2="1")
GROUP BY cwid1
ORDER BY messagetime DESC
Это возвращает последние сообщения, полученные пользователем от всех людей, которые отправили ему сообщение.Мне также нужно получить все сообщения, которые были отправлены этим пользователем аналогичным образом, это можно сделать с помощью
SELECT
user_table.cwid,
message.cwid1,
message.cwid2,
message.message,
message.userread,
MAX(message.messagetime),
user_table.first_name,
user_table.last_name,
user_table.user_image_link
FROM message
INNER JOIN user_table
ON message.cwid1 = user_table.cwid
WHERE (cwid1="1")
GROUP BY cwid2
ORDER BY messagetime DESC
Мне нужно, чтобы они были смешаны с разными пользователями и отсортированы по времени сообщения, как сообщения Facebook.Я новичок в MySQL, любая помощь будет очень признательна. Спасибо!
4 answers
Я еще немного поработал над этим и сделал этот запрос. - В нем перечислены сообщения от недавних пользователей, которые отправили мне сообщение ИЛИ получили от меня сообщение - Он также включает сообщения из группового чата, группы могут быть созданы кем угодно, и любой член группы может пригласить своих друзей - Он также включает флаг "прочитано/непрочитано" для выделения непрочитанных сообщений на интерфейсе.
Это не самый эффективный или элегантный запрос, но он работает. Любых улучшений очень много добро пожаловать
SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM (
SELECT users.id,'0' AS groupId, users.name, users.profilePic, messages.time,messages.message , 'Y' AS unread
FROM users
INNER JOIN messages ON messages.userId2 = users.id
WHERE messages.userId1 = '" . $userId . "'
UNION
SELECT users.id,'0' AS groupId, users.name,users.profilePic, messages.time, messages.message , messages.read AS unread
FROM users
INNER JOIN messages ON messages.userId1 = users.id
WHERE messages.userId2 = '" . $userId . "'
) AS allUsers
ORDER BY TIME DESC
) AS allUsersSorted
GROUP BY id
UNION
select * from(
SELECT '0' AS id, msgGroups.id AS groupId, msgGroups.name, msgGroups.image AS profilePic, IF(userGroupMsg.time IS NULL,userGroups.createdOn,userGroupMsg.time ) AS time,IF(userGroupMsg.message IS NULL,'',userGroupMsg.message ), 'Y' AS unread
FROM msgGroups
LEFT JOIN userGroups ON msgGroups.id = userGroups.groupId
LEFT JOIN userGroupMsg ON msgGroups.id = userGroupMsg.groupId
WHERE userGroups.userId = '" . $userId . "'
ORDER BY time DESC
)AS allUsersSorted
GROUP BY groupId
)AS allSorted
ORDER BY TIME DESC
Пожалуйста, улучшите, если кто-нибудь может.
Может быть, что-то вроде этого:
SELECT
DISTINCT m.id, m.cwid1, m.cwid2, m.message, m.userread, m.messagetime,
u.first_name, u.last_name, u.user_image_link
FROM
user_table u,
message m,
(
SELECT DISTINCT IF(cwid1 = "1", cwid2, cwid1) AS id
FROM message
WHERE "1" IN (cwid1, cwid2)
) partner
WHERE
u.cwid = partner.id
AND (m.messagetime, m.cwid1, m.cwid2) IN
(
SELECT MAX(messagetime), cwid1, cwid2
FROM message
WHERE (cwid1, cwid2) IN ((partner.id,"1"),("1",partner.id))
GROUP BY cwid1, cwid2
)
ORDER BY m.messagetime DESC
И, пожалуйста, обратите внимание, что все столбцы в предложении FROM, которые не отображаются в агрегатной функции, такой как MAX должны появиться в предложении GROUP BY. MySQL в этом случае не вызывает ошибки, но значения в неагрегатных столбцах являются случайными.
Я бы посмотрел, как Facebook настраивает таблицу FQL для сообщений здесь: https://developers.facebook.com/docs/reference/fql/unified_message . И основывайте архитектуру своего стола на этом.
Try this
select m.* ,u.*
from
messages m
inner join (
select max(id) as maxid
from messages
where messages.list_id = " '. $data['list_id'] .'"
group By (if(sender_id > reciever_id, sender_id, reciever_id)),
(if(sender_id > reciever_id, reciever_id, sender_id))
) t1 on m.id=t1.maxid