Mysql keep track of user likes and dislikes

Question

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

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)

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

I have tried the query

  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

Answer 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

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')

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

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.

Answer 2

1. For the record, there is another way to obtain the same result set, whether it is faster or slower depends on the platform. This uses a scalar subquery instead of an aggregate on the outer query. The idea is, this is supposed to be easier if you think in terms of sets. Or in terms of Dimension-Facts.

   First we have to straighten out your names. Let's call your "table" bulletin_like and the main bulletin table bulletin (bulletin_id is a very silly name for either of them, that is more of a column name). And just call the boolean column like (if it is 1, like is true; if it is 0, like is false; that's what boolean means). Use the singular form for names.

   SELECT name AS bulletin, 
       (SELECT COUNT(like) 
           FROM  bulletin_like bl 
           WHERE bl.bulletin_id = b.bulletin_id 
           AND   like = 1
           ) AS like,
       (SELECT COUNT(like) 
           FROM  bulletin_like bl 
           WHERE bl.bulletin_id = b.bulletin_id 
           AND   like = 0
           ) AS dislike
   FROM bulletin b

2. You asked for the Normalisation tag,. That bulletin_like "table" is not Normalised. Get rid of theIdiot column, it serves no purpose other than a redundant column and an additional index. The PK is (bulletin_id, user_id).

   Unless you want users to post multiple likes and dislikes per poster per bulletin.

Answer 3

This query worked for me:

SELECT bulletin_id, 
    sum(like_dislike) AS likes, 
    sum((1-like_dislike)) AS dislikes 
FROM bulletins_ld 
    GROUP BY (bulletin_id);

This assumes that likedislike = 1 means "LIKE IT" and likedislike = 0 means "DOES NOT LIKE IT".

ID    LIKES    DISLIKES
1     2    1
2     0    1