Yii2 using where AND and OR grouping with looping

Question

I have a keyword "example query" and I like to query like this

SELECT * FROM project WHERE (FLAG=1 and STATUS=1) and (NAMA_PROJECT like '%example query%' or DESCRIPTION_PROJECT like '%example query%' or NAMA_PROJECT like '%example%' or NAMA_PROJECT like '%query%') ORDER BY WAKTU_POST;

I already try this but it give me the query I didn't want.

$query=Project::find()->where(['FLAG'=>1,'STATUS'=>1])
->andFilterWhere(['like','NAMA_PROJECT',$q])
->andFilterWhere(['like','DESCRIPTION_PROJECT',$q]);
$words=\yii\helpers\BaseStringHelper::explode($q,$delimiter=' ');
foreach($words as $word){
    $query->orWhere(['like','NAMA_PROJECT',$word]);
}

Is it possible to have a loop inside where condition?

score 3 · Accepted Answer · answered Feb 08 '17 at 13:17

3

$words = \yii\helpers\BaseStringHelper::explode($q,$delimiter=' ');

$condition[] = 'OR';
$condition[] = ['like','NAMA_PROJECT',$q];
$condition[] = ['like','DESCRIPTION_PROJECT',$q];
foreach($words as $word){
    $condition[] = ['like','NAMA_PROJECT',$word];
}

$query = Project::find()
    ->where(['FLAG' => 1, 'STATUS' => 1])
    ->andWhere($condition)
    ->orderBy(['WAKTU_POST' => SORT_DESC])
    ->all();

answered Feb 08 '17 at 13:17

Insane Skull

9,220
9
44
63

This is the query that I want. But it give me this error if I don't delete '->all()' `The "query" property must be an instance of a class that implements the QueryInterface e.g. yii\db\Query or its subclasses`. Do you know why? – GenkaiG Feb 09 '17 at 02:21
@GenkaiG using this query in searchModel or dataProvider? – Insane Skull Feb 09 '17 at 06:07
it for dataProvider – GenkaiG Feb 09 '17 at 06:47
@GenkaiG Then `all()` not required there. Data Provider require query not query result. – Insane Skull Feb 09 '17 at 07:01

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

You can try Like this

Project::find()
        ->where("(FLAG=1 and STATUS=1) AND 
     ((NAMA_PROJECT like '%example query%' OR DESCRIPTION_PROJECT like '%example query%' OR NAMA_PROJECT like '%example%' or NAMA_PROJECT like '%query%')")
->all();

For Detail Information check this answers

edited May 23 '17 at 12:24

Community

1
1

answered Feb 08 '17 at 09:53

Amitesh Kumar

3,051
1
26
42

that works but not I really want. I want to dynamically add OR condition as much the words from the keyword – GenkaiG Feb 09 '17 at 02:02
then try to use loop for that and concat OR and AND . – Amitesh Kumar Feb 09 '17 at 11:57

Yii2 using where AND and OR grouping with looping

2 Answers2