How to conditionally query in yii framework-YII-php.cn

How to conditionally query in yii framework

爱喝马黛茶的安东尼

Release： 2019-12-07 15:33:15

Original

2627 people have browsed it

Conditional query

$customers = Customer::find()->where($cond)->all();

Copy after login

$cond is what we call conditions. The way of writing conditions also depends on the query data. There are differences, so how to write query conditions using yii2?

[[Simple condition]]

// SQL: (type = 1) AND (status = 2).  
$cond = [&#39;type&#39; => 1, &#39;status&#39; => 2]   
// SQL:(id IN (1, 2, 3)) AND (status = 2)  
$cond = [&#39;id&#39; => [1, 2, 3], &#39;status&#39; => 2]   
//SQL:status IS NULL  
$cond = [&#39;status&#39; => null]

Copy after login

[and]: Combine different conditions together, usage example:

//SQL:`id=1 AND id=2`  
$cond = [&#39;and&#39;, &#39;id=1&#39;, &#39;id=2&#39;]  
//SQL:`type=1 AND (id=1 OR id=2)`  
$cond = [&#39;and&#39;, &#39;type=1&#39;, [&#39;or&#39;, &#39;id=1&#39;, &#39;id=2&#39;]]  
//SQL:`type=1 AND (id=1 OR id=2)` //此写法&#39;=&#39;可以换成其他操作符，例：in like != >=等  
$cond = [  
    &#39;and&#39;,  
    [&#39;=&#39;, &#39;type&#39;, 1],  
    [  
        &#39;or&#39;,  
        [&#39;=&#39;, &#39;id&#39;, &#39;1&#39;],  
        [&#39;=&#39;, &#39;id&#39;, &#39;2&#39;],  
    ]  
]

Copy after login

[[or]]:

/SQL:`(type IN (7, 8, 9) OR (id IN (1, 2, 3)))`  
$cond = [&#39;or&#39;, [&#39;type&#39; => [7, 8, 9]], [&#39;id&#39; => [1, 2, 3]]

Copy after login

[[not]]:

//SQL:`NOT (attribute IS NULL)`  
$cond = [&#39;not&#39;, [&#39;attribute&#39; => null]]

Copy after login

[[between]]: not between has the same usage

//SQL:`id BETWEEN 1 AND 10`  
$cond = [&#39;between&#39;, &#39;id&#39;, 1, 10]

Copy after login

[[in]]: not in has the same usage

//SQL:`id IN (1, 2, 3)`  
$cond = [&#39;in&#39;, &#39;id&#39;, [1, 2, 3]] or $cond = [&#39;id&#39;=>[1, 2, 3]]

Copy after login

//IN条件也适用于多字段  
$cond = [&#39;in&#39;, [&#39;id&#39;, &#39;name&#39;], [[&#39;id&#39; => 1, &#39;name&#39; => &#39;foo&#39;], [&#39;id&#39; => 2, &#39;name&#39; => &#39;bar&#39;]]]  
//也适用于内嵌sql语句  
$cond = [&#39;in&#39;, &#39;user_id&#39;, (new Query())->select(&#39;id&#39;)->from(&#39;users&#39;)->where([&#39;active&#39; => 1])]

Copy after login

[[like]]:

//SQL:`name LIKE &#39;%tester%&#39;`
$cond = [&#39;like&#39;, &#39;name&#39;, &#39;tester&#39;]
//SQL:`name LIKE &#39;%test%&#39; AND name LIKE &#39;%sample%&#39;`
$cond = [&#39;like&#39;, &#39;name&#39;, [&#39;test&#39;, &#39;sample&#39;]]
//SQL:`name LIKE &#39;%tester&#39;`
$cond = [&#39;like&#39;, &#39;name&#39;, &#39;%tester&#39;, false]

Copy after login

[[exists]]: not exists usage is similar to

//SQL:EXISTS (SELECT "id" FROM "users" WHERE "active"=1)
$cond = [&#39;exists&#39;, (new Query())->select(&#39;id&#39;)->from(&#39;users&#39;)->where([&#39;active&#39; => 1])]

Copy after login

In addition, you can specify any operator as follows:

//SQL:`id >= 10`
$cond = [&#39;>=&#39;, &#39;id&#39;, 10] 
//SQL:`id != 10`
$cond = [&#39;!=&#39;, &#39;id&#39;, 10]

Copy after login

PHP Chinese website, There are a large number of free Yii introductory tutorials, everyone is welcome to learn!

The above is the detailed content of How to conditionally query in yii framework. For more information, please follow other related articles on the PHP Chinese website!