计算来自发件人的未读消息 [英] Count unread messages from a sender

问题描述

我正在使用 mysql 和 php 构建一个消息传递系统.我已经到了要选择用户收到的消息并计算从不同用户接收到同一用户的未读消息的地步.我已经在下面说明了

I am building a messaging system using mysql and php. I have gotten to a point where I want to select messages received by a user and also count the unread messages received from different users to the same user. I have illustrated it below

table----users
perID | name |
 001  | mum  |
 002  | tok  |
 003  | sat  |

table----messages
msgID |senderID | msgBody |      msgTime       | deleted_by_sender |
 200  | 002     | Hello   | 2014-07-13 19:14:22|  no               |
 201  | 002     | Mate    | 2014-07-13 19:14:29|  no               |
 202  | 003     | hi mum  | 2014-07-13 19:19:12|  no               |
 203  | 003     | How     | 2014-07-13 19:19:52|  no               |

来自父表users

   table----recipients
 recID |msgID |recipientID | msgStatus| deleted_by_recipient|       
 310   | 200  |    001     |  unread  |        no           |
 311   | 201  |    001     |  unread  |        no           |
 312   | 202  |    001     |  read    |        no           |
 313   | 203  |    001     |  read    |        no           |

recipientID 引用父表 users

我想

1. Get only the current message received by the recipient with recipientID=001
    if it is not deleted by the recipient.

2. count the number of unread messages received from the individual users.

类似下面的内容

senderID | msgID | unread |
   002   |  201  |  2     |
   003   |  203  |  0     |

我下面的查询按预期工作，但它隐藏了最后一行，因为它在 msgStatus 列中没有未读值，但我希望即使 msgStatus 没有值也能返回所有行.它也应该在一个优化的查询中.

My query below works as expected but, it hides the last row because it has no unread value in the msgStatus column, but i wish that all rows would be returned even if the msgStatus has no value. It should also be in one optimized query.

SELECT *,count(msgStatus) As unread  
FROM (
        SELECT
                m.senderID,
                m.msgTime,
                u.perID,
                r.recipientID,
                r.msgID,
                r.msgStatus,
                r.deleted_by_recipient
        FROM 
                messages m
        INNER JOIN 
                users u
            ON 
                m.senderID=u.perID
        INNER JOIN
                recipients r
            ON
                r.msgID=m.msgID

        ORDER BY msgTime DESC
    )h 
WHERE 
    recipientID=12 and 
    deleted_by_recipient ='no' and 
    msgStatus='unread'
GROUP BY perID

感谢您的帮助.

推荐答案

您可以使用条件聚合做您想做的事.这个想法是将条件从 where 子句移动到 select 子句:

You can do what you want with conditional aggregation. The idea is to move the conditions from the where clause to the select clause:

select senderid,
       max(case when r.deleted_by_recipient = 'no' and r.recipientID = '001' then m.msgID end
          ) as CurrentMsg,
       sum(r.msgStatus = 'unread') as unread
from messages m left outer join
     recipients r
     on  m.msgID = r.msgID
group by senderid;

我不是 100% 确定这实现了您的逻辑:

I'm not 100% sure that this implements your logic:

1. 这假设最新的消息是具有最大 MsgID 的消息.基于另一个字段是可能的，最容易使用 substring_index()/group_concat() 技巧来完成.
2. 这是计算所有未读邮件，无论收件人是谁.再一次，这可以通过更改 sum() 中的逻辑轻松解决.
3. 您的示例数据没有重复(具有多个收件人的相同 MsgId).如果可能，您可能需要更改计数逻辑.再说一遍，这并不难，只是不清楚是否需要额外的工作.

1. This assumes that the most recent message is the one with the largest MsgID. Basing it on another field is possible, most easily done with a substring_index()/group_concat() trick.
2. This is counting all unread messages regardless of the recipient. Once again, this is easily fixed by changing the logic inside the sum().
3. Your sample data doesn't have duplicates (the same MsgId with multiple recipients). If this is possible, you may need to change the logic for the counting. Once again, this is not difficult, it is just unclear whether the additional work is necessary.

这篇关于计算来自发件人的未读消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！