CODEWITHSUNDEEP
phpactiverecordyii2
Pedro del Sol
Pedro del Sol

Reputation: 2841

Yii2 - Generate ActiveRecord Query with AND + Multiple OR

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?

Upvotes: 3

Views: 631

Answers (1)

soju
soju

Reputation: 25312

You should simply try this :

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

Upvotes: 3

Related Questions