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

Solution 1

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();

Solution 2

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

Solution 3

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..

Solution 4

U can also do it this way:

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

Solution 5

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();

View more solutions

Comments

[Moeez]

How to use it with findOne()?

[Anton Rybalko]

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

[Boolean_Type]

@Faisal ->one() instead of ->all().