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 |
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:
Since not every product has entries in table favorite, use COALESCE() in the outer query to replace resulting NULL values with your defaults.