显示我与用户共有的条目，由喜欢和不喜欢分开 [英] Show entries that I have in common with an user, seperated by likes and dislikes

问题描述

我有一个类似于Facebook上的页面系统，每个用户都可以喜欢甚至不喜欢一个页面. status = 1是一个赞，0是一个讨厌.

I have a page system like the one on facebook where every user can like or even dislike a page. status = 1 is a like, 0 is a dislike.

这是我的查询:

SELECT  
p.title_de, de, p.keyname, l.status,
(CASE WHEN l.status = '1' THEN title_de END) AS liked,
(CASE WHEN l.status = '0' THEN title_de END) AS disliked
FROM pages_likes l  
JOIN pages p on p.id = l.page_id 
WHERE  p.keyname != 'rship'  AND l.uid = '311'  
GROUP BY l.page_id  
ORDER BY p.title_de ASC

问题:

1)我也想计算该用户的所有喜欢和不喜欢的条目.目前，我为此编写了一个额外的查询，但我想知道在该查询中实现该计数是否会更有效?我尝试使用COUNT，但随后我从整个查询中只得到一个结果.

1) I also want to count all liked and disliked entries by that user. At the moment I wrote an extra query for that but I wonder if it would be more efficient to implement that count in that query already? I tried to use COUNT but then I only get one result back from the whole query.

2)我想显示与该用户的共同/共同喜欢(uid表示user_id).我试图将AND 'uid' = 1添加到WHERE，但这没有用.这也应该由喜欢和不喜欢(status = 1和0)分开.我想应该用UNION编写子查询吗?但是我不知道到底是什么.

2) I want to display common/mutual likes I have with this user (uid means user_id). I tried to add an AND 'uid' = 1 to WHERE but that didn't work. This should also be seperated by likes and dislikes (status = 1 and 0). I suppose I should write a subquery with UNION? But I don't know how exactly.

这是我的结构:

推荐答案

将这些情况更改为

SUM(l.status = 1) AS likes,
SUM(l.status = 0) AS dislikes,

这篇关于显示我与用户共有的条目，由喜欢和不喜欢分开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！