Combine many notifications to one

Question

I'm re-creating my notification system to make it able to combine many notifications to one. Instead of showing 9 rows that say "Adam has answered your question", it could say "9 people have answered your question".

My current notification table looks like this:

notification_id (PK) | recipient_id | sender_id | type | foreign_id | date | viewed

Recipient_id is the one to get the notification. Sender_id is the one it came from. Type is what kind of notification, so I know what it should say, "xx has answered" or "xx liked your post". Foreign_id could be the ID of a question.

Which would be the best way to create this "combine" system? Should I update the notification when there is a new answer and say that another person has answered too, or should the query maybe count how many there is of the same foreign_id. What do you think?

Answer 1

Try this:

SELECT foreign_id, SUM(IF (TYPE= 'like', 1, 0)) likecnt, 
       SUM(IF(TYPE = 'answer', 1, 0)) anscnt 
FROM notification 
GROUP BY foreign_id

EDIT: Check this

SELECT n.foreign_id, n.sender_id
FROM notifications n 
INNER JOIN (SELECT foreign_id, MAX(DATE) DATE 
            FROM notifications 
            GROUP BY foreign_id, user_id
           )  a ON n.foreign_id = a.foreign_id AND n.date = a.date