phpmysqlcodeigniteractiverecordaggregate-functions

SELECT parent table columns and COUNT() JOINed rows after GROUPing with CodeIgniter active record

I have 2 tables with next construction & data:

Table 1 - events:

id  name    about          owner_id
1   Event1  <description>  2
2   Event2  <description>  1
3   Event3  <description>  2
4   Event4  <description>  2

And table 2 - event_follows

id   event_id   user_id
1    1          2
2    4          2

I need to get all events for current user (i.e. user with id 2) and join count of followers for this event. Now I'm using this code:

$this->db
    ->select('events.*, COUNT(event_follows.id) as followers')
    ->from('events')
    ->where('events.owner_id', $id); // $id - equals 2

$this->db
    ->join('event_follows', 'event_follows.event_id = events.id', 'left')
    ->group_by('event_follows.event_id');

All seems to be ok, but there is a problem. This code returns only 2 events of 3 - events with id 1 & 4. As I understand, this happens because there is no followers for event 3.

So how to correctly fetch all events and get count of it's followers, including those that have 0 followers?

Solution

  • In order to get all rows from the events table you need to group by events.id as opposed to event_follows.event_id

    $this->db->select('events.*, COUNT(event_follows.id) as followers')
        ->from('events')
        ->where('events.user_id', $id); // $id - equals 2

$this->db->join('event_follows', 'event_follows.event_id = events.id', 'left')
        ->group_by('events.id'); //change to events.id