Yii2 How to perform where AND or OR condition grou

问题:

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

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

回答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

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

回答4:

U can also do it this way:

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

回答5:

With MongoDB:

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

Tested. Similar with DBMS.

回答6:

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

回答7:

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