CODEWITHSUNDEEP
phpmysqlcodeigniter
Sasha
Sasha

Reputation: 8705

Codeigniter 3 - Join doesn't return all results

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?

Upvotes: 0

Views: 52

Answers (1)

Sanjay Kumar N S
Sanjay Kumar N S

Reputation: 4739

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

Upvotes: 1

Related Questions