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
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
GROUP BY lc.collection_id
ORDER BY cn.published_date DESC
LIMIT 0, 5
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