How do I make the following nested Query?

I’m having a little problem making a dynamic Query using the QueryBuilder. I’m trying to make a Query with nested “LIKE” parameters to be compared between each other. Can this be done?

This is the Query I’m trying to produce:


SELECT id FROM `users` WHERE `deleted`=0 AND (`firstname` LIKE '%value%' OR `lastname` LIKE '%value%' OR `email` LIKE '%value%') AND `userroleId`=1

Right now I’m trying it like this:




$query = User::find()->where(['deleted' => 0]);

$query->andWhere(['like', 'firstname', "value"]);

$query->orWhere(['like', 'lastname', "value"]);

$query->orWhere(['like', 'email', "value"]);

$query->andWhere(['and', 'userroleId', 1]);



Obviously this isn’t right at all. I’ve been trying to fiddle around with different setups to make the middle part compare between each other but with no success. Would love some help. Maybe a manual custom Query is my only option?

Thank you

Not tested:




$query = User::find()->where(['and', 

    ['deleted' => 0], 

    ['or',

        ['like', 'firstname', "value"],

        ['like', 'lastname',  "value"],

        ['like', 'email',     "value"]

    ],

    ['userroleId' => 1]

]):



Does this work? Here is the documentation: http://www.yiiframework.com/doc-2.0/yii-db-queryinterface.html#where()-detail

you could try something like this




    $value  = '' // define it

    

    $sql = 'SELECT id '

            . 'FROM users '

            . 'WHERE '

                . '(deleted = :deleted '

            . 'AND (firstname like :value OR lastname like :value OR email like :value) '

            . 'AND userroleId = :userroleId';


    $query = User::findBySql($sql, [

        ':deleted' => 0,

        ':value' => '%'.$value.'%',

        ':userroleId' => 1

    ])->all();




$query = User::find()->where(['and', 

    ['deleted' => 0], 

    ['or',

        ['like', 'firstname', "value"],

        ['like', 'lastname',  "value"],

        ['like', 'email',     "value"]

    ],

    ['userroleId' => 1]

]);

Worked perfectly! I’m going to build it dynamic like this (if anyone need the info for future reference):


$array = [];

    $array[] = 'and';

    $array[] = ['deleted' => 0];

    $array[] = ['or',

                        ['like', 'firstname', "value"],

                        ['like', 'lastname',  "value"],

                        ['like', 'email',     "value"]

                    ];

    $array[] = ['userroleId' => 1];




        $query = UserRecord::find()->where($array);