a.ziaratban
(Abolfazl Ziaratban)
September 5, 2016, 7:56pm
1
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_______________|
softark
(Softark)
September 5, 2016, 11:12pm
2
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;
a.ziaratban
(Abolfazl Ziaratban)
September 6, 2016, 6:26am
3
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 ?
softark
(Softark)
September 6, 2016, 8:40am
4
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.