CodeIgniter: counting results from a join table

Question

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

Let's say I have these two tables:

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

Not. I want to take the last three blog entries and count the number of blog comments, all in one query. I tried something like this, but it does not 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?

Answer 1

Got!:)

 $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'); 

Answer 2

Yes it's the right way

 $this->db->where('p.status','Active'); $this->db->where('p.admin_status','Active'); $this->db->select("p.id,p.date,p.user_id,p.title,p.contents,p.category_id,p.status,p.admin_status,u.username,i.mediaid,COUNT(c.id) as comments")->from('posts as p')->limit($num,$start)->order_by($order,$format); $this->db->join('users u','u.id=p.user_id','left'); $this->db->join('user_meta m', 'm.user_id = p.id','left'); $this->db->join('user_profile_image i', 'i.userid = p.id','left'); $this->db->join('comments c', 'c.postid = p.id','left'); return $this->db->get()->result();