Yii2 Query : add where clause by scope #12465

how i can implement this code by Yii2 ?

where clause structure :

{server scope} and {client scope}

server scope is require and client scope is limit and optional.

$messages = Message::find();

# set server scope

$messages->where(['creator_role_id' => $current_role_id]); #very important

$messages->andWhereStart(); # <- scope started

/*

    set client scope

    include anonymous functions and called orWhere() and andWhere()

    ....

*/

$messages->andWhereEnd(); # <- scope ended

echo $messages->createCommand()->rawSql;

output:

SELECT * FROM `messages` WHERE ( `creator_role_id` = 1 ) AND ( `title` LIKE '%te\'st%' OR 'enable' = 1 )

                                                             |______________Client Scope_______________|

You may consider using the "operator" format for where().

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#where

$messages = Message::find()


# server scope

$serverWhere = ['creator_role_id' => $current_role_id]; #very important


#client scope

$clientWhere = [

    'or',

    ['like', 'title', $title],

    ['enable' => 1]

];


/* 

    Or, you may want to construct $clientWhere differently ....

*/


$messages->where([

    'and',

    $serverScope,

    $clientScope

]);

echo $messages->createCommand()->rawSql;

thanks softark.

i read all http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#where

and i know feature of "where clause"

but i want implement above code (in first post) with orWhere() or andWhere()

i think yii2 not have this feature but i think about this problem and write this code :

$client = new Query();

$client->andWhere(['col1' => 1]);

$client->andWhere(['col2' => 2]);

$client->orWhere(['col3' => 3]);

preg_match('/^SELECT \* WHERE(.*?)$/',$client->createCommand()->rawSql,$m);


$server = new Query();

$server->where([

	'and',

	['aaa' => 1,],

	$m[1],

]);

echo $server->createCommand()->rawSql;

what is your comment ?

IMO, using andWhere() with orWhere() tends to make things confusing for a complicated set of conditions.

The following is an good example:

$client->andWhere(['col1' => 1]);

$client->andWhere(['col2' => 2]);

$client->orWhere(['col3' => 3]);

It means the following:

[sql]

where (col1 = 1 and col2 = 2) or col3 = 3

[/sql]

But are you 100% sure whether it is so? I would not be.

I would rather write like the following, explicitly expressing the logical structure.

$q = new Query();

$q->where([

    'and',

    // server

    [

        'aaa' => 1,

    ],

    // client

    [

        'or',

        [

            'col3' => 3,

        ],

        [

            'and',

             [

                 'col1' => 1,

             ],

             [

                 'col2' => 2,

             ],

        ],

    ],

]);

And I don’t think it’s a good idea to extract the ‘where’ part of the sql using preg_match.

It looks a fragile trick to me.