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

Question

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

Answer 1

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

Answer 2

You can use

$this->db->group_by();

otherwise

$this->db->distinct();