Help with a query

[eluser]sophistry[/eluser]
ok, well, that's more pertinent information you left out of the first description of the issue. ;-)

if it's only ten posts, then you certainly have ten post_ids right? just run that query ten times... if you do proper db optimization (such as building indexes) then it shouldn't be any more performance nabbing than a complex join query.

in any case, did you look at any of the sql commands i posted above? GROUP_CONCAT will help trim out some of those 6 extra records you mention in your first post.

cheers.

[eluser]garrettheel[/eluser]
Thanks for your help but the guys at PHPfreaks ended up helping with the solution I wanted. This way it's all done easily in one query.

Code:

$this->db->select('blog_posts.id,blog_posts.user_id, blog_posts.title, blog_posts.body, blog_posts.created, blog_posts.modified, users.username AS author,
(SELECT COUNT(id) FROM blog_comments WHERE blog_comments.post_id = blog_posts.id) AS comments'
,FALSE);

[eluser]darkhouse[/eluser]
I think all you're missing is the GROUP BY statement.

When you use an aggregate function like COUNT, you need to GROUP your results BY a common field. You mentioned that if you have 6 comments in the first blog post, you get 6 rows of that blog post... this is because you're joining the comments table without grouping anything. If you group the comments by blog_posts.id then it should give you exactly what you want. I've written it for you and added a total_comments column at the end:

Code:

$this->db->select('blog_posts.id,blog_posts.user_id, blog_posts.title, blog_posts.body, blog_posts.created, blog_posts.modified, users.username AS author, COUNT(blog_comments.id) AS total_comments');
$this->db->limit($num);
$this->db->join('users', 'users.id = blog_posts.user_id', 'left');
$this->db->join('blog_comments', 'blog_comments.post_id = blog_posts.id', 'left');
$this->db->group_by('blog_posts.id');
$this->db->order_by('blog_posts.created', 'DESC');
$query = $this->db->get('blog_posts');

[eluser]sophistry[/eluser]
ok, that's great!

a sub-select does the trick if your db supports it. :-) happy that you were probing other avenues instead of just waiting for your answer to magically appear here on boxing day!

BTW, i'd love to see if darkhouse's query would work for you. even though you found something that works, if you don't mind testing that (non-sub-select) solution i'd be curious to see any feedback.

cheers.

[eluser]garrettheel[/eluser]
darkhorse's technique works perfectly too i'll have to do some more research into the group by statement now