Adding 'or' Condition

Yii 1.1 General Discussion
1

Hello everyone!!!

i want to make sql query like this:




SELECT * FROM sometbl WHERE((((col1=val1)OR(col1 IS NULL))AND(col2=val2))OR(col2 IS NULL))

but i want to use the compare() function. can i do it???

Thanks!

2

Try




$criteria->condition ="((((col1=val1)OR(col1 IS NULL))AND(col2=val2))OR(col2 IS NULL))"
3

Hi Ariel, welcome to the forum.

Yes, you can add conditions with ‘OR’ operator using compare() function.

See the reference of CDbCritria::compare() and you will notice the 4th parameter($operator) is what you want.

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#compare-detail

You can specify the operator not only in compare() but also in all of the functions that create the condition clause.

But there’s an important note about the operator. That is, in a single CDbCriteria object the conditions are concatenated in a very simple manner. When you call compare() to add a condition, the operator is applied between the existing conditions as a whole and the new condition to be added.

For example, when you use compare() like this:




1) $criteria->compare('column_a', 'A', false);

2) $criteria->compare('column_b', 'B', false);

3) $criteria->compare('column_c', 'C', false, 'OR');

4) $criteria->compare('column_d', 'D', false);

then Yii will create the conditions in a way like the following:




1) WHERE column_a = 'A'

2) WHERE (column_a = 'A') AND column_b = 'B'

3) WHERE ((column_a = 'A') AND column_b = 'B') OR column_c = 'C'

4) WHERE (((column_a = 'A') AND column_b = 'B') OR column_c = 'C') AND column_d = 'D'

Note that the operator defaults to ‘AND’.

And the operator of the first condition has no practical meaning.

This strategy works very fine when all the operators are either ‘AND’ or ‘OR’. Because there’s no logical difference between the following 2 conditions.




WHERE (((column_a = 'A') AND column_b = 'B') AND column_c = 'C') AND column_d = 'D'

WHERE column_a = 'A' AND column_b = 'B' AND column_c = 'C' AND column_d = 'D'

But as you may notice, when you want to use ‘AND’ and ‘OR’ together, the simple left-to-right concatenating rule may not satisfy your needs. When you want to control the logical concatenation of the conditions, usually you have to use multiple CDbCriteria objects and merge them using mergeWith() function.




$criteria->compare('column_a', 'A', false);

$criteria->compare('column_b', 'B', false);

$criteria2 = new CDbCriteria;

$criteria2->compare('column_c', 'C', false);

$criteria2->compare('column_d', 'D', false);

$criteria->mergeWith($criteria2, 'OR');


The resulting condition is ...

WHERE ((column_a = 'A') AND column_b = 'B') OR ((column_c = 'C') AND column_d = 'D')

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#mergeWith-detail

1 Like
4

That’s a great answer.

5

Great!

6

I loved the way Softark compiled the answer.

He is having a mindset of a great teacher.

7

Wow, thank you my friends. :)

8

Really simple solution

9

Thank you softark for the great answer. This saved my day.

10

Very Helpful answer…!

Thanks softark…