我正在处理一个项目,需要检查用户是否喜欢过帖子并使用
我尝试使用以下查询:
我的问题是它一直给出2作为LD的计数,这不应该是可能的。
*注意:在我的代码中,我通过PDO使用
编辑:
我会循环遍历结果并将其格式化为json。
COUNT()
进行统计,如果返回0则表示没有喜欢过,返回1则表示已经喜欢过。我尝试使用以下查询:
SELECT P.id AS id
, U.username AS username
, P.body AS body
, P.timestamp AS timestamp
, COUNT(L.user_id) AS likes
, COUNT(LD.post_id) AS liked
FROM posts AS P
LEFT JOIN users AS U ON U.id = P.user_id
LEFT JOIN followers AS F ON F.user_id = 'user1'
LEFT JOIN likes AS L ON L.post_id = P.id
LEFT JOIN likes AS LD ON LD.post_id = P.id
AND LD.user_id = 'user1'
WHERE F.following_id = P.user_id
OR P.user_id = 'user1'
GROUP BY P.id
我喜欢表中的条目是
UserId|PostId|timestamp
user1 |post1 |time
user2 |post1 |time
我的问题是它一直给出2作为LD的计数,这不应该是可能的。
*注意:在我的代码中,我通过PDO使用
:user
,我实际上不会像那样键入ID。编辑:
$sql = "SELECT P.id AS id, P.user_id AS userid, U.username AS username, U.name AS name, U.verified AS verified, P.body AS body, P.data AS data, P.timestamp AS timestamp, P.type AS type, P.users AS users, COUNT(L.user_id) AS likes, COUNT(DISTINCT LD.post_id) AS liked FROM posts AS P LEFT JOIN users AS U ON U.id = P.user_id LEFT JOIN followers AS F ON F.user_id = :userid LEFT JOIN likes AS L ON L.post_id = P.id LEFT JOIN likes AS LD ON LD.post_id = P.id AND LD.user_id = :userid WHERE F.following_id = P.user_id OR P.user_id = :userid GROUP BY P.id";
$results = DB::query($sql, array(':userid' => $user_id));
我会循环遍历结果并将其格式化为json。
id
分组,但是随后选择了其他非聚合列。 - Tim BiegeleisenGROUP BY posts.id
时,你的意思是将属于每个id
的所有记录聚合在一起,然后返回一些东西(例如MAX、AVG等)。因此,在查询中选择用户名毫无意义。恰巧MySQL容忍它,带来的副作用是不清楚你的查询尝试做什么。 - Tim BiegeleisenCOUNT()
方法之外的所有选择使用GROUP BY
吗? - DanMAX
、AVG
等中。 - Tim Biegeleisen