Query with andWhere comprised of multiple ORs

Yii 2.0 General Discussions
1

I’m trying to build a query which is to be used to populate a dropDownList form field.

I’m looking to build the following SQL statement

...
WHERE
...
AND ([DriverItems].[ItemName] LIKE '%Mini%'
	OR [DriverItems].[ItemName] LIKE '%Day Rate%' 
	OR [DriverItems].[ItemName] LIKE '%Front%' 
	OR [DriverItems].[ItemName] LIKE '%Rear%')
...

So my question is how does one build multiple OR statements and then make apply them as an AND element to a query in Yii2?

I currently have the following and need to add the extra andWhere with those multiple ORs (shown above) and not sure how to proceed exactly

$query = DriverItems::find()
                                ->distinct()
                                ->select(['DriverItems.*'])
                                ->where(['=', 'DriverItems.Active', 1])
                                ->orderBy([DriverItems.ItemName] => SORT_ASC);

is this the way to do it, or am I better to simply use RAW SQL? Is using RAW SQL a bad practice?

Thank you for your help!

2

Yes it is.

From guide: https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#query-builder

Compared to writing raw SQL statements, using query builder will help you write more readable SQL-related code and generate more secure SQL statements.

Now, on your statement:

You can use ‘operator format’ https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#operator-format

[…] For example, ['and', 'type=1', ['or', 'id=1', 'id=2']] will generate type=1 AND (id=1 OR id=2)

1 Like
3

I think I finally got it functional using ‘or like’ (I hadn’t seen mention of it before).

$criteria = ['Mini', 'Day Rate', 'Front', 'Rear'];
->andWhere(['or like', 'lst_pilot_car_items.PilotCarItem', $criteria])

Still testing, but I believe it is good.

1 Like
4

And the only small issue – is there really a need for introducing an extra variable?

Could the above look like:

->andWhere(['or like', 'lst_pilot_car_items.PilotCarItem', [
    'Mini', 'Day Rate', 'Front', 'Rear'
]])

At least that is what my PhpStorm would surely suggest! :]

1 Like
5

In this specific case, there is some logic to build the criteria based on certain other variables, so yes, the extra variable is required.

1 Like
6

Thanks, but that was more like a joke. It is your code and your deal of course! :]