How do you select in mySQL all users with whom there is at least one message, along with the latest message for each
SELECT
a.*,
u.username ,
u.pics AS pic,
u.id
FROM message a
INNER JOIN (SELECT
fromuserId, MAX(`date`) AS Max_time,
IF(fromuserId != 14, fromuserId, foruserId) AS user_notme
FROM message
WHERE (fromuserId = 14 OR foruserId = 14) AND (fromuserId <> foruserId)
GROUP BY user_notme) b
on ( a.`date`=b.Max_time)
JOIN user u
ON (u.id = IF(a.fromuserId != 14, a.fromuserId, a.foruserId))
WHERE ( foruserId != 14)
LIMIT 0, 10
it's better to find the latest message not by time, but by id,
first, you'll avoid the time zone conversion issue and get correct message sorting,
and second, the issue of messages with identical timestamps, where your main query would duplicate the contact list
so I suggest doing it like this
SELECT
a.*,
u.username ,
u.pics AS pic,
u.id
FROM message a
INNER JOIN (SELECT
fromuserId, MAX(id) AS max_id,
IF(fromuserId != 14, fromuserId, foruserId) AS user_notme
FROM message
WHERE (fromuserId = 14 OR foruserId = 14) AND (fromuserId <> foruserId)
GROUP BY user_notme) b
ON (a.id = b.max_id)
JOIN user u
ON (u.id = IF(a.fromuserId != 14, a.fromuserId, a.foruserId))
WHERE ( foruserId != 14)
LIMIT 0, 10
yes, thanks, that query is better
Comments