MySQL跟踪用户的好恶 [英] Mysql keep track of user likes and dislikes

问题描述

我正在创建一个布告栏应用程序.每个公告都可以被站点用户喜欢或不喜欢.为了跟踪喜欢和不喜欢的地方，我创建了以下数据库表

I am creating a bulletin board application. Each bulletin can be liked or disliked by users of the site.To keep track of the likes and dislikes I have created the following database table

id  user_id   bulletin_id   like_dislike
1   1         1             1
2   1         2             0
3   3         1             1
4   2         1             0

在like_dislike栏中，1表示喜欢"，0表示不喜欢" 我知道怎么问 -1号公告被点赞了几次(2) -不喜欢公告1的次数(1)

In the like_dislike column 1 means 'Like It', 0 means 'Don't like it' I know how to ask. - How many times was bulletin 1 liked (2) - How many times was bulletin 1 disliked (1)

但是我该如何同时询问这两个问题呢?也就是说，公告1被喜欢和不喜欢多少次

But How do I do a query to ask those two questions at the same time? That is, how many times was bulletin 1 liked and disliked

liked  disliked
2      1

我已经尝试过查询

  SELECT count(like_dislike) AS likes, count(like_dislike) AS dislikes FROM bulletins_ld
where bulletins_id = 1 AND likes = 1 AND dislikes = 0

但是我得到的只有两次，这并不奇怪.
我能想到的唯一解决方案是拥有一个单独的喜欢"和不喜欢"列

but all I get is two twice which is not surprising.
The only solution I can think of is having a separate like and dislike column

推荐答案

您可以通过对单个like_dislike列使用相反的条件，通过聚合查询来做到这一点(我假设该列中的"1"表示喜欢" ').

You can do this with an aggregate query, using opposing conditions on the single like_dislike column (I am assuming below that a '1' in that column means 'liked').

SELECT bulletin_id, 
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bulletins_ld
GROUP BY bulletin_id

更新:根据下面评论中的讨论，可以将like/dislike列归一化到其自己的表中，就像这样(示例故意是愚蠢的...):

Update: As per the discussion in the comments below, the like/dislike column could be normalized into its own table, like so (example deliberately silly...):

CREATE TABLE how_user_feels(
    feeling_id INT,
    feeling_desc VARCHAR(20)
)

INSERT INTO how_user_feels(feeling_id, feeling_desc) VALUES
(0, 'Undecided'),
(1, 'Likes It'),
(2, 'Could Do Without It')

然后，将公告表中的Likes_Dislikes列替换为外键feeling_id，默认值为0.假设您在用户第一次查看公告时在此表中输入一条记录，使它们未定" "，并在他们对该公告进行投票时更新该记录.您可以这样查询结果:

The Likes_Dislikes column in the Bulletin table is then replaced by the foreign key feeling_id, with a default to 0. Let's say that you then enter a record in this table when a user first views a bulletin, making them "Undecided" by default, and update that record when they vote on the bulletin. You could query the results like so:

SELECT bulletin_id, 
       SUM(CASE WHEN feelings_id = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN feelings_id = 2 THEN 1 ELSE 0 END) AS dislikes,
       SUM(CASE WHEN feelings_id = 0 THEN 1 ELSE 0 END) AS doesnt_seem_to_care
FROM bulletins_ld b
INNER JOIN how_user_feels h ON b.feeling_id = h.feeling_id
GROUP BY bulletin_id

请记住，这只是一种方法，可能对您的情况没有用.但是，如果您决定改变模型来表达用户对公告的感受，例如对五星级评级系统进行表达或更改，就可以做到这一点.更改数据库架构-只需更改how_user_feels表中的记录以及相关的查询即可.

Keep in mind, this is just one approach, and may not be useful in your case. But if you ever decided to change or expand the model by which a user expresses their feelings for a bulletin, say to a five-star rating system, you could do that without changing the database schema - just alter the records in the how_user_feels table, and the associated queries.

这篇关于MySQL跟踪用户的好恶的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！