John Davidson

Message:

I'm trying to reuse query for counting total rows before and after I set limit in CI3, I use caching before.

How to do it in CI4 ?

here is my query

    $sql = $this->db->start_cache()

        ->select(<<some_items>>)

        ->from(<<items_table>>)

        ->where(<<condition>>)

        ->group_by(<<group_by_condition>>)

        ->order_by(<<order_by_condition>>)

        ->stop_cache();



    $total    = $sql->get()->num_rows();



    $sql= $this->db->limit(10, 10)->get();



    $data = $sql->result_array();

    $total_per_limit    = $sql->num_rows();



    $this->db->flush_cache();

---

My Answer:
In CodeIgniter 4, you can achieve the same functionality using the Query Builder class. Here's how you can upgrade your query caching code from CI3 to CI4:

php
$query = $this->db->table('items_table')
->select('some_items')
->where('condition')
->groupBy('group_by_condition')
->orderBy('order_by_condition');

// Count total rows
$total = $query->countAllResults();

// Get data with limit
$data = $query->limit(10, 10)
->get()
->getResultArray();

// Count total rows per limit
$total_per_limit = count($data);

// Flush cache
$this->db->resetQuery();



In CodeIgniter 4, the Query Builder class provides a fluent interface for building SQL queries. You can chain methods to construct your query and then execute it using `get()`, `getResult()`, or other methods. The `countAllResults()` method can be used to count the total number of rows matching the query criteria.

After executing the query, you can get the result data using `getResultArray()` or other methods. Remember to reset the query using `resetQuery()` to clear any cached query data.

This code snippet should help you upgrade your query caching code from CI3 to CI4 and reuse the CodeIgniter 4 Active Record features.