将多个通知合并为一个 [英] Combine many notifications to one

问题描述

我正在重新创建通知系统，使其能够将多个通知合并到一个通知系统中.而不是显示9行说亚当回答了您的问题"，而是说"9人回答了您的问题".

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".

我当前的通知表如下:

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

Recipient_id是获取通知的对象. Sender_id是它的来源.类型是什么类型的通知，所以我知道它应该说的是"xx已回答"或"xx喜欢您的帖子". Foreign_id可以是问题的ID.

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.

哪种是创建此组合"系统的最佳方法?我应该在有新答案时更新通知，并说另一个人也已回答，还是应该在查询中计算出多少个相同的foreign_id.你觉得呢?

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?

推荐答案

尝试一下:

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

选中此

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

这篇关于将多个通知合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！