CakePHP - Generated query not working correctly with SQL Server

Question

I have a CakePHP app that is being moved to Sql Server from MySql. There is one query that does not seem to transfer correctly:

$this->Model->find('all', array(
        'conditions' => array(
            'Model.column' => array(1, 2, 3)
        )
    )
);

When I use this syntax with mysql, it seems to 'unpack' the array correctly, and the query generated is something like

"...WHERE 'Model.column' IN (1, 2, 3)..."

When I use sql server, the query generated is

"...WHERE 'Model.column' IN 'Array'"...

which obviously generates an error. I posted this question on the CakePHP Google Group yesterday, but have not received a reponse, so I thought I would try SO. If anyone has any ideas/suggestions I would appreciate it.

Answer 1

The code that generates this is in dbo_source.php (function conditionKeysToString) and while specific database drivers can override this, I have never seen that done.

I have 1.2.5, 1.2.6 and 1.3.0-RC1 on my system and they all append ' IN (' and nothing overides that function. There is no instance of just appending ' IN ' and then deciding if it is an array or scalar value. The word Array is what happens when an array variable is evaluated in a string context. EG: php -r '$a = array(1,2,3); echo $a;' will output Array.

Check the cake/VERSION.txt file in both. If they are different, back up the cake directory on the SQL Server instance and replace it with the one from MySQL.

If they are the same try going to the cake/libs/model/datasources directory. Hopefully you have Unix or Linux because "grep -r ' IN ' *" will help you out immensely. Otherwise compare dbo_soures.php and dbo/dbo_mssql.php between both installs of cake and see if there are any differences in conditionKeysToString.

Ultimately I would upgrade to 1.2.6 just so that you get any and all fixes.

Answer 2

Try passing that condition as a string:

$this->Model->find('all', array(
        'conditions' => array(
            'Model.column in (1, 2, 3)'
        )
    )
);

Answer 3

Try it like this:

$this->Model->find('all', array(
        'conditions' => array(
            'Model.column' => array(
                'OR' => array(1, 2, 3)
            )
        )
    )
);