Get number of rows in reference-CodePudding

I have to tables with one referencing the other one. Here is the code:

CREATE TABLE blogs (
    article LONGTEXT,
    id VARCHAR(255) PRIMARY KEY
);

CREATE TABLE blog_eval (
    blog_id VARCHAR(255) REFERENCES blogs(id) ON DELETE CASCADE,
    user_id VARCHAR(255) REFERENCES users(id),
    is_like BOOLEAN, --true if like / false if dislike
    PRIMARY KEY (blog_id, user_id)
);

I need to get all blogs with two additional columns: sum of likes and sum of dislikes.

I am not really sure how to combine a JOIN with the COUNT-Function and also add the logic to distinguish between likes and dislikes.

I am using MySQL.

Thanks for your help!

CodePudding user response：

One option uses conditional counts:

select b.*, e.cnt_dislike, e.cnt_dislike
from blogs b
left join (
    select blog_id, sum(is_like) cnt_like, sum(1 - is_like) cnt_dislike
    from blog_eval
    group by blog_id
) e on e.blog_id = b.id

Since is_like is a boolean (an integer in essence), expression sum(is_like) counts the true values. Pre-aggregating in a subquery might help efficiency here.

In recent MySQL versions (>= 8.0.14), we can express this with a lateral join, which might perform better:

select b.*, e.*
from blogs b
left join lateral (
    select sum(e.is_like) cnt_like, sum(1 - e.is_like) cnt_dislike
    from blog_eval e
    where e.blog_id = b.id
) e on 1 = 1

CodePudding user response：

Join the two tables, and use SUM() to total the likes and dislikes.

SELECT b.id, SUM(e.is_like) AS likes, SUM(NOT e.is_like) AS dislikes
FROM blogs AS b
LEFT JOIN blog_eval AS e ON e.blog_id = b.id
GROUP BY b.id

CodePudding user response：

Can't you just get all the data you need from the one table?

SELECT blog_id, is_like, count(*)
FROM blog_eval
GROUP BY blog_id, is_like;