获取喜欢和评论的帖子 - 一个查询？ - 编程

I am about to develop a wall for a users-profile. It basically works the same way as Facebooks comment/like system.

我即将为用户配置文件开发一个墙。它基本上与Facebook评论/喜欢系统的工作方式相同。

So what I want to do is fetch all wall-posts associated with a users profile (by current id variable) and all comments and likes associated with this specific post. I want to use user data from both comments and likes (ie.: name, user_id, email, whatever).

所以我想要做的是获取与用户个人资料相关的所有墙贴(通过当前的id变量)以及与此特定帖子相关的所有评论和喜欢。我想使用来自评论和喜欢的用户数据(即:姓名,user_id,电子邮件等等)。

So the question is: Is it possible?

所以问题是:有可能吗?

According to this thread's answer it is not: Fetch all news and all comments

根据这个帖子的答案,它不是:获取所有新闻和所有评论

According to this thread it is: mysql/php: show posts and for each post all comments

根据这个帖子它是:mysql / php:显示帖子和每个帖子的所有评论

I have tried working with this code but it doesn't seem to work, as it just fetches all comments from DB.

我尝试使用此代码,但它似乎不起作用,因为它只是从DB获取所有注释。

SELECT p.*, c.*, l.*, u.name as post_author, u2.name as comment_author, u3.user_id
FROM edu_wall p
LEFT JOIN edu_comments c ON c.comment_entity = p.post_id
LEFT JOIN edu_likes l ON l.like_entity = p.post_id
LEFT JOIN edu_users u ON u.user_id = p.post_author
LEFT JOIN edu_users u2 ON u2.user_id = c.comment_author
LEFT JOIN edu_users u3 ON u3.user_id = l.like_author

So what is the solution? Should I start up by fetching all wall posts, and then inside that while(), loop comments and likes? It just seems like a very complex and resource-intensive solution.

那么解决方案是什么?我应该通过获取所有墙贴,然后在while()内部启动,循环评论和喜欢?它似乎是一个非常复杂和资源密集的解决方案。

Thanks in advance!

提前致谢!

2 个解决方案

#1

This is certainly possible in a single query.

这在单个查询中当然是可能的。

Without knowing your table layouts and what fields you want to use I can't really give any suggested code at the moment.

如果不知道您的表格布局以及您想要使用的字段,我现在无法提供任何建议的代码。

However the issue is that a single wall post will have numerous comments and numerous likes. You current SQL would bring back one row for every combination of comment and like for each wall post. So if a wall post had 2 comments and 2 likes that would result in 4 rows.

然而问题是,一个墙上的帖子会有很多评论和众多喜欢。您当前的SQL会为每个墙贴的每个评论组合带回一行。因此,如果一个墙贴有2条评论和2条喜欢会导致4行。

You can get round this a couple of ways. Either make sure you have an order clause on the SELECT and then manually in php process each row and decide if the comment or like is a new one for that wall post. If so process it and if not ignore it.

你可以通过几种方式解决这个问题。要么确保在SELECT上有一个order子句,然后在php进程中手动处理每一行,并确定评论或类似内容是否为该墙贴的新内容。如果这样处理它,如果没有忽略它。

Alternatively you can use aggregate functions, such as GROUP_CONCAT, to bring back the fields you want in one row. Then explode them out in php. Sql something like this:-

或者,您可以使用聚合函数(例如GROUP_CONCAT)在一行中恢复所需的字段。然后在php中爆炸它们。这样的Sql: -

SELECT p.*, u.name as post_author, GROUP_CONCAT(u2.name SEPARATOR '#') as comment_authors, GROUP_CONCAT(u3.user_id SEPARATOR '#') as post_likers
FROM edu_wall p
LEFT JOIN edu_comments c ON c.comment_entity = p.post_id
LEFT JOIN edu_likes l ON l.like_entity = p.post_id
LEFT JOIN edu_users u ON u.user_id = p.post_author
LEFT JOIN edu_users u2 ON u2.user_id = c.comment_author
LEFT JOIN edu_users u3 ON u3.user_id = l.like_author
WHERE p.id = $SomeWallId
GROUP BY p.id

获取喜欢和评论的帖子 - 一个查询？

2 个解决方案

#1

更多相关文章

随机推荐