php - MYSQL 查询 - 选择帖子和每个帖子的喜欢计数

Question

我正在尝试获取属于某个用户的所有帖子，然后对每个帖子进行点赞。

在这里我得到了属于特定用户的所有帖子:

SELECT Posts.id,
Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage
    FROM A.Posts JOIN A.USERS ON
    Posts.id = 145 AND USERS.id = 145 ORDER by date DESC
    LIMIT 0, 5

下面是我如何查询每个帖子的点赞数:

SELECT COUNT(uuidPost)
 FROM Activity
 WHERE type =  "like" AND uuidPost = "FA4C8196-CEA3-4373-94B2-59F387BB1906"

不确定如何组合它们？

如果有人可以提供帮助或提供有关查询的提示，我将不胜感激!

提前致谢!

SELECT Posts.id,
    Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage,
    coalesce(A.LikeCNT,0),
IF( A.uuidPost IS NOT NULL , 1, 0 ) AS CurrentUser
FROM Posts 
INNER JOIN USERS 
ON Posts.id = 145 
AND USERS.id = 145
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost, A.id
FROM Activity A
WHERE type = 'like' 
GROUP BY A.UUIDPOST) A
on A.UUIDPost=Posts.uuid
AND A.id = Posts.id
WHERE Posts.id = 145
ORDER BY date DESC
LIMIT 0, 5

Answer 1

执行此操作的一种方法是使用内联选择...使用相关查询。

SELECT Posts.id,
    Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage,
   (SELECT COUNT(A.uuidPost)
    FROM Activity A
    WHERE type =  'like' 
      AND A.uuidPost = Posts.uuid) as LikeCNT
FROM Posts 
INNER JOIN USERS 
  ON Posts.id = 145 
 AND USERS.id = 145
ORDER BY date DESC
LIMIT 0, 5

虽然我不是大型数据集的忠实粉丝......我通常更喜欢......

   SELECT Posts.id,
        Posts.uuid,
        Posts.caption,
        Posts.path,
        Posts.date,
        USERS.id,
        USERS.username,
        USERS.fullname,
        USERS.profileImage,
        coalesce(A.LikeCNT,0)
    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
    ORDER BY date DESC
    LIMIT 0, 5

因为引擎只生成一次计数数据集，而不必为每个 UUID 执行一次。我们必须使用合并作为 UUID，因为帖子可能没有喜欢，因此不存在记录，因此左连接上的空值。所以要显示 0，我们需要取第一个非空值，可以是数字，也可以使用 0。

---更新:

您确实意识到您添加的 A.ID 将是 uuidpost 事件表中的随机 ID，对吧？

我修改了它以包括当前用户(145？)是否通过向子查询添加一个名为 CurrentUserLiked 的新列来“喜欢”该帖子。

我不得不假设事件表中 userID 的列名是 UserID；根据需要更改它。我还假设当前用户被定义为 145，这最终将通过 php 传入。以及列出的其他两个 145。

我不确定您要对当前用户的 A.uuidPost 做什么，所以我暂时不去管它。

SELECT Posts.id,
    Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage,
    coalesce(A.LikeCNT,0),
IF( A.uuidPost IS NOT NULL , 1, 0 ) AS CurrentUser,
    A.CurrentUserLiked
FROM Posts 
INNER JOIN USERS 
ON Posts.id = 145 
AND USERS.id = 145
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost, A.id, sum(CASE WHEN A.USERID = 145 then 1 else 0 end) as CurrentUserLiked
           FROM Activity A
           WHERE type = 'like' 
           GROUP BY A.UUIDPOST) A
on A.UUIDPost=Posts.uuid
AND A.id = Posts.id
WHERE Posts.id = 145
ORDER BY date DESC
LIMIT 0, 5