I have 3 tables: alert, user_to_alert, and user_device. The device table holds a list of tokens and and includes a user_id field.
The user_to_alert table holds records of when a user reads an alert. If a record doesn't exist for the appropriate alert_id/user_id combo, it means the user hasn't read the alert. There is also an alert_read column that can be zero (if the user marks the alert as unread after reading it).
I have a query that loads a list of device tokens and I need to expand this to include the number of unread alerts. This is the query I've come up with
SELECT d.*, (
SELECT COUNT(*) FROM `alert` AS a
LEFT JOIN `user_to_alert` AS uta ON
( uta.`alert_id` = a.`alert_id` AND uta.`user_id` = d.`user_id` )
WHERE uta.`alert_read` = 0 OR uta.`alert_read` IS NULL) AS user_badge_number
FROM `user_device` AS d
WHERE d.`device_type` = 'iOS' AND d.`device_active` = 1
GROUP BY d.`device_token`
#1054 - Unknown column 'd.user_id' in 'on clause'
uta.user_id = d.user_id
As you noted, its not hard to count the number of alerts that were read. You can subtract that from total alerts to get the number unread:
SELECT d.*, ( SELECT COUNT(*) FROM `alert` ) - ( SELECT COUNT(*) FROM `user_to_alert` WHERE `user_id` = d.`user_id` AND `alert_read` = 1 ) FROM `user_device` AS d WHERE d.`device_active` AND d.`device_type` = 'iOS';
But note there is a problem with the design though, which is that if a device is added 6 months from now, all the alerts from the past will show up as unread on that new device. There are similar problems if a device gets reassigned to a new user, or an existing user switches devices (depending on whether you are counting unread alerts per user or per device).
To get around this you could insert rows into
user_to_alert to mark all the old alerts read, every time a device is added. But I think the better answer is to insert rows into
user_to_alert as each alert is created. This would have several advantages: