[eluser]jedd[/eluser]
[quote author="demods" date="1261162019"]
The problem is that I also couldn't manage to write the SQL query itself.
[/quote]
Two things.
Show us what you've got so far - it's MUCH easier to work forward from some starting point rather than a blank slate. You'll learn more, as we can point out where you're going wrong or what you're getting stuck on. You'll also find people more willing to help you if it doesn't look like you're asking them to do all your homework for you.
Make it clear up front what question you're asking - it's actually not an AR problem at all, but a plain vanilla SQL one. This is fine - it's not really appropriate to these forums - but trying to disguise it just wastes everyone's time.
Code:
SELECT
category.id AS category_id
category.title AS category_title
post.id AS post_id
post.title AS post_title
post.content AS post_content
COUNT(comment.id) AS comment_count
FROM
categories
LEFT JOIN
posts ON posts.category_id = categories.id
LEFT JOIN
comments ON comments.post_id = posts.id
WHERE
posts.status = 1
AND
comments.status = 1
Btw, mixing plurals with singulars is
really confusing. You should stick with one or the other (and I think you should stick with singular).
Consider this fairly uncomfortable-to-read line:
posts ON posts.category_id = categories.id
Actually, I see I've mixed up some plurals and singulars above because of this. I'll leave that to you to sort out.
Untested code and all that. Don't have any test data to experiment with, so let us know how you go. If you have an actual CI related problem when converting this to AR, let us know.
