MYSQL 查询 - 获取当前用户的帖子以及他正在关注的用户的帖子

我正在尝试查询以获取当前用户的帖子以及他所关注的人的帖子。就像大多数社交网络在其主页上所做的那样。

我知道如何查询当前用户的所有帖子，但我正在努力获取用户正在关注的帖子。

我看到了另一个问题Mysql select 查询获取当前用户帖子和关注的朋友帖子 https://stackoverflow.com/questions/26428551/mysql-select-query-for-getting-current-user-post-and-followed-friend-post?rq=1

但答案并没有真正帮助我......

这是到目前为止我的查询：

SELECT P.id, 
P.caption,
P.date,
U.id,
U.fullname,
U.username,
F.IdOtherUser
FROM USERS AS U
INNER JOIN Activity AS F
ON U.id = F.id
INNER JOIN Posts AS P 
ON P.id = U.id OR P.id = F.IdOtherUser
WHERE P.id = 145
ORDER BY P.id DESC

145=当前用户。

Activity.IdOtherUser = 用户“145”正在关注

Activity.id = 将是“145”当前用户

如果有人能帮我解决这个问题，我将非常感激！

似乎无法完全理解它，因为我对 MYSQL 还很陌生......

Fix

(SELECT P.id as postid, 
        P.caption,
        P.date,
        U.id as userid,
        U.fullname,
        U.username,
        coalesce(Activity.LikeCNT,0),
        Activity.CurrentUserLiked
        FROM USERS AS U
        INNER JOIN Posts AS P 
        ON P.id = U.id
        LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = 145 then 1 else 0 end) as CurrentUserLiked
        FROM Activity Activity
        WHERE type = 'like' 
        GROUP BY Activity.uuidPost) Activity
        ON Activity.uuidPost = P.uuid
        AND Activity.id = U.id
        WHERE U.id = 145)
UNION
(SELECT P.id, 
        P.caption,
        P.date,
        U.id,
        U.fullname,
        U.username,
        coalesce(Activity.LikeCNT,0),
        Activity.CurrentUserLiked
        FROM Activity AS A
        INNER JOIN USERS AS U 
        ON A.IdOtherUser=U.id
        INNER JOIN Posts AS P 
        ON P.id = U.id
        LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = 145 then 1 else 0 end) as CurrentUserLiked
    FROM Activity Activity
    WHERE type = 'like' 
    GROUP BY Activity.uuidPost) Activity
    ON Activity.uuidPost = P.uuid
    AND Activity.id = U.id
    WHERE A.id = 145)

要获取给定用户 (id=145) 及其关注帖子的所有用户的所有帖子，以及每个帖子的用户详细信息，我将重写查询以使用union而不是 or，从而简化了逻辑。第一个选择获取给定用户的帖子，第二个选择获取其关注的用户的帖子：

(SELECT P.id as postid, 
       P.caption,
       P.date,
       U.id as userid,
       U.fullname,
       U.username,
FROM USERS AS U
INNER JOIN Posts AS P ON P.userid = U.id
WHERE U.id = 145)
UNION
(SELECT P.id, 
       P.caption,
       P.date,
       U.id,
       U.fullname,
       U.username,
FROM Activity AS A
INNER JOIN USERS AS U ON A.IdOtherUser=U.id
INNER JOIN Posts AS P ON P.userid = U.id
WHERE A.id = 145)
ORDER BY postid DESC

假设：

1. Activity.id 字段代表关注其他用户的用户。如果不是，您需要将字段名称更改为适当的名称。

2. 发明了 Posts 表的 userid 字段，表示发布帖子的用户。请在其位置使用正确的字段名称。