CODEWITHSUNDEEP
mysqlsql
George
George

Reputation: 1114

Count unread messages from a sender

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               |

the senderID references from the parent table 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           |

the recipientID references the parent table users

I want to 

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.

something like below

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

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

Thanks for helping.

Upvotes: 0

Views: 1385

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

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

  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.

Upvotes: 3

Related Questions