Yii2 - Generate ActiveRecord Query with AND + Multiple OR

Question

I am trying to dynamically generate a query which will allow searching conditionally in the following manner

SELECT * FROM model WHERE col1=val1 AND (col2 LIKE '%val2%' OR col3 LIKE '%val2%');

or

SELECT * FROM model WHERE col2 LIKE '%val2%' OR col3 LIKE '%val2%';

depending on an initial condition and where there are n x coln values in an array.

At the moment I have something along these lines.

if ($condition1) {
   $query = Model::find()->where(['col1' => $val]);
} else {
   $query = Model::find();
}

foreach ($cols as $col) {
   $query->andFilterWhere(['or',['like', $col, $value]]);
}

$dataProvider = new ActiveDataProvider(['query' => $query]);

but, when $condition1 is true, that gives an underlying query structure of

SELECT * FROM model WHERE col1=val1 AND col2 LIKE '%val2%' AND col3 LIKE '%val2%';

or when $condition1 is false,

SELECT * FROM model WHERE col2 LIKE '%val2%' AND col3 LIKE '%val2%';

which when val2 is populated is too restrictive and wont return anything.

and using $query->orFilterWherewhen $condition1 == true gives an underlying structure of

SELECT * FROM model WHERE col1=val1 OR col2 LIKE '%val2%' OR col3 LIKE '%val2%';

or when !$condition1

SELECT * FROM model WHERE col2 LIKE '%val2%' OR col3 LIKE '%val2%';

which when val2 will return is too permissive and will return results that aren't desired.

How can I iterate over the array of extra columns and append them as coln LIKE $value clauses to the SQL statement?

soju · Accepted Answer · 2015-09-23 12:34:59Z

3

You should simply try this :

$condition = ['or'];
foreach ($cols as $col) {
    $condition[] = ['like', $col, $value];
}
$query->andFilterWhere($condition);

answered Sep 23, 2015 at 12:34

soju

25.4k3 gold badges70 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Yii2 - Generate ActiveRecord Query with AND + Multiple OR

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related