Bdtjtk

I have composed a query using Codeigniter's Query Builder class. The query utilizes aliases and the having method. When I call the count_all_results method on this query, an exception occurs. Inspecting the log, I see that the query has stripped out the 'having' clauses. Is there a way to keep these clauses in while calling count_all_results? Thanks for your help.

EDIT: I first believed the problem was knowledge-based and not code-based and so did not share the code, but here it is. Please let me know if more is needed.

Here's the call on the model in the controller.

$where_array = array(

    $parent_key.' is not NULL' => null

);

$search_post = $request_data['search'];

if (isset($request_data['filter'])) {

    $filter_array = $request_data['filter'];

    foreach ($filter_array as $filter_pair) {

        if (isset($filter_pair['escape'])) {

            $where_array[$filter_pair['filterBy']] = null;

        } else {

            if ($filter_pair['filterBy'] == 'table3_id') {

            $where_array['table3.'.$filter_pair['filterBy']] = isset($filter_pair['filterId']) ?

                $filter_pair['filterId'] : null;

            } else {

                $where_array[$table.'.'.$filter_pair['filterBy']] = isset($filter_pair['filterId']) ?

                    $filter_pair['filterId'] : null;

            }

        }

    }

}

$like_array = array();

foreach ($request_data['columns'] as $key => $column) {

    if (!empty($column['search']['value'])) {

        $like_array[$column['data']] = $column['search']['value'];

    }

}

$totalFiltered = $this->$model_name->modelSearchCount($search, $where_array, $like_array);

Here's the model methods.

public function modelSearchCount($search, $where_array = null, $like_array = null)

{

    $this->joinLookups(null, $search);

    if ($where_array) {

        $this->db->where($where_array);

    }

    if ($like_array) {

        foreach($like_array as $key => $value) {

            $this->db->having($key." LIKE '%". $value. "%'");

        }

    }

    return $this->db->from($this->table)->count_all_results();

}



protected function joinLookups($display_config = null, $search = null)

{

    $select_array = null;

    $join_array = array();

    $search_column_array = $search ? array() : null;

    $i = 'a';



    $config = $display_config ? $display_config : $this->getIndexConfig();

    foreach ($config as $column) {

        if (array_key_exists($column['field'], $this->lookups)) {

            $guest_model_name = $this->lookups[$column['field']];

            $this->load->model($guest_model_name);

            $join_string =$this->table.'.'.$column['field'].'='.$i.'.'.

                $this->$guest_model_name->getKey();

            $guest_display = $this->$guest_model_name->getDisplay();

            if ($search) {

                $search_column_array = $i.'.'.$guest_display;

            }

            $join_array[$this->$guest_model_name->getTable().' as '.$i] = $join_string;

            $select_array = $i.'.'.

                $guest_display;

        } else {

            $select_array = $this->table.'.'.$column['field'];

            if ($search) {

                $search_column_array = $this->table.'.'.$column['field'];

            }

        }

        $i++;

    }

    $select_array = $this->table.'.'.$this->key;

    foreach ($join_array as $key => $value) {

        $this->db->join($key, $value, 'LEFT');

    }

    $this->db->join('table2', $this->table.'.table2_id=table2.table2_id', 'LEFT')

        ->join('table3', 'table2.table3_id=table3.table3_id', 'LEFT')

        ->join('table4', $this->table.'.table4_id=table4_id', 'LEFT')

        ->join('table5', 'table4.table5_id=table5.table5_id', 'LEFT');

    $this->db->select(implode($select_array, ', '));

    if ($search) {

        foreach (explode(' ', $search) as $term) {

            $this->db->group_start();

                $this->db->or_like($this->table.'.'.$this->key, $term);

            foreach ($search_column_array as $search_column) {

                $this->db->or_like($search_column, $term);

            }

            $this->db->group_end();

        }

    }

    $this->db->select('table2_date, '. $this->table.'.table2_id, table4_id, '. 'table5.table5_description');

}

Since count_all_results() will basically run a Select count(*) and not count the rows in your resultset (basically rendering the query useless for your purposes) you may use other Codeigniter methods to get the resultset and the row count.

Try running the query into a variable:

 $query = $this->db->get();

From then, you can do pretty much anything. Besides returning the result with $query->result(); you can get the number of rows into another variable with:

 $rownum = $query->num_rows();

You can then return that into your controller or even just return the $query object and then run the num_rows() method on the controller

To answer this question, count_all_results() transforms the original query by replacing your selects with SELECT COUNT(*) FROM table. the aliased column would not be selected, and the having clause would not recognize the column. This is why count_all_results() does not work with having.