Counting results filtered by distinct() or group_by() in Codeigniter

codeigniter, database, php, postgresql

Solution

    $this->db->select('COUNT(id)');
    $this->db->join();
    $this->db->distinct();
    $this->db->group_by();
//...etc ...
    $query = $this->db->get('mytable');

    return count($query->result());

or

    $this->db->join();
    $this->db->distinct();
    $this->db->group_by();
//...etc ...
    $query = $this->db->get('mytable');

    return $query->num_rows();

Problem

I want to count my results of an active record query with CI (using postgreSQL). I am using `count_all_results()`, which works fine for most queries, but not when using `distinct()` or `group_by()` after a join, because `count_all_results()` ignores these functions. Here is a fix vor this, which is not yet implemented in the newsest (2.1.3) stable version. https://github.com/EllisLab/CodeIgniter/commit/b05f506daba5dc954fc8bcae76b4a5f97a7433c1 When I try to implement this fix in the current version myself, there is no additional filtering done. The row count stays the same. Any tips on how to implement this in the current version, or other ways to count results filtered by `distinct()` or `group_by()`?

Original source