Query builder: Write 'condition 1' AND ('condition 2' AND 'condition 3')

Yii 2.0 General Discussions
1

I want to make a query similar to this:

SELECT * FROM ' Table 1' WHERE 'condition 1' AND ('condition 2' AND 'condition 3');

Where I want to filter results that apply to ‘condition 1’ and BOTH ‘condition 2’ and ‘condition 3’ at the same time, I did

Table::find()->where(['condition 1'])->andWhere(['and', 'condition 2', 'condition 3']);

But it seems to be actually doing something like this:

SELECT * FROM 'Table 1' WHERE 'condition 1' AND 'condition 2' AND 'condition 3';
2

It does exactly what you want. Adding parenthesis for AND like that does not change anything.

3

I think I didn’t explain it well, what’s happening is, this query is getting rows which applies to ‘condition 3’ but don’t apply to ‘condition 2’, and I only need rows that apply to both ‘condition 2’ and ‘condition 3’.

It’s like if I could turn ‘condition 2’ and ‘condition 3’ to being a single condition.

4

Hm, I need a concrete example what is behind “conditions” placeholders here to understand.

5

‘condition 2’ can be translated to:

WHERE 'name' NOT LIKE '%Refund%' ;

And ‘condition 3’:

WHERE 'paymentOption' = 'other';

For some context, I don’t want to bring rows where name field has the word “Refund” AND paymentOption is “other”. What’s happening is, the query is not bringing rows that don’t have “Refund” in name and have different values in paymentOption. It only brings rows that don’t have “Refund” in name and have “other” in paymentOption.

6

Still confused. Please write here the description of that query like “I want all rows that are this and that”.

7

I want all rows except the ones that contains “Refund” in name field AND have “other” in paymentOption field. This means that I don’t want rows which fills BOTH of these conditions. And it is currently not bringing rows that have a different value in paymentOption, only rows that have “others” in paymentOption.

8 
Table::find()->andWhere([
    'and',
    ['not like', 'name', 'Refund'],
    ['!=', 'paymentOption', 'other'],
]);
1 Like
9

I guess this could work (not tried)

Table::find()
	->where(['condition 1'])
	->andWhere(
		['not', 
			['and',
			    ['like', 'name', 'Refund'],
				['==', 'paymentOption', 'other'],
			]
		]
	);
10

Is the title of the post really what you mean?

I guess you are trying to get

'condition 1' AND ('condition 2' OR 'condition 3')

Or, as @tri suggests

'condition 1' AND NOT ('condition 2' AND 'condition 3')