【宋词】《留春令》 晏几道：画屏天畔，梦回依约，十洲云水。手拈红笺寄人书，写无限、伤春事。别浦高楼曾漫倚。对江南千里。楼下分流水声中，

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

Build Methods in Yii 2.0 by Mapping Filter Condition Keywords: Add Specific Condition Builders (fis) to Support the MySQL FIND_IN_SET() Function

filter[and][0][or][1][keyword][fis]=关键字3

作者：

永夜

在

, , , , , , , , ,
Views: 34

1. Open the URL in 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]=关键字 , as shown in Figure 1

Open the URL in 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]=关键字
Figure 1
Plaintext
filter[and][0][or][0][title][like]:深圳市
filter[status][neq]:0
filter[and][0][or][1][keyword]:关键字

2. Print the request parameters: $requestParams

Plaintext
Array
(
    [filter] => Array
        (
            [and] => Array
                (
                    [0] => Array
                        (
                            [or] => Array
                                (
                                    [0] => Array
                                        (
                                            [title] => Array
                                                (
                                                    [like] => 深圳市
                                                )

                                        )

                                    [1] => Array
                                        (
                                            [keyword] => 关键字
                                        )

                                )

                        )

                )

            [status] => Array
                (
                    [neq] => 0
                )

        )

)

3. Print the filter: $filter

Plaintext
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. The final generated SQL is as follows, as shown in Figure 2

The final generated SQL is as follows
Figure 2
SQL

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. The current requirement is that the keyword field, which stores multiple keywords separated by commas, requires exact matching rather than fuzzy matching, and definitely not equality. As shown in Figure 3

The current requirement is that the keyword field, which stores multiple keywords separated by commas, requires exact matching rather than fuzzy matching, and definitely not equality.
Figure 3

6. Open the URL: https://www.yiiframework.com/doc/api/2.0/yii-data-activedatafilter#$conditionBuilders-detail . It maps filter condition keywords to builder methods. buildCondition() uses these methods to build the actual filter conditions. You can use a PHP callback to specify the implementation of a specific condition builder, as shown in Figure 4

It maps filter condition keywords to builder methods. buildCondition() uses these methods to build the actual filter conditions. You can use a PHP callback to specify the implementation of a specific condition builder
Figure 4

7. Copy \vendor\yiisoft\yii2\data\ActiveDataFilter.php to \common\components\data\ActiveDataFilter.php and edit it to support the MySQL FIND_IN_SET() function

PHP
<?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. Edit \api\rests\plan\HaveAction.php to support filtering by the keyword field

PHP

    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. Open the URL in 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 , as shown in Figure 5

filter[and][0][or][1][keyword][fis]=关键字3
Figure 5
Plaintext
filter[and][0][or][0][title][like]:深圳市2
filter[status][neq]:0
filter[and][0][or][1][keyword][fis]:关键字3

10. The final generated SQL is as follows, which meets expectations, as shown in Figure 6

The final generated SQL is as follows, which meets expectations
Figure 5
SQL

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

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章