Now I know how to join two tables, I'd like to actually count the number of rows in the second table with active records.

Say, I have these two tables:

blog     comments
-------  ----------
id       id
title    blog_id
content  comment

No I want to take the three last blog entries and count the number of comments per blog entry, all in one query. I tried something like this, but it doesn't work:

$this->db->select('*')
         ->from('blog')
         ->order_by('blog.id', 'desc')
         ->limit(3);

$this->db->join('comments', 'blog_entry_id = blog.id')
         ->group_by('blog_entry_id')
         ->count_all_results('comments'); 

What should I do? What am I doing wrong?

Accepted Answer

Got it! :)

$this->db->select('blog.*, COUNT(comments.id) as num_comments')
         ->from('blog')
         ->order_by('blog.id', 'desc')
         ->limit(3);

$this->db->join('comments', 'blog_entry_id = blog.id')
         ->group_by('blog_entry_id');
Written by Kriem
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki