Get total count and filtered count from single subquery

Question

product table

product_id	name
1	t-shirt
2	shirt

users table

user_id	name
1	sphinx

favorite table with a UNIQUE constraint on (user_id, product_id):

user_id	product_id
1	2

What is best way to query this table for user_id = 1

favorite_count: how many users added this product in favorite?
isfavorite: did user with user_id = 1 add this product as favorite?

Expected result:

product_id	product_name	isfavorite	favorite_count
1	t-shirt	false	0
2	shirt	true	1

Answer 1

Typically, it's cheapest to aggregate rows in the n-table (favorite in this case) before the join:

SELECT p.*
     , COALESCE(f.is_favorite, false) AS is_favorite
     , COALESCE(f.favorite_count, 0)  AS favorite_count
FROM   product p
LEFT   JOIN (
   SELECT product_id
        , count(*) AS favorite_count            -- total count
        , bool_or(true) FILTER (WHERE user_id = 1) AS is_favorite  -- for given user
   FROM   favorite
   GROUP  BY 1
   ) f USING (product_id);

We don't need the users table for the query.

LEFT JOIN keeps all products in the result, while only adding counts for products that have favorite entries.

About the FILTER clause:

• Aggregate columns with additional (distinct) filters

Since not every product has entries in table favorite, use COALESCE() in the outer query to replace resulting NULL values with your defaults.