Conditional query
$customers = Customer::find()->where($cond)->all();
$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 = ['type' => 1, 'status' => 2] // SQL:(id IN (1, 2, 3)) AND (status = 2) $cond = ['id' => [1, 2, 3], 'status' => 2] //SQL:status IS NULL $cond = ['status' => null]
[and]: Combine different conditions together, usage example:
//SQL:`id=1 AND id=2` $cond = ['and', 'id=1', 'id=2'] //SQL:`type=1 AND (id=1 OR id=2)` $cond = ['and', 'type=1', ['or', 'id=1', 'id=2']] //SQL:`type=1 AND (id=1 OR id=2)` //此写法'='可以换成其他操作符,例:in like != >=等 $cond = [ 'and', ['=', 'type', 1], [ 'or', ['=', 'id', '1'], ['=', 'id', '2'], ] ]
[[or]]:
/SQL:`(type IN (7, 8, 9) OR (id IN (1, 2, 3)))` $cond = ['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]
[[not]]:
//SQL:`NOT (attribute IS NULL)` $cond = ['not', ['attribute' => null]]
[[between]]: not between has the same usage
//SQL:`id BETWEEN 1 AND 10` $cond = ['between', 'id', 1, 10]
[[in]]: not in has the same usage
//SQL:`id IN (1, 2, 3)` $cond = ['in', 'id', [1, 2, 3]] or $cond = ['id'=>[1, 2, 3]]
//IN条件也适用于多字段 $cond = ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]] //也适用于内嵌sql语句 $cond = ['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]
[[like]]:
//SQL:`name LIKE '%tester%'` $cond = ['like', 'name', 'tester'] //SQL:`name LIKE '%test%' AND name LIKE '%sample%'` $cond = ['like', 'name', ['test', 'sample']] //SQL:`name LIKE '%tester'` $cond = ['like', 'name', '%tester', false]
[[exists]]: not exists usage is similar to
//SQL:EXISTS (SELECT "id" FROM "users" WHERE "active"=1) $cond = ['exists', (new Query())->select('id')->from('users')->where(['active' => 1])]
In addition, you can specify any operator as follows:
//SQL:`id >= 10` $cond = ['>=', 'id', 10] //SQL:`id != 10` $cond = ['!=', 'id', 10]
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!