显示“订阅者"帖子和用户自己的帖子 [英] Showing "Subscriber" Posts And User's Own Posts

问题描述

这让我难住了一段时间.

This has stumped me for some time.

我的问题:我有 2 个不同的表.. 一个用于用户帖子的表和一个用于订阅者的表.

My problem: I have 2 different tables.. A table for user posts and a table for subscribers.

订阅者表如下所示:

SubscriberID -> ProfileID
1 -> 2
1 -> 3
2 -> 3
2 -> 4
3 -> 2

SubscriberID -> ProfileID
1 -> 2
1 -> 3
2 -> 3
2 -> 4
3 -> 2

我的帖子表如下所示:

PostID -> AuthorID -> PostDate -> PostBody
1 -> 2 -> 12/20/12 -> 你好词
2 -> 3 -> 12/21/12 -> 再见世界
3 -> 1 -> 12/22/12 -> 哦等等
4 -> 4 -> 12/23/12 -> 有人还在吗?

My posts table looks like this:

PostID -> AuthorID -> PostDate -> PostBody
1 -> 2 -> 12/20/12 -> Hello Word
2 -> 3 -> 12/21/12 -> Bye Bye World
3 -> 1 -> 12/22/12 -> Oh Wait
4 -> 4 -> 12/23/12 -> Is anyone still here?

基本上，它的工作原理是拥有 ID 的用户订阅了 ID 2 和 3 的用户.ID #2 订阅了 ID #3 和 #4.如果用户订阅了某个用户，他们只能看到来自他们订阅的人的帖子.现在，我正在使用我在类似问题中看到的以下内容:

Basically, how it works is that the user with the ID is subscribed to the user with the ID 2 and 3. ID #2 is subscribed to ID #3 and #4. If a user is subscribed to a certain user, they can see only posts from the person they subscribed to. Now, I'm using the following I saw in a similar question:

SELECT POSTS.*
FROM POSTS
JOIN SUBSCRIBERS
ON POSTS.AUTHORID = SUBSCRIBERS.PROFILEID
WHERE SUBSCRIBERS.SUBSCRIBERID = ?
ORDER BY POSTS.POSTID DESC LIMIT 10

这工作正常，但它也不会显示用户的帖子.我试过修改它，但它不起作用:\

This works fine, but it does not show the user's post as well. I've tried modifying it, but it's not working :\

如果您想知道，?"代表用户的ID

If you're wondering, the "?" represents the user's ID

所以如果可以的话，如果有人能告诉我如何将用户自己的帖子与用户订阅的人的帖子一起包含在内，那就太好了

So if you can, it would be great if someone could tell me how to include the user's own posts alongside with the posts from the people the user subscribes to

推荐答案

您可以将查询修改为:

SELECT POSTS.*
FROM POSTS
LEFT JOIN SUBSCRIBERS
ON POSTS.AUTHORID = SUBSCRIBERS.PROFILEID
WHERE SUBSCRIBERS.SUBSCRIBERID = ? OR POSTS.AUTHORID = ?
GROUP BY POSTS.POSTID ORDER BY POSTS.POSTID DESC LIMIT 10 

它也选择用户自己的帖子.希望这会有所帮助.

It selects the user own posts as well. Hope this would help.

更新:添加了 GROUP BY POSTS.POSTID 以便删除重复项，因为您只在 POSTS 表中查找数据.

Updated : Added GROUP BY POSTS.POSTID so duplicates are removed as you only look for data in POSTS table.

当您像传递值一样运行查询时-例如.对于 ID 为 1 的用户，查询如下所示:

When you run query like passing values- Eg. for user having id 1 the query looks like :

SELECT POSTS.*
FROM POSTS
LEFT JOIN SUBSCRIBERS
ON POSTS.AUTHORID = SUBSCRIBERS.PROFILEID
WHERE SUBSCRIBERS.SUBSCRIBERID = 1 OR POSTS.AUTHORID = 1 GROUP BY POSTS.POSTID
ORDER BY POSTS.POSTID DESC LIMIT 10

结果是:

PostID  AuthorID    PostDate    PostBody

3       1   2012-12-21  Oh Wait
2       3   2012-12-21  Bye Bye World
1       2   2012-12-20  Hello Word

这是正确地将值传递给选择查询时得到的结果.传递给 SUBSCRIBERID 和 AUTHORID 的值应该相同.LEFT JOIN 可以解决您的问题.

This is what you get when pass values to the select query properly. The values passed to SUBSCRIBERID and AUTHORID should be same. The LEFT JOIN would fix your problem.

这篇关于显示“订阅者"帖子和用户自己的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！