Codeigniter 4 Query Builder loses SELECT and WHERE condition while executing once more

Question

I am using Codeigniter 4 Query Builder. Following is code to retrieve data from the database

    public function get_data()
    {
        $where = [
            'username' => 'admin'
        ];
        $this->_builder = $this->_db->table('pf_user_master s');
        $this->_builder->join('pf_role_master r', 'r.role_id = s.role_id');
        $this->_builder->select('username, first_name, last_name, mobile, email, r.role_name, s.status');
        if (is_array($where) && !empty($where)) {
            $this->_builder->where($where);
        }
        $first= $this->_builder->get()->getResultArray();
        print_r($first);
        $second= $this->_builder->get()->getResultArray();
        print_r($second);
        exit;
    }

I am getting the following output:

In variable $first I am getting output as expected

Array
(
    [0] => Array
        (
            [username] => admin
            [first_name] => Fisrt
            [last_name] => Last
            [mobile] => 
            [email] => [email protected]
            [role_name] => Admin
            [status] => 1
        )

)

But in variable $second it Query Builder loses SELECT, WHERE condition and also JOIN. And prints the output as follows:

Array
(
    [0] => Array
        (
            [user_id] => 1
            [username] => admin
            [password] => 21232f297a57a5a743894a0e4a801fc3
            [first_name] => First
            [last_name] => Last
            [mobile] => 
            [email] => [email protected]
            [role_id] => 1
            [status] => 1
            [created_by] => 
            [created_date] => 2020-09-08 19:30:52
            [updated_by] => 
            [updated_date] => 2020-09-08 19:32:42
        )

    [1] => Array
        (
            [user_id] => 2
            [username] => superadmin
            [password] => 21232f297a57a5a743894a0e4a801fc3
            [first_name] => NewFirst
            [last_name] => NewLast
            [mobile] => 
            [email] => [email protected]
            [role_id] => 1
            [status] => 1
            [created_by] => 
            [created_date] => 2020-09-08 21:51:42
            [updated_by] => 
            [updated_date] => 
        )

)

Answer 1

As mentioned by TimBrownlaw I changed $first= $this->_builder->get()->getResultArray(); to $first= $this->_builder->get(NULL, 0 , false)->getResultArray(); and it worked for me as it didn't reset the query as third parameter of get() is for resetting the query

Answer 2

I've not tested this and it comes from reading the documentation only.

In the CodeIgniter documentation get() has the following parameters

get([$limit = NULL[, $offset = NULL[, $reset = TRUE]]]])

Reference: https://codeigniter.com/user_guide/database/query_builder.html#get

If you were to use

$first= $this->_builder->get(NULL,NULL,FALSE)->getResultArray();

Then your code would become:

public function get_data()
{
    $where = [
        'username' => 'admin'
    ];
    $this->_builder = $this->_db->table('pf_user_master s');
    $this->_builder->join('pf_role_master r', 'r.role_id = s.role_id');
    $this->_builder->select('username, first_name, last_name, mobile, email, r.role_name, s.status');
    if (is_array($where) && !empty($where)) {
        $this->_builder->where($where);
    }
    $first= $this->_builder->get(NULL,NULL,FALSE)->getResultArray();
    print_r($first);
    $second= $this->_builder->get()->getResultArray();
    print_r($second);
    exit;
}

Of course if you were to perform this a 3rd time, the 2nd instance should cause a reset.

The Code is the Documentation ( in most cases ) so we have

In CodeIgniter 4.04 - /system/Database/BaseBuilder.php - Line 1824 The code is

/**
     * Get
     *
     * Compiles the select statement based on the other functions called
     * and runs the query
     *
     * @param integer $limit  The limit clause
     * @param integer $offset The offset clause
     * @param boolean $reset  Are we want to clear query builder values?
     *
     * @return ResultInterface
     */
    public function get(int $limit = null, int $offset = 0, bool $reset = true)
    {
        if (! is_null($limit))
        {
            $this->limit($limit, $offset);
        }

        $result = $this->testMode
            ? $this->getCompiledSelect($reset)
            : $this->db->query($this->compileSelect(), $this->binds, false);

        if ($reset === true)
        {
            $this->resetSelect();

            // Clear our binds so we don't eat up memory
            $this->binds = [];
        }

        return $result;
    }

So the default for $reset, will "clear" what you have observed.