MYSQL 查询 - 获取当前用户的帖子和他关注的用户的帖子 [英] MYSQL Query - Get current user's posts and the user's he's following posts

问题描述

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

I am trying to query to get the current user's posts and the posts of the people he is following. Like most social networks do in their home page.

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

I know how to query all of the current user's posts, but I am struggling to get the user's he's following posts.

我看到了另一个问题 Mysql 选择查询获取当前用户帖子和关注的朋友帖子

但是那里的答案并没有真正帮助我...

But the answer there isn't really helping me...

这是我目前的查询:

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 = 当前用户.

145 = current user.

Activity.IdOtherUser = 用户145"正在关注

Activity.IdOtherUser = the user '145' is following

Activity.id = 将是当前用户的145"

Activity.id = will be '145' the current user

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

If anyone can help me sort this I'd appreciate it alot!

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

Cannot seem to understand it fully, as I am quite new to MYSQL...

修复

(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，从而简化逻辑.第一个选择获取给定用户的帖子，第二个获取它关注的用户的帖子:

To get all the posts a given user (id=145) and all the users it follows posts, along with the user details for each post, I would rewrite the query to use union instead of the or, thus simplifying the logic. The 1st select gets the posts of the given user, the 2nd gets the posts of the users it is following:

(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 字段代表关注其他用户的用户.如果不是，则需要将字段名称更改为适当的名称.

1. Activity.id field represents the user, who follows the other user. If not, you need to change the field name to the appropriate one.

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

Invented the userid field of Posts table representing the user who posted the post. Pls use the correct field name in its place.

这篇关于MYSQL 查询 - 获取当前用户的帖子和他关注的用户的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！