php - MYSQL 查询 - 在当前查询中包含我关注的人的帖子

Question

我正在尝试获取属于我在当前查询中关注的用户的帖子。

目前我得到:当前用户的帖子 - 每个帖子和评论的点赞数。

不过，我需要得到这样的结果:当前用户的帖子 - 我关注帖子的人 - 每个帖子和评论的点赞计数。

最终结果就像大多数社交网络主页一样。他们会获取您的帖子以及您关注的人的帖子/或您 friend 的帖子。

这是我当前的查询:

 SELECT
      Posts.id
    , Posts.uuid
    , max(case when rcom.row_number = 1 then rcom.comment end) Comment_one
    , max(case when rcom.row_number = 2 then rcom.comment end) Comment_two
    , Posts.caption
    , Posts.path
    , Posts.`date`
    , USERS.id
    , USERS.username
    , USERS.fullname
    , USERS.profileImage
    , COALESCE(A.LikeCNT, 0) num_likes
FROM Posts
INNER JOIN USERS ON Posts.id = 145
            AND USERS.id = 145
LEFT JOIN (
          SELECT
                COUNT(A.uuidPost) LikeCNT
              , A.UUIDPost
          FROM Activity A
          WHERE type = 'like'
          GROUP BY
                A.UUIDPOST
          ) A ON A.UUIDPost = Posts.uuid 
LEFT JOIN (
      SELECT
            @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
          , comment
          , uuidPost
          , `date`
          , @prev_value := UUIDPOST
      FROM Activity
      CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci  ) xy
      WHERE type = 'comment'
      ORDER BY
            uuidPost
          , `date` DESC
      ) rcom ON rcom.uuidPost  = Posts.UUID
            AND rcom.row_number <= 2
GROUP BY
      Posts.id
    , Posts.uuid
    , Posts.caption
    , Posts.path
    , Posts.`date`
    , USERS.id
    , USERS.username
    , USERS.fullname
    , USERS.profileImage
    , COALESCE(A.LikeCNT, 0)

      ORDER BY date DESC

      ;

如何存储我的关注者？

我将我的关注者存储在“事件”表中，如下所示:id(currentUser) - idOtherUser(我关注的人)- 类型(等于“关注”)。

就是这样。这是sql fiddle以及一些示例数据。

如有任何其他问题，请告诉我。

Answer 1

这可能就是您正在寻找的。

我假设每个用户都必须自己点击关注按钮。另外这是从145用户的角度写的，你得改成WHERE - 条款。在您的示例中，用户 145 跟随用户 123 没有关系，我添加它是为了测试查询。

我删除了Users.id从选择列表中，因为它与 Posts.id 相同无论如何。

一些点赞数的示例数据就很好了，但不知道如何填充表格。

SELECT
    *
FROM
    (
        SELECT
            Posts.id,
            Posts.uuid,
            max(
                CASE
                WHEN rcom.row_number = 1 THEN
                    rcom.commentText
                END
            ) Comment_one,
max(
    CASE
    WHEN rcom.row_number = 1 THEN
        rcom.commentUserName
    END
) Comment_one_user,
            max(
                CASE
                WHEN rcom.row_number = 2 THEN
                    rcom.commentText
                END
            ) Comment_two,
max(
    CASE
    WHEN rcom.row_number = 2 THEN
        rcom.commentUserName
    END
) Comment_two_user,
            Posts.`date`,
            USERS.username,
            USERS.profileImage,
            COALESCE (A.LikeCNT, 0) num_likes
        FROM
            Posts
        INNER JOIN USERS ON Posts.id = USERS.id 
        LEFT JOIN (
            SELECT
                COUNT(A.uuidPost) LikeCNT,
                A.UUIDPost
            FROM
                Activity A
            WHERE
                type = 'like'
            GROUP BY
                A.UUIDPOST
        ) A ON A.UUIDPost = Posts.uuid
            LEFT JOIN (
                SELECT
                    @row_num :=
                IF (
                    @prev_value = UUIDPOST ,@row_num + 1,
                    1
                ) AS row_number,
                commentText,
                uuidPost,
Activity.`date`,
                @prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
            FROM
                Activity
                        CROSS JOIN (
                                SELECT
                                        @row_num := 1,
                                        @prev_value := '' COLLATE utf8_unicode_ci
                                ) xy
INNER JOIN USERS ON USERS.id = Activity.id
                        WHERE
                                type = 'comment'
                        ORDER BY
                                uuidPost,
                                `date` DESC
            ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2

WHERE Posts.id = 145

        GROUP BY
            Posts.id,
            Posts.uuid,
            Posts.`date`,
            USERS.username,
            USERS.profileImage,
            COALESCE (A.LikeCNT, 0)
        UNION
            SELECT
                Posts.id,
                Posts.uuid,
            max(
                CASE
                WHEN rcom.row_number = 1 THEN
                    rcom.commentText
                END
            ) Comment_one,
max(
    CASE
    WHEN rcom.row_number = 1 THEN
        rcom.commentUserName
    END
) Comment_one_user,
            max(
                CASE
                WHEN rcom.row_number = 2 THEN
                    rcom.commentText
                END
            ) Comment_two,
max(
    CASE
    WHEN rcom.row_number = 2 THEN
        rcom.commentUserName
    END
) Comment_two_user,
                Posts.`date`,
                USERS.username,
                USERS.profileImage,
                COALESCE (A.LikeCNT, 0) num_likes
            FROM
                Posts
            INNER JOIN Activity ON Activity.type = 'Follow' AND Posts.id = Activity.IdOtherUser
            INNER JOIN USERS ON Activity.IdOtherUser = USERS.id
            LEFT JOIN (
                SELECT
                    COUNT(A.uuidPost) LikeCNT,
                    A.UUIDPost
                FROM
                    Activity A
                WHERE
                    type = 'like'
                GROUP BY
                    A.UUIDPOST
            ) A ON A.UUIDPost = Posts.uuid
            LEFT JOIN (
                SELECT
                    @row_num :=
                IF (
                    @prev_value = UUIDPOST ,@row_num + 1,
                    1
                ) AS row_number,
                commentText,
                uuidPost,
Activity.`date`,
                @prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
            FROM
                Activity
                        CROSS JOIN (
                                SELECT
                                        @row_num := 1,
                                        @prev_value := '' COLLATE utf8_unicode_ci
                                ) xy
INNER JOIN USERS ON USERS.id = Activity.id
                        WHERE
                                type = 'comment'
                        ORDER BY
                                uuidPost,
                                `date` DESC
            ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2

WHERE Activity.id = 145

            GROUP BY
                Posts.id,
                Posts.uuid,
                Posts.`date`,
                USERS.username,
                USERS.profileImage,
                COALESCE (A.LikeCNT, 0)
    ) AS posts
ORDER BY
    posts.`date` DESC;

编辑

整理缩进并在选择帖子后加入评论+点赞数:

SELECT
    posts.id,
    posts.uuid,
    max(CASE WHEN rcom.row_number = 1 THEN rcom.commentText END) AS Comment_one,
    max(CASE WHEN rcom.row_number = 1 THEN rcom.commentUserName END) Comment_one_user,
    max(CASE WHEN rcom.row_number = 2 THEN rcom.commentText END) Comment_two,
    max(CASE WHEN rcom.row_number = 2 THEN rcom.commentUserName END) Comment_two_user,
    posts.`date`,
    posts.username,
    posts.profileImage,
    COALESCE(A.LikeCNT) AS likes
FROM
(
    SELECT
        Posts.id,
        Posts.uuid,
        Posts.`date`,
        USERS.username,
        USERS.profileImage
    FROM
        Posts
    INNER JOIN USERS 
        ON Posts.id = USERS.id 
    WHERE 
        Posts.id = 145
    GROUP BY
        Posts.id,
        Posts.uuid,
        Posts.`date`,
        USERS.username,
        USERS.profileImage
    UNION
        SELECT
            Posts.id,
            Posts.uuid,
            Posts.`date`,
            USERS.username,
            USERS.profileImage
        FROM
            Posts
        INNER JOIN Activity 
            ON Activity.type = 'Follow' AND Posts.id = Activity.IdOtherUser
        INNER JOIN USERS 
            ON Activity.IdOtherUser = USERS.id
        WHERE 
            Activity.id = 145
        GROUP BY
            Posts.id,
            Posts.uuid,
            Posts.`date`,
            USERS.username,
            USERS.profileImage
) AS posts
LEFT JOIN (
    SELECT
        COUNT(A.uuidPost) LikeCNT,
        A.UUIDPost
    FROM 
        Activity A
    WHERE
        type = 'like'
    GROUP BY
        A.UUIDPOST
) AS A ON A.UUIDPost = posts.uuid

LEFT JOIN (
    SELECT 
        commentText,
        uuidPost,
        `date`,
        commentUserId,
        commentUserName,
        @row_num := IF (@prev_value = UUIDPOST, @row_num + 1, 1) AS row_number,
        @prev_value := UUIDPOST
    FROM
    (
        SELECT
            commentText,
            uuidPost,
            Activity.`date`,
            USERS.id AS commentUserId,
            USERS.username AS commentUserName
        FROM
            Activity
        INNER JOIN USERS 
            ON USERS.id = Activity.id
        WHERE
            type = 'comment'
        ORDER BY
            uuidPost,
            `date` DESC
        ) AS comments

        CROSS JOIN (
            SELECT
                @row_num := 1,
                @prev_value := '' COLLATE utf8_unicode_ci
        ) AS xy
    ) rcom 
        ON rcom.uuidPost = posts.UUID AND rcom.row_number <= 2
GROUP BY 
    posts.uuid
ORDER BY
    posts.`date` DESC