实现关注用户后查看其私有帖子的SQL查询需求

Hey there! Let's fix your query so that you can properly view private posts from users you follow. First, let's break down the problem and adjust your SQL to respect the follow relationship and post visibility rules.

Issues with Your Current Query #

Your current query doesn’t account for the followings table at all — right now, it will show all posts from user 3 (regardless of if you follow them) plus all public posts. That’s not what you want, since private posts should only be visible to followers of the post’s author.

Corrected Query #

Assuming:

• Your current user ID (user A) is {current_user_id} (replace this with the actual ID of the logged-in user)
• facebook_posts.status uses values like 'public' and 'private' to control visibility
• followings.follower_id = the ID of the user doing the following (user A), followings.following_id = the ID of the user being followed (user B)

Here's the adjusted query:

SELECT 
    fp.*,
    u.*,
    fp.post_id AS post_id,
    fp.status AS visibility
FROM 
    facebook_posts fp
INNER JOIN 
    users u ON u.id = fp.user_id
LEFT JOIN 
    followings f ON f.following_id = fp.user_id AND f.follower_id = {current_user_id}
WHERE 
    -- Show all your own posts
    fp.user_id = {current_user_id}
    -- Show public posts from any user
    OR fp.status = 'public'
    -- Show private posts from users you follow
    OR (fp.status = 'private' AND f.following_id IS NOT NULL)
ORDER BY 
    fp.post_id DESC
LIMIT 3;

How This Works #

Let’s break down the key parts:

• LEFT JOIN with followings: We join the followings table to check if the current user follows the post’s author. The join condition ensures we only look for follow relationships where the current user is the follower and the post’s author is the followed user.
• WHERE clause logic:
  • First condition: Always show your own posts, regardless of visibility.
  • Second condition: Show all public posts from any user.
  • Third condition: Only show private posts if the current user follows the post’s author (verified by the followings join not being NULL).

Alternative Simplified Version (Targeted to a Specific Followed User) #

If your only goal is to view private posts from a specific followed user (e.g., user B with ID 3), you can use this more targeted query:

SELECT 
    fp.*,
    u.*,
    fp.post_id AS post_id,
    fp.status AS visibility
FROM 
    facebook_posts fp
INNER JOIN 
    users u ON u.id = fp.user_id
INNER JOIN 
    followings f ON f.following_id = fp.user_id AND f.follower_id = {current_user_id}
WHERE 
    fp.user_id = 3
    AND fp.status = 'private'
ORDER BY 
    fp.post_id DESC
LIMIT 3;

This will only return private posts from user 3, but only if the current user (user A) is actually following them.

内容的提问来源于stack exchange，提问作者Peter