Add on development. Database class not executing correct queries

Question

i'm developing an addon. While developing i'm encountering a strange problem that I don't know how to solve. It's like EE is executing the wrong queries or something.

For example, when I call this function:

$channel_info = ee()->api_channel_structure->get_channel_info(2);

The result is completely off:

echo '<pre>';
echo 'CHANNEL INFO:';
print_r($channel_info->result());
echo '</pre>';

Giving me this information:

CHANNEL INFO:Array
(
    [0] => stdClass Object
        (
            [child_id] => 11
            [order] => 3
        )

    [1] => stdClass Object
        (
            [child_id] => 4
            [order] => 2
        )

    [2] => stdClass Object
        (
            [child_id] => 2
            [order] => 1
        )

    [3] => stdClass Object
        (
            [child_id] => 10
            [order] => 4
        )

)

This is a result from a former executed query like this:

$result = ee()->db->query($sql);
$this->_result_array = $result->result_array();

Do I need to clear the db class or something? I'm not sure what's causing this. Hopefully someone can give me a push in the right direction here? :)

EDIT: Ok, I have solved the problem by adding this line:

ee()->db->_reset_select();

Before calling:

$channel_info = ee()->api_channel_structure->get_channel_info(2);

Still, I'm not sure why I have to call this manually...

Answer 1

If you are in an extension, it's possible that the db class is in the midst of a query. I'll show an example. Let's say this was the core EE code:

ee()->db->select('some_column');

ee()->db->where('some_column', 1);

// your code gets called here, while the active record methods have already been invoked
ee()->extensions->call('some_hook');

$query = ee()->db->get('some_table');

I don't remember which hooks fall into this pattern, but I know I've been bitten by it once or twice. If you find yourself in a scenario like this, here is the solution. Make a copy of the db like this (gist):

$db = ee()->load->database(array(
  'dbdriver' => 'mysql',
  'conn_id' => ee()->db->conn_id,
  'database' => ee()->db->database,
  'dbprefix' => ee()->db->dbprefix,
), TRUE);

and make all your db calls using $db instead of ee()->db;

I might also add that the way you've solved it by doing ee()->db->_reset_select() could interfere with the original query and have unintended consequences. With this method you won't run any chance of interfering with other queries.