Yii2 How to perform where AND or OR condition grouping?

Question

I am new to Yii-2 framework. How can i achieve following query in Yii-2 framework using activeQuery and models.

SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND u.enable_social = 1)

Thanks

score 105 · Accepted Answer · edited Jul 10 '17 at 08:17

105

You can try this:

//SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND u.enable_social = 1)
$model = arname()->find()
       ->andWhere(['user_id'=>[1,5,8]])
       ->andWhere(['or',
           ['status'=>1],
           ['verified'=>1]
       ])
       ->orWhere(['and',
           ['social_account'=>1],
           ['enable_social'=>1]
       ])
       ->all();

edited Jul 10 '17 at 08:17

Rohit Suthar

3,303
1
34
46

answered Jun 17 '17 at 02:01

gouki

1,166
1
8
4

How to use it with `findOne()`? – Moeez Mar 21 '18 at 05:23
@Faisal `->one()` instead of `->all()`. – Boolean_Type Jun 13 '19 at 16:25

score 33 · Answer 2 · answered Jul 22 '15 at 08:37

try this -

$query = (new \yii\db\Query())
            ->select('*')
            ->from('users u')
            ->where(['and',['u.user_id'=>[1,5,8]],['or','u.status=1','u.verified=1']])
            ->orWhere(['u.social_account'=>1,'u.enable_social'=>1]);
    $command = $query->createCommand();
    print_r ($command->sql);die;

more info

score 3 · Answer 3 · answered Jul 22 '15 at 08:26

I assume that you have already knew about database configuration in Yii 2.0, which is basically the same as in Yii 1.0 version.

If you want to use activeQuery, you need to define a ‘USERS’ class first:

<?php
    namespace app\models;
    use yii\db\ActiveRecord;

    class USERS extends ActiveRecord {
        public static function tableName()
        {
            return 'users';
        }
    }
?>

Then when you use it，you can write it as following:

<?
    $usr_data = USERS::find()->  
            ->where("user_id IN(1,5,8) AND (status = 1 OR verified = 1) OR (social_account = 1 AND enable_social = 1)")
            ->all();    
?>

In my opinion, active query provides you a way to separate sql by sub-blocks. But it does not make any sense to apply it when you have such a complicated 'AND OR' WHERE condition..

score 3 · Answer 4 · answered Jul 22 '15 at 14:55

3

U can also do it this way:

$data = model::find()
->andWhere('plz = :plz',[':plz' => 40])
->andWhere('plz = :plz',[':plz' => 40000])
->all();

answered Jul 22 '15 at 14:55

fsn

549
3
11

Equal to `$data = model::find()->andWhere(['plz' => 40])->andWhere(['plz' => 40000])->all();` – Anton Rybalko Nov 01 '18 at 08:29

score 2 · Answer 5 · edited Dec 05 '17 at 21:37

2

With MongoDB:

            $query->andWhere(['and',
                ['$eq', 'status', 1],
                ['$in', 'activity', [1, 2]],
            ]);
            $query->orWhere(['$in', 'yourField', $yourArray]);

Tested. Similar with DBMS.

edited Dec 05 '17 at 21:37

halfer

18,701
13
79
158

answered Nov 23 '16 at 04:23

ThangTD

1,336
14
15

score 2 · Answer 6 · answered Feb 06 '19 at 14:07

Use OR condition at first. For example:

(new \yii\db\Query())
            ->select(['id', 'client', 'ts', 'action'])
            ->from('log_client as log')
            ->orWhere(['action' => 'lock'])
            ->orWhere(['action' => 'rel'])
            ->andWhere(['in', 'client', $IDs])
            ->orderBy(['ts' => SORT_ASC])
            ->all();

It'll be like a "AND...(..OR..)"

score 0 · Answer 7 · answered Jul 22 '15 at 08:30

where() function of ActiveQuery also accepts string as its first parameter that will be used in WHERE condition of the query. So easiest way would be to put those conditions in where()function.

Assuming you are using ActiveRecord model, you can do something like following in your model.

$this->find()
       ->where("user_id IN(1,5,8) AND (status = 1 OR verified = 1) OR (social_account = 1 AND enable_social = 1)")
       ->all();

Of course you should use prepared statements instead of hardcoding values inside query, but above code is just to give you an idea.

You can find more detail in documentation Here and Here

score 0 · Answer 8 · answered Mar 04 '20 at 11:38

User::find()
    ->select('u_id , u_unique_id, u_first_name, u_last_name, u_username, u_email, u_image,u_phone') 
    ->andWhere("u_status != 2  AND u_id != 1 and u_role in (6,7)")
    ->andWhere(
        ['or',
            ['like', 'u_first_name', $searchVal],
            ['like', 'u_last_name', $searchVal],
            ['like', 'u_username', $searchVal],
            ['like', 'u_email', $searchVal]

        ]
        )
    ->all();

Yii2 How to perform where AND or OR condition grouping?

8 Answers8

Linked