Can query builder method where_in() be used to augment the WHERE clause of CodeIgniter's query() call?

I am experiencing a strange phenomenon while mixing $this->db->where() and $this->db->query().

if (!empty($args) && is_array($args)) {
    foreach ($args as $key => $value) {
        $this->db->where_in($field, $value);
    }
}
$result = $this->db->query('SELECT * FROM ...')->result_array();

While I can understand it is probably better to not mix query builder methods and regular queries, I am experiencing that the WHERE clause I am building up with $this->db->where_in() is affecting subsequent queries (i.e., the clause executes on next query).

Should all my queries be rewritten to either not use query builder, or to use ONLY query builder? Or is there a way to prevent this "clash"? I thought about $this->db->flush_cache(), but I am not using database caching, and using it does not seem to have any effect.

Solution

I would suggest not calling db->where_in in a loop. Consider instead

if (!empty($args) && is_array($args)) 
{
    foreach ($args as $key => $value)
    {
        $values[] = $value;
    }
    $this->db->where_in($field, $values);
}

In truth the loop in not needed if $args is an indexed (as opposed to associative) array. You could simply remove the foreach and use

 $this->db->where_in($field, $args);

You probably shouldn't mix Query Builder (previously known as Active Record) and db->query(). The Query Builder (QB) methods essentially assemble a statement that you would provide to db->query() and then, pretty much literally calls query("The statement that QB built").

IMO, it is almost always easier to type the query statement and use it in $this->db->query("Select * from ...");. Query Builder is great but is often more work that it is worth.

In your example you should probably stick to Query Builder. That can still be very concise. If you're looking for all fields from a single table then db->get will do the job.

$result = $this->db->get('table_name')->result_array();

Typically QB clears all the various elements created that go into the statement when the statement is executed with a get, insert, or update call. You can start fresh at any point with this call.

$this->db->reset_query();