如何从具有不同通知类型的数据库中检索通知 [英] how to retrieve notification from database with diffrent types of notification

问题描述

我正在像Facebook一样在通知系统上工作.

我的数据库结构是

我已将type_id设置为notification的类型. [留言，留言等]

问题是我如何从通知中检索数据，例如使用时间戳，我可以根据更新的日期或时间获取数据，但仍在获取类型之后.如何将类型与其他表匹配以显示结果，例如在Facebook中进行随机通知.谁能帮我解决数据库查询问题.

喜欢类型

留言墙-我必须同时从留言墙和通知表中获取数据

墙贴-我必须同时从墙贴和通知表中获取数据

稍后如何根据时间戳组合它们，并根据类似结果获得结果

例如

rahul 2小时前发布在您的墙上

ritu在1小时前评论了您的帖子

有谁可以帮忙?

解决方案

我不会在一个查询中尝试全部完成操作.我宁愿进行几个小的简单查询，对每种类型的实体(墙上的帖子，评论等)进行一次查询，然后将结果汇编到PHP代码中并发送给客户端.

不要过于复杂，已经有很多复杂性. :-)

查询示例:

SELECT p.*, n.*
FROM notifications n
INNER JOIN wall_posts p ON p.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;

SELECT c.*, n.*
FROM notifications n
INNER JOIN wall_comments c ON c.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;

i m working on a notification system just like facebook has .

my database structure is

i have made type_id such as type of notification . [ wall comments, wall post ,etc]

the problem is how do i retrieve data from the notification like using timestamp i can get data in according to updated date or time but still after getting the type . how to match the type with the other tables needed to show outcome like random notification in facebook . can anyone help me out with the database query .

like for type

wall comment -- i have to retrieve data from the wall comment and notification table both

wall post -- i have to retrieve data from the wall post and notification table both

how to combine them according to the timestamp later on and get results according to like this

for example

rahul posted on your wall 2 hours ago

ritu commented on your post 1 hrs ago

can any one help??

解决方案

I would not try to do it all in one query. I'd rather make several small simple queries, one for each type of entity (wall post, comment, etc), then assemble results in PHP code and send to the client.

Don't overcomplicate things, there's plenty of complexity already. :-)

Examples of queries:

SELECT p.*, n.*
FROM notifications n
INNER JOIN wall_posts p ON p.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;

SELECT c.*, n.*
FROM notifications n
INNER JOIN wall_comments c ON c.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;

这篇关于如何从具有不同通知类型的数据库中检索通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！