Pfthb

This is my table structure:

tblTickets
ticket_id | subject | creator_id | created_date | last_updated | category_id | ticket_status

tblMessages
msg_id | msg | ticket_id | replied_by | reply_time

tblReadBy - PrimaryKey: (ticket_id,user_id)
ticket_id | user_id | last_read_time

Am trying to list all the tickets to the currently logged in User. That is, tickets that were created by them only.

Also, am showing a small color indication to let the the User know whether there's any unread messages.

So, whenever a User views a Ticket(by clicking from the list), a record will be either inserted/updated in tblReadBy with the ticket_id and user_id along with the current time)

My query for listing Tickets looks somewhat like this:

SELECT 
 t.`ticket_id`, 
 t.`subject`, 
 t.`user_id`, 
 t.`created_date`, 
 t.`last_updated`
 ( 
 SELECT COUNT(*) 
 FROM `tblReadBy` 
 WHERE `ticket_id` = t.`ticket_id` AND 
 `user_id` = 'XXX' AND 
 `last_read_time` < t.`last_updated` 
 ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

Here, XXX is the user_id of the currently logged in User. This query works fine if there's a row in tblReadBy table for the currently logged in user_id and the ticket_id.

But if there's no row exists for this user_id and ticket_id, then as per logic it's an "unread ticket", but the query would return 0 for has_unread_msg column.

How can I fix this particular case?

NOTE: There would be more than one user replying to a Ticket. Like the actual User, Moderator (more than one), Admin, etc.

Thank you

SOLUTION:

So far, what I figured out is this:

SELECT 
 t.`ticket_id`, 
 t.`subject`, 
 t.`user_id`, 
 t.`created_date`, 
 t.`last_updated`
 ( 
 SELECT 
 COALESCE(SUM(CASE WHEN `last_read_time` < t.`last_updated` THEN 1 ELSE 0 END), 1)
 FROM `tblReadBy` 
 WHERE `ticket_id` = t.`ticket_id` AND 
 `user_id` = 'XXX' 
 ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

Am still trying to test all possible cases.

We can use SUM() function's NULL handling behavior. It will return NULL if no row exists.

If the return set has no rows, SUM() returns NULL

MySQL automatically typecasts results of a Boolean comparison operator to 0/1. Using this behavior, we can can use Sum() and Coalesce().

SELECT 
 t.`ticket_id`, 
 t.`subject`, 
 t.`user_id`, 
 t.`created_date`, 
 t.`last_updated`
 ( 
 -- coalesce handles case when no rows
 SELECT COALESCE(SUM(`ticket_id` > 0), 1)
 FROM `tblReadBy` 
 WHERE `ticket_id` = t.`ticket_id` AND 
 `user_id` = 'XXX' AND 
 `last_read_time` < t.`last_updated` 
 ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC 

As I see it you need to count both messages that has been update in tblReadBy and messages that are new and have never been read, that is they exists in tblTickets but not in tblReadBy.

Here is my solution that is a bit complicated, I would love to see someone improve it, but basically I join agains two `SELECT ... COUNT(*)´, one for completely new messages and one for updated

SELECT t.ticket_id, subject, t.user_id, created_date, last_updated, IFNULL(c1.count, 0) + IFNULL(c2.count, 0)
FROM tblTickets t
LEFT JOIN (SELECT ticket_id, user_id, COUNT(*) count
 FROM tblTickets t
 WHERE NOT EXISTS (SELECT * 
 FROM tblReadBy r 
 WHERE t.ticket_id = r.ticket_id 
 AND t.user_id = r.user_id)
 GROUP BY ticket_id, user_id) AS c1 ON c1.ticket_id = t.ticket_id AND c1.user_id = t.user_id
LEFT JOIN (SELECT r.ticket_id, r.user_id, COUNT(*) count
 FROM tblReadBy r
 JOIN tblTickets t ON t.ticket_id = r.ticket_id AND t.user_id = r.user_id
 WHERE r.last_read_time < t.last_updated
 GROUP BY r.ticket_id, r.user_id) AS c2 ON c2.ticket_id = t.ticket_id AND c2.user_id = t.user_id
WHERE t.user_id = 'aaa'

This could work because it left joins the two tables to find out whether there are any records in tblReadBy and then uses the result in the subquery. Unfortunately I couldn't test it.

SELECT 
t.`ticket_id`, 
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
 SELECT 
 CASE WHEN r.`ticket_id` IS NULL THEN 1 
 ELSE COUNT(*) 
 END AS message_count
 FROM `tblReadBy` 
 WHERE `ticket_id` = t.`ticket_id` AND 
 `user_id` = 'XXX' AND 
 `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
 r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC

You might need to add r.ticket_id in the SELECT list of the outer query like so:

SELECT 
t.`ticket_id`, 
r.`ticket_id`,
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
 SELECT 
 CASE WHEN r.`ticket_id` IS NULL THEN 1 
 ELSE COUNT(*) 
 END AS message_count
 FROM `tblReadBy` 
 WHERE `ticket_id` = t.`ticket_id` AND 
 `user_id` = 'XXX' AND 
 `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
 r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC