Count the rows from a joined table with CodeIgniter's query builder

Question

I know how to join two tables, but I'd like to actually count the number of rows in the joined/second table with CodeIgniter's query builder methods.

Say, I have these two tables:

blog     comments
-------  ----------
id       id
title    blog_entry_id
content  comment

I want to take the three last blog entries and count the number of comments per blog entry, all in one query. I unsuccessfully tried using count_all_results() like this:

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

The above script produces an unintended query and incorrectly returns a numeric result not larger than 3:

SELECT COUNT(*) AS `numrows`
FROM (
    SELECT *
    FROM `blog`, `comments`
    JOIN `comments` ON `blog_entry_id` = `blog`.`id`
    GROUP BY `blog_entry_id`
    LIMIT 3
)

What should I do? What am I doing wrong?

Frankie · Accepted Answer · 2011-04-17 19:01:44Z

11

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

edited Apr 17, 2011 at 19:01

Frankie

25.2k10 gold badges84 silver badges127 bronze badges

answered Apr 17, 2011 at 18:37

Kriem

8,71317 gold badges76 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kumar Saurabh · Accepted Answer · 2015-12-02 11:40:40Z

0

Yes, this is correct 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();

edited Dec 2, 2015 at 11:40

Kumar Saurabh

2,3035 gold badges31 silver badges44 bronze badges

answered Dec 2, 2015 at 10:58

Kail D

1277 bronze badges

1 Comment

mickmackusa Over a year ago

Have you answered a different question (with different context) then asserted that your answer is the correct answer without offering any explanation as to why it is "correct" for this asked question?

mickmackusa · Accepted Answer · 2025-08-27 20:09:43Z

If you want to permit zero counts on the SELECTed records, you'll need to use a LEFT JOIN. SQLize Demo

SELECT 
    b.*,
    COUNT(c.id) comment_count
FROM blog b
LEFT JOIN comments c ON c.blog_id = b.id
GROUP BY b.id
ORDER BY b.id DESC
LIMIT 3

The CodeIgniter query builder script:

return $this->db
    ->select('blog.*, COUNT(c.id) comment_count')
    ->join('comments c', 'c.blog_id = b.id', 'left')
    ->group_by('b.id')
    ->order_by('b.id', 'desc')
    ->get('blog b', 3)
    ->result();

Alternatively, using a subquery to pre-limit the blog table will enjoy better performance because JOINing all comments to all blogs is avoided.

SELECT 
    b.*,
    COUNT(c.id) comment_count
FROM (
    SELECT * 
    FROM blog 
    ORDER BY id DESC 
    LIMIT 3
) b
LEFT JOIN comments c ON c.blog_id = b.id
GROUP BY b.id
ORDER BY b.id DESC

The CodeIgniter script is a little more complex because the subquery will be precompiled and then injected into the parent query.

$sub = $this->db
    ->order_by('id', 'desc')
    ->limit(3)
    ->get_compiled_select('blog');

return $this->db
    ->select('b.*, COUNT(c.id) comment_count')
    ->join('comments c', 'c.blog_id = b.id', 'left')
    ->group_by('b.id')
    ->order_by('b.id', 'desc')
    ->get("($sub) b");

Collectives™ on Stack Overflow

Count the rows from a joined table with CodeIgniter's query builder

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related