Ok I know this is going to sound stupid. But I hav开发者_如何学JAVAe tried everything.
Here is my code to start of with
SELECT toD.username AS ToUser,
fromD.username AS FromUser,
rvw.* FROM usermessages AS rvw
LEFT JOIN users AS toD
ON toD.id = rvw.touserid
LEFT JOIN users AS fromD ON fromD.id = rvw.fromuserid
WHERE touserid = '" . $this->userid . "'
AND deleted = '0'
GROUP BY subkey
ORDER BY rvw.read ASC, rvw.created DESC
while this does work, what I am finding is that if there is a new message, and the read is 0 it still shows up as 1. I know this is because I am grouping the rows together.
But am not sure of any other way to do this.
It doesn't work because mysql can return any row from the group no matter how you try to order your set. To find first row in the group using some custom order you have to split it into two tasks - first finding all distinct values for the column you group by and then finding first row in the subquery for every referenced value. So your query should look like:
SELECT toD.username AS ToUser, fromD.username as FromUser, msg.* FROM
( SELECT DISTINCT touserid, subkey
FROM usermessages
WHERE touserid = 'insert_your_id_here'
AND deleted=0 ) msgg
JOIN usermessages msg
ON msg.id = ( SELECT msgu.id
FROM usermessages msgu
WHERE msgu.touserid = msgg.touserid
AND msgu.subkey = msgg.subkey
AND deleted=0
ORDER BY msgu.read ASC, msgu.created DESC
LIMIT 1 )
JOIN users fromD ON msg.fromuserid = fromD.id
JOIN users toD ON msg.touserid = toD.id
Make sure you have an index on (touserid,subkey). Depending on how big your db is you may need more.
精彩评论