Why is COUNT() on one of several LEFT JOINed tables returning NULL using CodeIgniter's query builder?

I need to select some data from a table and count related images from another table.

From my model file:

$this->rci_db->select (" 
        $this->tbl_register.id,
        $this->tbl_register.cor,
        DATE_FORMAT($this->tbl_register.registerdate, '%d/%m/%Y') AS registerdate,
        $this->tbl_registrations.registration,
        $this->tbl_aircrafts.cn,
        $this->tbl_aircrafts.built,
        $this->tbl_manufacturers.manufacturer, 
        $this->tbl_models.type AS model,
        COUNT($this->tbl_images.imgid) AS count
        ");
    $this->rci_db->from("$this->tbl_register");
    $this->rci_db->join("$this->tbl_registrations", "$this->tbl_registrations.rid = $this->tbl_register.rid", 'left');
    $this->rci_db->join("$this->tbl_aircrafts", "$this->tbl_register.aid = $this->tbl_aircrafts.aid", 'left');
    $this->rci_db->join("$this->tbl_manufacturers", "$this->tbl_manufacturers.mid = $this->tbl_aircrafts.mid", 'left');
    $this->rci_db->join("$this->tbl_models", "$this->tbl_models.tid = $this->tbl_aircrafts.tid", 'left');
    $this->rci_db->join("$this->tbl_images", "$this->tbl_register.id = $this->tbl_images.id", 'left');
  
    $this->rci_db->where("$this->tbl_register.rid", $rid);
 
    $query = $this->rci_db->get();

    if ($query->num_rows() > 0)
    {
        return $query->result();
    }
    return false;

Why is COUNT() on one of several LEFT JOINed tables returning NULL using CodeIgniter's query builder?

Other data are returned correctly.

That count is in another table, so you must relate those somehow in your query. Instead of joining in this case it might be easy to just use a sub select.

Something like this in your select:

$this->rci_db->select (" 
        $this->tbl_register.id,
        $this->tbl_register.cor,
        DATE_FORMAT($this->tbl_register.registerdate, '%d/%m/%Y') AS registerdate,
        $this->tbl_registrations.registration,
        $this->tbl_aircrafts.cn,
        $this->tbl_aircrafts.built,
        $this->tbl_manufacturers.manufacturer, 
        $this->tbl_models.type AS model,
        (SELECT COUNT($this->tbl_images.imgid) FROM $this->tbl_images WHERE $this->tbl_register.id = $this->tbl_images.id) AS count
        ");