phpcodeignitercountleft-joinquery-builder

How to COUNT() LEFT JOINed records with CodeIgniter's query builder


I have this query:

return $this->db
    ->select('forumCategories.id, forumCategories.name, forumCategories.order, forumCategories.date_created, COUNT(forumPosts.forumCategory_id) as postCount')
    ->from('forumCategories')
    ->join('forumPosts', 'forumCategories.id = forumPosts.forumCategory_id', 'left')
    ->group_by('forumPosts.forumCategory_id')
    ->order_by('forumCategories.order DESC')
    ->get()
    ->result_array();

The query takes all forum categories and counts the posts for each category. The problem is that it is taking only categories that have posts, plus only one category that doesn't have any posts yet.

Example:

Category 1 | 16 posts
Category 2 | 3 posts
Category 3 | 0 posts
Category 4 | 0 posts
Category 5 | 0 posts
Category 6 | 0 posts

Only category 1, 2 and 3 will be returned. How can I write this query, so it will return all categories?


Solution

  • Try Grouping by with first table column(forumCategories.id) which have value always.

     return $this->db
                ->select('forumCategories.id, forumCategories.name, forumCategories.order, forumCategories.date_created, COUNT(forumPosts.forumCategory_id) as postCount')
                ->from('forumCategories')
                ->join('forumPosts', 'forumCategories.id = forumPosts.forumCategory_id', 'left')
                ->group_by('forumCategories.id')
                ->order_by('forumCategories.order DESC')
                ->get()
                ->result_array();