Codeigniter count_all_results with having

Question

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');
}

you need to show some code (at least from your model), show the specific exception you're getting, etc... otherwise it's next to impossible to help you — Javier Larroulet, Jan 04 '19 at 17:47
Please let me know if the provided code is insufficient, thanks. — Enoch, Jan 04 '19 at 18:12
Hi @Enoch it would help to see the whole query built in the model, not just the block where you build the `having` clause. If relevant, parameters passed from the controller would help too. The error messages you get may also help pinpoint the issue. Remember, you are expected to provide a [Minimal, complete and verifiable example](https://stackoverflow.com/help/how-to-ask) — Javier Larroulet, Jan 08 '19 at 12:15
@JavierLarroulet I've included the entire query build as it occurs in the model, plus the lead up to the model call in the controller. Really my question is why count_all_results strips 'having' from its query, and how I can get it not to do that. — Enoch, Feb 07 '19 at 15:35
I found a solution. For me I had a similar problem. However, in one similar query, the "count_all_results" was working just fine, but in the other one it wasn't. Found out that I had to add a "group by" statement and then the problematic query also worked with "count_all_results". In these correct cases the "count_all_results" doesn't replace my SELECT with just "COUNT(*)" but instead does a wrapper query on top of my actual query (my actual query becomes a subquery). — Chique, Jul 23 '20 at 14:07

score 0 · Answer 1 · answered Feb 07 '19 at 16:28

0

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.

answered Feb 07 '19 at 16:28

Enoch

202
1
10

Good find... I still hadn't figured it out. A workaround, using CI's methods, would be to run the query into a variable (e.g. `$result = $this->db->get();`) and then use another method to get the number of result rows (e.g. `$numrows = $result->num_rows();`) ... let me know if this works for you so I can post it like an answer – Javier Larroulet Feb 08 '19 at 13:10
@JavierLarroulet yes your solution seems to be the only way to keep having clauses in the query. Thanks. – Enoch Feb 12 '19 at 21:02

score 0 · Accepted Answer · answered Feb 13 '19 at 03:16

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

Codeigniter count_all_results with having

2 Answers2