addInCondition errors

Yii 1.1 General Discussion
1

I receive the following error when I use ‘addInCondition’ to the CDbCriteria object:

This only occurs when I already have a condition already specified; it works fine if I do not add any additional conditions. Does anyone else have this issue? Has anyone gotten around it.

2

Why not share your code with us so we can see it? :)

3

Reduced test case will be helpful.

4

Here is my code:


$criteria = new CDbCriteria;

            $criteria->limit = 1;

            $criteria->order = 't.id DESC';

            $criteria->condition = 'active=:active';


            $criteria->addNotInCondition('id', array(1,2));

                

            $criteria->params = array(

                ':active' => 1,

            );




            $result = MyModel::model()->find($criteria);
5

Hm, I don’t see any obvious mistake. Can you show the resulting SQL query?

6

Trying to reproduce the error I get this in the stack trace:




Error in querying SQL: SELECT * FROM `MyModel` `t` WHERE (active=:active) AND

(id NOT IN (:ycp0, :ycp1)) ORDER BY t.id DESC LIMIT 1. Bind with parameter

:active=1

I used the code provided by chazclark. For some reasons Yii replaces the actual values given in the array (array(1,2)) with tokens (:ycp0, :ycp1). If we add these tokens to params everything works as expected. Here:




    $criteria = new CDbCriteria;

        $criteria->order = 't.id DESC';

        $criteria->condition = 'active=:active';


        $criteria->addNotInCondition('id', array(1,2));

                

        $criteria->params = array(

            ':active' => 1,

            ':ycp0' => 1,

            ':ycp1' => 2

        );




        $result = MyModel::model()->find($criteria);

I’m not sure if this is by design.

Btw, chazclark, you don’t need to use $criteria->limit => 1, as Yii already does this for you when using find() method.

7

chazclark

With $criteria->params = array(’:active’ => 1); you are replacing existing params for NOT IN. That’s why you are getting this error.

Try using compare() method.

8

Thanks for looking at this. I attempted using the compare() method yet I am getting the same error. Please let me know if I’m using it incorrectly as I’ve never used it before:




criteria = new CDbCriteria;

            $criteria->limit = 1;

            $criteria->order = 't.id DESC';

            $criteria->condition = 'active=:active';


            $criteria->compare('id', array(1,2));

                

            $criteria->params = array(

                ':active' => 1,

            );




            $result = MyModel::model()->find($criteria);

Equ, thanks for your suggestion; however, although it does work as suggested in this example, the array will be populated with a dynamic array so I’m not sure that it will work in my use case.

9

Here’s the query for my last post:




Querying SQL: SELECT * FROM `dba`.`MyModel` `t` WHERE (active=:active) AND (id IN (:ycp0, :ycp1)) ORDER BY t.id DESC LIMIT 1. Bind with parameter :active=1 in [...]MyModelController.php (104) in [...]index.php (12)
10

SOLVED:

Turns out that if I change the order and place the params property before the “addInCondition” or “compare()” method, I’ll get the correct query run. Here’s the code change I made for anyone else with the same problem:




$criteria = new CDbCriteria;

        $criteria->order = 't.id DESC';

        $criteria->condition = 'active=:active';

        $criteria->params = array(

            ':active' => 1

        );

        $criteria->addNotInCondition('id', array(1,2));

        $result = MyModel::model()->find($criteria);

Thanks for all your help in this.

11

I meant using compare like this:




$criteria = new CDbCriteria;

$criteria->order = 't.id DESC';

$criteria->compare('active', 1);

$criteria->addNotInCondition('id', array(1,2));

                

$result = MyModel::model()->find($criteria);
12

Is there an addParams method (like addCondition) that you can call to add parameters after calling addInCondition or addNotInCondition?

13

$criteria->compare will add both a part of condition and param. Generally there is no need to bind params manually but if you still want to do so it’s easy to do with native PHP array syntax:




$criteria->params[':active'] = 1;
14

Ok I see that works. Thanks samdark.

1 Like
15

I just struggled with this for an hour before figuring out the same (unfortunately I found your post after I figured it out, go figured).

This behavior really needs to be doc’d. :(

16

Caught me as well with a simple addCondition with condition set before params - I have now replaced this with compare and it works fine.

17

Oh my God, I agree this weird behavior should be documented.

1 Like
18

I think this is bug. It was closed http://code.google.com/p/yii/issues/detail?id=1478 but not fixed