我正在尝试获取属于某个用户的所有帖子,然后为每个帖子计数。
这里我得到属于某个用户的所有帖子:
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
这样做的一种方法是使用内联选择…使用相关查询
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将是uidpost的活动表中的随机ID,对吗?
我已经修改了这个,包括如果当前用户(145?)
通过在子查询中添加一个名为CurrentUserLiked的新列来"点赞"该帖子。我必须假设活动表上userID的列名是userID;必要时进行更改。我还假设当前用户被定义为145,这将最终通过php传递进来。还有其他两架145。
我不确定你想用当前用户的a.p uidpost做什么,所以我现在把它单独留下。
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