Yii Advanced Filters Guide

Installation

To install the extension using the default syntax shown in the introduction, simply:

1. Create a new folder in your extensions directory. This can be named anything you like, but for this guide, we will call the folder advancedFilters.

2. Download and unzip the Advanced Filters extension and copy the files into your new folder. Your folder structure should now look like this:

|- extensions
   |- advancedFilters ← name this anything you like
      |- components
      |- dbhelpers
      |- filters
      |- AdvancedFilters.php
      |- LICENSE
      |- README.md

3. Update your application configuration file to include the following:

'components'=>array(
            Name the application component anything you like
    ...     ↓
    'advancedFilters'=>array(
        'class'=>'ext.advancedFilters.AdvancedFilters',
    ),                       ↑
),                           The folder name created in step 1

NOTE: If you have used a different folder name, ensure that the class path is updated accordingly. You can name the application component anything that you like and it does not have to match the folder name.

Usage

With the AfDbCriteria Class

If you aren't using an extended version of CDbCriteria, you can make use of the AfDbCriteria class.

In your search model, instantiate your criteria object as follows:

$criteria = Yii::app()->advancedFilters->createCriteria();

You can use this object as you would a standard CDbCriteria object, but the following method is also available:

public AfDbCriteria addAdvancedFilterCondition(
        string $columnExpression, string $filterExpression,
        string $operator='AND', array $config=array())

Your model's search method will look something like this:

public function search()
{
    // Instantiate and configure an AfDbCriteria object
    $criteria = Yii::app()->advancedFilters->createCriteria();

    // Add a normal compare condition using the item_code attribute
    $criteria->compare('item_code', $this->item_code);

    // Add an advanced filter condition using the description attribute
    $criteria->addAdvancedFilterCondition('description', $this->description);

    // Return your data provider as normal
    return new CActiveDataProvider('Item', array(
        'criteria'=>$criteria,
        ...
    ));
}

With a Customised Criteria Class

If you're already using an extended version of CDbCriteria and don't want to lose your additional functionality, you can apply advanced filter conditions using a convenience method of the AdvancedFilters application component:

public function search()
{
    // Instantiate your custom class
    $criteria = new CDbCriteriaCustomClass();

    // Add a normal compare condition using the item_code attribute
    $criteria->compare('item_code', $this->item_code);

    // Add an advanced filter condition using the description attribute
    Yii::app()->advancedFilters->addAdvancedFilterCondition(
            $criteria, 'description', $this->description);

    // Return your data provider as normal
    return new CActiveDataProvider('Item', array(
        'criteria'=>$criteria,
        ...
    ));
}

Filter Overview

By default, five filter classes are implemented. They are described below in their default order of priority.

When parsing an expression, the full expression is first split into expression segments using the 'or' and 'and' delimiters. Next, any logic inversion prefix and suffix characters are analysed and removed. Finally, the segment is trimmed to remove any surrounding whitespace.

The remaining segment is analysed by each filter in turn, in order of filter priority (small to large). As soon as a filter is found that accepts the segment, that filter is selected to process the segment and no more filters are checked.

Range

This filter handles numeric ranges only.

By default, it understands expression segments in the following formats:

The range filter will only match if n1 and n2 are numbers as determined by the $numberPattern property.

By default, rows with non-numeric values will be excluded from the results. If you wish to treat non-numeric values as equal to zero, set the $treatNonNumericValuesAsZero property to true.

Exact

This filter requires that the column value exactly matches the quoted text expression under text comparison.

By default, it understands expression segments in the following format:

Substring

This filter requires that the column value contains the quoted text expression under text comparison. It can be used to include space characters in the search term, or to include other characters which might otherwise be processed by a filter.

By default, it understands expression segments in the following format:

Regex

The regex filter requires that the column value match the provided regular expression. Note that the regular expression syntax is dependent on the type of database targeted.

The regex filter relies on a database connection to check if the provided expression segment is valid. You can provide either the name of an application component, or a CDbConnection object to the extension configuration's $dbConnection property. By default, the 'db' application component will be used.

By default, it understands expression segments in the following format:

Default

The default filter is a catch-all filter that will always run if no previous filter accepts the expression segment. Even if you have deactivated the default filter, it will be appended to the filter list to ensure that every expression is accepted.

By default, this filter will split the provided search term into words on space characters and return rows where every word appears in any order in the column value.

Configuration

The extension and related filters have default property values which can be overridden at three levels:

Application Level Configuration

Configure the extension in your main config file to override the defaults for your whole application. These defaults can be overridden again at lower levels.

'components'=>array(
    ...
    'advancedFilters'=>array(
        'class'=>'ext.filter.AdvancedFilters',
        'andDelimiter'=>' and ', // Overrides default '&'
        'orDelimiter'=>' or ',   // Overrides default '|'
        ...
    ),
),

Criteria Level Configuration

If you're using the AfDbCriteria class to apply your filters, you can override application and default configuration when creating the criteria object. This updated configuration will apply for each advanced filter condition that you add.

$criteria = Yii::app()->advancedFilters->createCriteria(array(), array(
    'orDelimiter'=>',', // Overrides application level ' or '
    'filterConfig'=>array(
        'regex'=>array(
            'active'=>false, // Disable the regex filter
        ),
    ),
));

Condition Level Configuration

All previous configuration can be overridden when you add an advanced filter condition.

$criteria->addAdvancedFilterCondition('code', $this->code, 'AND', array(
    'orDelimiter'=>'', // Disable 'or' delimition
));

The same applies if you're adding a condition using the application component convenience method.

Configurable Properties

Regardless of where you're applying the configuration, any of the properties below can be overridden. Their default values are shown here. See the class reference for a detailed description of each property.

array(
    'orDelimiter'       => '|',
    'andDelimiter'      => '&',
    'escapeSequence'    => '\\', // Single backslash
    'invertLogicPrefix' => '!',
    'invertLogicSuffix' => '',
    'dbConnection'      => 'db',

    'filterConfig' => array(

        'range' => array(
            'class'                       => 'AfRangeFilter',
            'priority'                    => 10,
            'active'                      => true,
            'treatNullAsEmptyString'      => true,
            'treatNonNumericValuesAsZero' => true,
            'betweenPrefix'               => '',
            'betweenInfix'                => ' to ',
            'betweenSuffix'               => '',
            'lessThanPrefix'              => '<',
            'lessThanSuffix'              => '',
            'lessThanEqualPrefix'         => '<=',
            'lessThanEqualSuffix'         => '',
            'greaterThanPrefix'           => '>',
            'greaterThanSuffix'           => '',
            'greaterThanEqualPrefix'      => '>=',
            'greaterThanEqualSuffix'      => '',
            'equalPrefix'                 => '=',
            'equalSuffix'                 => '',
            'numberPattern'               => '-?\\d*\\.?\\d+',
            'conversionNumDigits'         => 20,
            'conversionDecimalPlaces'     => 4,
        ),

        'exact' => array(
            'class'                       => 'AfExactFilter',
            'priority'                    => 20,
            'active'                      => true,
            'treatNullAsEmptyString'      => true,
            'prefix'                      => '"',
            'suffix'                      => '"',
        ),

        'substring' => array(
            'class'                       => 'AfSubstringFilter',
            'priority'                    => 30,
            'active'                      => true,
            'treatNullAsEmptyString'      => true,
            'prefix'                      => '#',
            'suffix'                      => '#',
        ),

        'regex' => array(
            'class'                       => 'AfRegexFilter',
            'priority'                    => 40,
            'active'                      => true,
            'treatNullAsEmptyString'      => true,
            'prefix'                      => '/',
            'suffix'                      => '/',
        ),

        'default' => array(
            'class'                       => 'AfDefaultFilter',
            'priority'                    => 50,
            'active'                      => true,
            'treatNullAsEmptyString'      => true,
            'wordDelimiter'               => ' ',
        ),

    ),

    $driverMap = array(
        // Advanced config, see section on extending database helpers
    ),
)

Configuration Example 1

If you wanted to use more natural language in your filters, you could configure the component as follows:

...
'components'=>array(
    ...
    'advancedFilters'=>array(
        'andDelimiter'=>' and ',
        'orDelimiter'=>' or ',
        'invertLogicPrefix'=>'not ',
    ),
),
...

Your users will now be able to write filters like this:

Note that the user must enter the space characters that we have specified in the delimiter config. This prevents words from being split unintentionally, so torn will never be treated as the expression t or n. It doesn't matter if the user types additional whitespace, as this will automatically be trimmed when the expression is split.

With the standard delimiters, the whitespace is not required, so <400&!"" and < 400 & ! "" are equivalent.

Configuration Example 2

Perhaps you have certain columns where you would like to force exact numeric matching when a number is entered without requiring the user to prefix an equal symbol. To achieve this, you could update the config as follows when adding the filter condition for the column:

$criteria->addAdvancedFilterCondition('id', $this->id, 'AND', array(
    'filterConfig'=>array(
        'range'=>array(
            'equalPrefix'=>'',
        ),
    ),
));

Here we have changed the range filter's $equalPrefix value from '=' to an empty string, and the suffix is an empty string by default. With these changes, the range filter will perform a numeric equality match when it finds an empty string, followed by a number, followed by an empty string; i.e. a plain number.

When the user enters 100, the range filter will accept the expression and return rows with a value numerically equal to 100.

Extending

You can extend the advanced filters functionality by replacing filter classes, adding new filter classes and adding support for different databases.

Adding New Filters

To add a new filter, simply create a new class extending AfBaseFilter and implement the acceptsFilterExpression() and getCriteria() methods. You can make your new class available for the whole application by adding an entry into the $filterConfig array with an unused key, and specifying the path alias to your class.

As an example, we will create a new filter class which allows the user to search for null and non-null values.

The NullFilter Class

protected/components/NullFilter.php

<?php

class NullFilter extends AfBaseFilter
{
    /**
     * @var string holds the string that the user can enter in
     * order to filter null columns.
     * This can be overridden via config.
     */
    public $nullString = 'null';

    /**
     * Accepts the filter expression if it is the same as the
     * $nullString property.
     */
    public function acceptsFilterExpression()
    {
        return $this->filterExpression === $this->nullString;
    }

    /**
     * Construct and return a CDbCriteria object with your
     * filter conditions applied.
     */
    public function getCriteria()
    {
        // Override the value of this property as it
        // makes no sense in the context of this filter
        $this->treatNullAsEmptyString = false;

        // You must alter your criteria if the logic is inverted
        $conditionString = $this->invertLogic
                ? 'IS NOT NULL' : 'IS NULL';

        // Instantiate a regular CDbCriteria object
        $criteria = new CDbCriteria;

        // Use any CDbCriteria methods to apply your filter
        $criteria->addCondition("({$this->columnExpression}) $conditionString");

        return $criteria;
    }
}

Adding the New Filter to the Application

To make the filter available throughout your application, update the config for the advanced filters application component. The key you choose for the filterConfig entry is not important, but must be unique:

protected/config/main.php

...
'components'=>array(
    ...
    'advancedFilters'=>array(
        'filterConfig'=>array(
            'null'=>array(
                'class'=>'NullFilter',
                // You could override your 'nullString' property
                // here, or base properties like the priority
            ),
        ),
    ),
),
...

Now, whenever you add an advanced filter condition, it will automatically include your filter. Users will now be able to type null or !null into a filter box to filter on null and non-null values.

Replacing Existing Filters

To replace an existing filter, create a new class extending the class that you're replacing or AfBaseFilter.

Update the config file to override the filter config class for the relevant filter config key. To replace the AfExactFilter class with your own version, update the 'exact' entry in the filterConfig array:

...
'components'=>array(
    ...
    'advancedFilters'=>array(
        'filterConfig'=>array(
            'exact'=>array(
                'class'=>'path.to.YourExactFilter',
            ),
        ),
    ),
),
...

Database Helpers

Database helpers provide methods to generate database specific syntax. Filters can use these methods to manipulate the column and filter expressions, or to add conditions to criteria in a database dependent way.

To create a new database helper, create a class extending AfBaseDbHelper and implement the abstract methods. You should also override any base methods which use syntax which is unsuitable for the target database. See the existing database helpers for clarification.

To make your database helper available to the extension, update the $driverMap property in the application component's configuration. The default values can be found in the AfParser class.

There are two database helpers available for SQL Server, a generic one and one for version 2012 and later. By default, the generic helper will be loaded, but you should use the 2012 version if you are dealing with SQL Server 2012 or later as it provides more robust syntax. To use the class, configure the extension as shown below:

...
'components'=>array(
    ...
    'advancedFilters'=>array(
        'driverMap'=>array(
            'mssql'=>'AfMssql2012DbHelper',
            'dblib'=>'AfMssql2012DbHelper',
            'sqlsrv'=>'AfMssql2012DbHelper',
        ),
    ),
),
...