How to get count of another table in a left join
This edited version shows rows with no comments:
SELECT post.id, post.title, user.id AS uid, username, count(post_comments.comment_id) as comment_count
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
LEFT JOIN post_comments ON post_comments.post_id = post.id
GROUP BY post.id
ORDER BY post_date DESC
For example:
+----+------------+------+----------+---------------+
| id | title | uid | username | comment_count |
+----+------------+------+----------+---------------+
| 3 | post-name3 | 2 | user2 | 0 |
| 1 | post-name1 | 1 | user1 | 3 |
| 2 | post-name2 | 1 | user1 | 1 |
+----+------------+------+----------+---------------+
3 rows in set (0.01 sec)
SELECT post.id, post.title, user.id AS uid, username, COALESCE(x.cnt,0) AS comment_count
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
LEFT OUTER JOIN (SELECT post_id, count(*) cnt FROM post_comments GROUP BY post_id) x ON post.id = x.post_id
ORDER BY post_date DESC
EDIT: made it an outer join in case there aren't any comments
EDIT2: Changed IsNull
to Coalesce