CODEWITHSUNDEEP
mysqljoin
Muthu
Muthu

Reputation: 1550

MySQL query for counting users unread message

I am sending the notification message to multiple devices. User can login from multiple devices. I have separate table which maintains the device ids of each user. When I send the notification, I will put an entry on the message_data with userid, message etc., I will put an entry on the message_status table for each user device.

So that I can maintain each device notification sent status and error. 

message_data
id          INT     
userid      VARCHAR
message     VARCHAR
sent_time   DATETIME
language    TINYINT

message_status
id              INT
data_id         INT
device_id       VARCHAR
device_type     TINYINT
sent_time       DATETIME
status          TINYINT
error_code      TINYINT (If there is any error, we will have the error code here)

I want to get the unread notification message count for each user. status field 0 represents , the message for this device is not yet read by user. If it is 1, User have read the message.

A user can login from multiple devices, If user reads the message in at-least one device, It should be considered as notification message is read.

If the notification message is not read by user in any device, It needs to be considered as unread message.

How to identify the users with unread notification message count ? Any Idea on this ?

Upvotes: 0

Views: 1146

Answers (1)

Wajih
Wajih

Reputation: 4393

Select
  m.*, -- m.user_id
  IF (SUM(s.status) > 0, 1, 0) has_un_read,
  SUM(s.status) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id
having SUM(s.status) > 0

If status field has more than one state other than 0 and 1 then you can do that:

Select
  m.*,
  IF(SUM(IF(s.status = 1, 1, 0)) > 0, 1, 0) has_un_read,
  SUM(if(s.status = 1, 1, 0)) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id

UPDATE

In case you want to distinguish un read messages from every device:

Select
  m.*,
  s.device_id,
  IF (SUM(s.status) > 0, 1, 0) has_un_read,
  SUM(s.status) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id, s.device_id
having SUM(s.status) > 0

Upvotes: 2

Related Questions