domoindal domoindal - 5 months ago 9
SQL Question

MySQL calculate number of items FROM an INNER relationship

I'm trying to get a set of collections from a table and I can not figure out how I can calculate the number of likes from the the related table 'like_collections' using LEFT JOIN syntax.

As you can see in the query, for each collection_id I need to get the number of users that like such collection.

I know I can merely use

(SELECT COUNT(*) FROM likes_collections WHERE collection_id=cn.id) as n_likes
but want to know if it possible using the below query.

SELECT cn.id,
cn.name,
cn.description,
u.avatar,
u.username,
COUNT(lc.id) as n_likes,
(SELECT COUNT(*) FROM collection_items WHERE collection_id=cn.id) as n_items
FROM collection_names as cn
INNER JOIN users as u ON u.ID=cn.user_id
LEFT JOIN likes_collections as lc ON lc.collection_id=cn.id
WHERE cn.public=1
GROUP BY lc.collection_id
ORDER BY cn.published_date DESC
LIMIT 0, 5

Answer

When you have a LEFT JOIN, you shouldn't use a column in the child table in the GROUP BY. This is because columns from that table will all be NULL when there are no matches for the row in the master table, and it will group all those unmatched rows together. So you should GROUP BY cn.id rather than GROUP BY lc.collection_id.

And instead of the correlated subquery, you can join with a subquery that calculates the number of items grouped by collection ID.

SELECT  cn.id,
        cn.name,
        cn.description,
        u.avatar,
        u.username,
        COUNT(lc.id) as n_likes,
        n_items
FROM collection_names as cn 
INNER JOIN users as u ON u.ID=cn.user_id 
LEFT JOIN likes_collections as lc ON lc.collection_id=cn.id
LEFT JOIN (SELECT collection_id, COUNT(*) AS n_items
           FROM collection_items
           GROUP BY collection_id) AS ci ON ci.collection_id = cn.id
WHERE cn.public=1
GROUP BY cn.id
ORDER BY cn.published_date DESC
LIMIT 0, 5