Yii2 How to perform where AND or OR condition grou

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

Answer 1

With MongoDB:

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

Tested. Similar with DBMS.

Answer 2

U can also do it this way:

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

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

Answer 4

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

Answer 5

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

Answer 6

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