图5
1、在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plans/have?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=f347ba5bb18cb3fbef94c0b37c796bf5&filter[and][0][or][0][title][like]=深圳市&filter[status][neq]=0&filter[and][0][or][1][keyword]=关键字 ，如图1
filter[and][0][or][0][title][like]:深圳市 filter[status][neq]:0 filter[and][0][or][1][keyword]:关键字
2、打印请求参数：$requestParams
Array
(
[filter] => Array
(
[and] => Array
(
[0] => Array
(
[or] => Array
(
[0] => Array
(
[title] => Array
(
[like] => 深圳市
)
)
[1] => Array
(
[keyword] => 关键字
)
)
)
)
[status] => Array
(
[neq] => 0
)
)
)
3、打印过滤器：$filter
Array
(
[0] => AND
[1] => Array
(
[0] => AND
[1] => Array
(
[0] => OR
[1] => Array
(
[0] => LIKE
[1] => {{%plan}}.[[title]]
[2] => 深圳市
)
[2] => Array
(
[{{%plan}}.[[keyword]]] => 关键字
)
)
)
[2] => Array
(
[0] => !=
[1] => {{%plan}}.[[status]]
[2] => 0
)
)
4、最终生成的 SQL 如下，如图2
SELECT `pa_plan`.* FROM `pa_plan` WHERE (((`pa_plan`.`title` LIKE '%深圳市%') OR (`pa_plan`.`keyword`='关键字')) AND (`pa_plan`.`status` != '0')) GROUP BY `pa_plan`.`id` ORDER BY `pa_plan`.`id` DESC LIMIT 20
5、现在的需求是，字段 keyword，关键字，多个用,号隔开，需要精准匹配，而不是模糊匹配，更不是等于。如图3
6、打开网址：https://www.yiiframework.com/doc/api/2.0/yii-data-activedatafilter#$conditionBuilders-detail ，基于映射过滤条件关键字以构建方法。 buildCondition() 使用这些方法来构建实际的过滤条件。可以使用 PHP 回调指定特定条件构建器的实现，如图4
7、复制 \vendor\yiisoft\yii2\data\ActiveDataFilter.php 至 \common\components\data\ActiveDataFilter.php，编辑，以支持 MySQL FIND_IN_SET() 函数
<?php
/**
* @link http://www.yiiframework.com/
* @copyright Copyright (c) 2008 Yii Software LLC
* @license http://www.yiiframework.com/license/
*/
namespace common\components\data;
/**
* ActiveDataFilter allows composing a filtering condition in a format suitable for [[\yii\db\QueryInterface::where()]].
*
* @see DataFilter
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/class ActiveDataFilter extends \yii\data\ActiveDataFilter
{
/**
* @var array keywords or expressions that could be used in a filter.
* Array keys are the expressions used in raw filter value obtained from user request.
* Array values are internal build keys used in this class methods.
*
* Any unspecified keyword will not be recognized as a filter control and will be treated as
* an attribute name. Thus you should avoid conflicts between control keywords and attribute names.
* For example: in case you have control keyword 'like' and an attribute named 'like', specifying condition
* for such attribute will be impossible.
*
* You may specify several keywords for the same filter build key, creating multiple aliases. For example:
*
* ```php
* [
* 'eq' => '=',
* '=' => '=',
* '==' => '=',
* '===' => '=',
* // ...
* ]
* ```
*
* > Note: while specifying filter controls take actual data exchange format, which your API uses, in mind.
* > Make sure each specified control keyword is valid for the format. For example, in XML tag name can start
* > only with a letter character, thus controls like `>`, '=' or `$gt` will break the XML schema.
*/ public $filterControls = [
'and' => 'AND',
'or' => 'OR',
'not' => 'NOT',
'lt' => '<',
'gt' => '>',
'lte' => '<=',
'gte' => '>=',
'eq' => '=',
'neq' => '!=',
'in' => 'IN',
'nin' => 'NOT IN',
'like' => 'LIKE',
'fis' => 'FIND_IN_SET',
];
/**
* @var array maps filter condition keywords to validation methods.
* These methods are used by [[validateCondition()]] to validate raw filter conditions.
*/ public $conditionValidators = [
'AND' => 'validateConjunctionCondition',
'OR' => 'validateConjunctionCondition',
'NOT' => 'validateBlockCondition',
'<' => 'validateOperatorCondition',
'>' => 'validateOperatorCondition',
'<=' => 'validateOperatorCondition',
'>=' => 'validateOperatorCondition',
'=' => 'validateOperatorCondition',
'!=' => 'validateOperatorCondition',
'IN' => 'validateOperatorCondition',
'NOT IN' => 'validateOperatorCondition',
'LIKE' => 'validateOperatorCondition',
'FIND_IN_SET' => 'validateOperatorCondition',
];
/**
* @var array specifies the list of supported search attribute types per each operator.
* This field should be in format: 'operatorKeyword' => ['type1', 'type2' ...].
* Supported types list can be specified as `*`, which indicates that operator supports all types available.
* Any unspecified keyword will not be considered as a valid operator.
*/ public $operatorTypes = [
'<' => [self::TYPE_INTEGER, self::TYPE_FLOAT, self::TYPE_DATETIME, self::TYPE_DATE, self::TYPE_TIME],
'>' => [self::TYPE_INTEGER, self::TYPE_FLOAT, self::TYPE_DATETIME, self::TYPE_DATE, self::TYPE_TIME],
'<=' => [self::TYPE_INTEGER, self::TYPE_FLOAT, self::TYPE_DATETIME, self::TYPE_DATE, self::TYPE_TIME],
'>=' => [self::TYPE_INTEGER, self::TYPE_FLOAT, self::TYPE_DATETIME, self::TYPE_DATE, self::TYPE_TIME],
'=' => '*',
'!=' => '*',
'IN' => '*',
'NOT IN' => '*',
'LIKE' => [self::TYPE_STRING],
'FIND_IN_SET' => [self::TYPE_STRING],
];
/**
* @var array maps filtering condition keywords to build methods.
* These methods are used by [[buildCondition()]] to build the actual filtering conditions.
* Particular condition builder can be specified using a PHP callback. For example:
*
* ```php
* [
* 'XOR' => function (string $operator, mixed $condition) {
* //return array;
* },
* 'LIKE' => function (string $operator, mixed $condition, string $attribute) {
* //return array;
* },
* ]
* ```
*/ public $conditionBuilders = [
'AND' => 'buildConjunctionCondition',
'OR' => 'buildConjunctionCondition',
'NOT' => 'buildBlockCondition',
'<' => 'buildOperatorCondition',
'>' => 'buildOperatorCondition',
'<=' => 'buildOperatorCondition',
'>=' => 'buildOperatorCondition',
'=' => 'buildOperatorCondition',
'!=' => 'buildOperatorCondition',
'IN' => 'buildOperatorCondition',
'NOT IN' => 'buildOperatorCondition',
'LIKE' => 'buildOperatorCondition',
'FIND_IN_SET' => 'buildFindInSetCondition',
];
/**
* Builds an findInSet condition.
* @param string $operator operator keyword.
* @param mixed $condition attribute condition.
* @param string $attribute attribute name.
* @return string actual condition.
*/ protected function buildFindInSetCondition($operator, $condition, $attribute)
{
if (isset($this->queryOperatorMap[$operator])) {
$operator = $this->queryOperatorMap[$operator];
}
return "" . $operator . "('" . $this->filterAttributeValue($attribute, $condition) . "', " . $attribute . ")";
}
}
8、编辑 \api\rests\plan\HaveAction.php，以支持字段 keyword 的过滤
public $dataFilter = [
'class' => 'common\components\data\ActiveDataFilter',
'searchModel' => 'api\models\PlanSearch',
'attributeMap' => [
'created_at' => '{{%plan}}.[[created_at]]',
'status' => '{{%plan}}.[[status]]',
'title' => '{{%plan}}.[[title]]',
'keyword' => '{{%plan}}.[[keyword]]',
],
];
9、在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plans/have?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=f347ba5bb18cb3fbef94c0b37c796bf5&filter[and][0][or][0][title][like]=深圳市2&filter[status][neq]=0&filter[and][0][or][1][keyword][fis]=关键字3 ，如图5
filter[and][0][or][0][title][like]:深圳市2 filter[status][neq]:0 filter[and][0][or][1][keyword][fis]:关键字3
10、最终生成的 SQL 如下，符合预期，如图6
SELECT `pa_plan`.* FROM `pa_plan` WHERE (((`pa_plan`.`title` LIKE '%深圳市2%') OR (FIND_IN_SET('关键字3', `pa_plan`.`keyword`))) AND (`pa_plan`.`status` != '0')) GROUP BY `pa_plan`.`id` ORDER BY `pa_plan`.`id` DESC LIMIT 20