开发者

Group by subkey but if new read if not 1 show 0

开发者 https://www.devze.com 2023-03-03 08:25 出处:网络
Ok I know this is going to sound stupid. But I hav开发者_如何学JAVAe tried everything. Here is my code to start of with

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消