sql - How to correctly use COUNT() when multiple joins are used?

Question

I have the following schema:

Multiple webinar entities can have multiple categories hence the webinarcategorymapping table.

What I need to achieve is find the most popular webinars (by likes number) of a specific category.

For doing this, I've written the query below:

select
    webinar.id, webinar.name as "webinar", webinar.publishat,
    string_agg(category.name, ',' order by category.name) as categories,
    count("like".likeableid) as "likes_count"
    from
        webinar
        join "like" on webinar.id = "like".likeableid and "like".likeabletype = 'webinar'
        join webinarcategorymapping on webinarcategorymapping.webinarid = webinar.id
        join category on category.id = webinarcategorymapping.categoryid
    group by "like".likeableid, webinar.id
    having
        string_agg(category.name, ',' order by category.name) ilike '%CategoryName%'
        and count("like".likeableid) > 0
    order by count("like".likeableid) desc;

Due to the many-to-many relationship between category and webinar I've decided to join all categories for every webinar into a comma-separated value by using string_agg. This way I'll be able to perform the search by category by using ilike %search_term%.

In the like table the likeabletype must be equal to webinar and the likeableid filed is the id of an entity on which the like is made. So, in my case, when querying the like table I need to use likeabletype='webinar' and likeableid = webinar.id conditions.

The problem is that is gives me incorrect likes_count results (I guess it's due to multiple joins that duplicate many rows).

However using count(distinct "like".likeableid) doesn't help as it just gives me 1 for every row.

---

What should I change in my query in order to get correct result from count() of likes?

question from:https://stackoverflow.com/questions/65938110/how-to-correctly-use-count-when-multiple-joins-are-used

Answer 1

What I need to achieve is find the most popular webinars (by likes number) of a specific category.

You can aggregate the likes in a subquery and just filter on the categories:

select w.id, w.name as "webinar", w.publishat, num_likes
from webinar w join
     (select l.likableid, count(*) as num_likes
      from "like" l
      where l.likeabletype = 'webinar'
      group by l.likeableid
     ) l
     on w.id = l.likeableid join
     webinarcategorymapping wcm
     on wcm.webinarid = w.id join
     category c
     on c.id = wcm.categoryid
where c.name = ?
order by num_likes desc;