在 Yii 2.0 中，基于映射过滤条件关键字以构建方法。新增特定条件构建器(fis)的实现，以支持 MySQL FIND_IN_SET() 函数
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
![在 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]=关键字](http://www.shuijingwanwq.com/wp-content/uploads/2019/08/1-7.png)
图1
1 2 3 | filter[and][0][or][0][title][like]:深圳市filter[status][neq]:0filter[and][0][or][1][keyword]:关键字 |
2、打印请求参数：$requestParams
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | Array( [filter] => Array ( [and] => Array ( [0] => Array ( [or] => Array ( [0] => Array ( [title] => Array ( [like] => 深圳市 ) ) [1] => Array ( [keyword] => 关键字 ) ) ) ) [status] => Array ( [neq] => 0 ) )) |
3、打印过滤器：$filter
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
图2
1 | 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
图3
6、打开网址：https://www.yiiframework.com/doc/api/2.0/yii-data-activedatafilter#$conditionBuilders-detail ，基于映射过滤条件关键字以构建方法。 buildCondition() 使用这些方法来构建实际的过滤条件。可以使用 PHP 回调指定特定条件构建器的实现，如图4
图4
7、复制 \vendor\yiisoft\yii2\data\ActiveDataFilter.php 至 \common\components\data\ActiveDataFilter.php，编辑，以支持 MySQL FIND_IN_SET() 函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | <?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 的过滤
1 2 3 4 5 6 7 8 9 10 | 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][1][keyword][fis]=关键字3](http://www.shuijingwanwq.com/wp-content/uploads/2019/08/6.png)
图5
1 2 3 | filter[and][0][or][0][title][like]:深圳市2filter[status][neq]:0filter[and][0][or][1][keyword][fis]:关键字3 |
10、最终生成的 SQL 如下，符合预期，如图6
图5
1 | 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 |
近期评论