Detailed explanation of Yii2 query where condition assembly

After becoming familiar with the query conditions of Yii2, use Active Record is very convenient for querying data. Below we introduce the assembly method of conditions in the where() method.

1 Syntax Yii2 uses the where () method (of course there are other methods) to implement conditional filtering, syntax:

public $this where ( $condition, $params = [] ) $params is an optional parameter, specifying the value to be bound to the query.

$condition is a required parameter, $condition can be a string (such as 'id=1' ) or array. When

$condition is an array, there are two formats:

Hash format: [' column1' => value1, 'column2' => value2, ...]

##Operator format: [operator, operand1, operand2, ...]

2 Hash format Usually, the query condition in hash format generates an SQL statement like this: column1=value1 AND column2=value2 AND ...

If a value is an array, an IN statement will be generated.

If a value is null , IS NULL will be used to generate the statement.

Example: ['type' => 1, 'status' => 2] // 生成：(type = 1) AND (status = 2)['id' => [1, 2, 3], 'status' => 2] // 生成：(id IN (1, 2, 3)) AND (status = 2)['status' => null] // 生成：status IS NULL

3 Operator format In operator format, Yii will generate SQL statements based on the specified operator.

运算符有： and 、 or 、 not 、 between 、 not between 、 in 、 not in 、 like 、 or like 、 not like 、 or not like 、 exists 、 not exists 、 > 、 cc0b437f19d41439a31f61dca23a5da5= 、 、 != 等。

3.1 对比 ['>', 'id', 1] // 生成：id > 1[' =', 'id', 1] // 生成：id >= 1[' 具体生成的SQL语句，运算符 id 会自动加上反斜杠引号 ` ，运算数会自动转义。

3.2 and ['and', 'id' => 1, 'id' => 2] // 生成：id=1 AND id=2['and', 'id=1', 'id=2'] // 生成：id=1 AND id=2['and', 'type=1', ['or', 'id=1', 'id=2']] // 生成：type=1 AND (id=1 OR id=2) 在第2条和第3条语句中，列名称和搜索值未用键值关系指定，所以生成的SQL不会添加引号，也不会转义。

3.3 or ['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]] // 生成：(type IN (7, 8, 9) OR (id IN (1, 2, 3))) 3.4 not ['not', ['attribute' => null]] // 生成：NOT (attribute IS NULL) 3.5 between和not between ['between', 'id', 1, 10] // 生成：id BETWEEN 1 AND 10['not between', 'id', 1, 10] // 生成：id NOT BETWEEN 1 AND 10 运算符后面的运算数1为数据表 列名称 ，运算数2和运算数3分别为列值范围的 最小值 和 最大值 。

3.6 in和not in ['in', 'id', [1, 2, 3]] // 生成：id IN (1, 2, 3)['not in', 'id', [1, 2, 3]] // 生成：id NOT IN (1, 2, 3) 运算符后面的 运算数1 为列名称或DB表达式， 运算数2 为数组，指定列值所在的范围。

这个方法会为值添加引号，并正确转义。

要生成混合 IN 条件，列名和列值都设置为数组，并且用列名为列值指定下标：

['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]] // 生成：(`id`, `name`) IN ((1, 'foo'), (2, 'bar')) 另外，还可以用子查询作为 IN 条件的值，如下：

['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])] 3.7 like ['like', 'name', 'tester'] // 生成：name LIKE '%tester%'['like', 'name', ['test', 'sample']] // 生成：name LIKE '%test%' AND name LIKE '%sample%'['like', 'name', '%tester', false] // 生成：name LIKE '%tester' // 这是自定义查询方式，要传入值为false的运算数3，并且自行添加% 运算数 后面的运算数1为列名称或DB表达式，运算数2为字符串或数组，指定列值查询条件。

这个方法会为值添加引号，并正确转义。

or like 、 not like 、 or not like 用法和 like 一样。

['or like', 'name', ['test', 'sample']] // 生成：name LIKE '%test%' OR name LIKE '%sample%'['not like', 'name', 'tester'] // 生成：name NOT LIKE '%tester%'['or not like', 'name', ['test', 'sample']] // 生成：name NOT LIKE '%test%' OR name NOT LIKE '%sample%' 3.8 exists ['exists', (new Query())->select('id')->from('users')->where(['active' => 1])] // 生成：EXISTS (SELECT "id" FROM "users" WHERE "active"=1) not exists 用法和 exists 一样。

Detailed explanation of Yii2 query where condition assembly

Related articles