Stop ACTA

CakePHP Filter Plugin advanced usage

Posted in CakePHP on 21.04.2011.

A lot of people have been asking for an article on "advanced usage" of the Filter plugin for CakePHP, so here it is (finally, eh?).

Introduction

In the first article about the Filter plugin, I've explained how to use the filter in a quick and easy way. For most people, that kind of usage should be enough. So, before you read this article, go read that one. Go on, I'm going to wait here.

Now, what's important to understand is that the filter plugin is messing up your query in order to filter data. By messing up, I mean modifying it a lot, depending on your filtering conditions. This means it won't be perfect always. For example, there is currently no "fast" way to filter data of ModelA by ModelC if ModelA hasMany ModelB hasMany ModelC. In order to do these somewhat more complex things, you need to use the filter callbacks.

Filter callbacks are simple in nature, yet they allow you to do all kinds of crazy stuff. They allow you to check the current filtering data and options, and to determine whether filtering is to be performed at all. Additionally, after all the filter conditions have been applied to a query, you get to inspect it and modify everything.

There are two callbacks, beforeDataFilter() and afterDataFilter().

beforeDataFilter callback

beforeDataFilter callback is called before anything is done to the query. You use the beforeDataFilter in the following way:

class YourModel extends AppModel
{
    function beforeDataFilter($query, $options)
    {
    }
}

Where $query parameter contains the current CakePHP query options (unmodified by filter plugin!) and the $options is an array of 'values' and 'settings' for the current query. 'values' contains the submitted values from the filter form, while 'settings' contains the plugin settings (didn't see that coming, did you?) which will be used to determine what needs to be filtered and how.

If beforeDataFilter returns false, the filtering will be aborted, and the $query will be executed as is. This enables you, for example, to check for some special condition in the values array, when the data should not be filtered.

afterDataFilter callback

This callback is only slightly more complicated. It has the same signature:

class YourModel extends AppModel
{
    function afterDataFilter($query, $options)
    {
    }
}

Both parameters are the same as for beforeDataFilter. There are two differences:

  1. The $query has already been modified by the filter plugin. If you print it with debug($query) you can see this for yourself.
  2. If you return an array from this callback, the plugin will assume it is a modified $query, and it will try to execute that.

The $query variable is of course the CakePHP query definition, not the raw SQL. You may see something like this for example:

Array
(
    [conditions] => 
    [fields] => 
    [joins] => Array
        (
        )

    [limit] => 
    [offset] => 
    [order] => Array
        (
            [0] => 
        )

    [page] => 1
    [group] => 
    [callbacks] => 1
)

Example

So, how would you modify this query to solve the problem from the start of this article, filtering ModelA with values from ModelC? Simple, add a filter with a field which doesn't exist! Let's take the following database structure:

FilteredRows hasMany RelatedRows hasMany OtherRows

To filter by related rows, you simply add something like this in the filter configuration:

class FilteredRowsController extends AppController
{
    var $components = array('Filter.Filter');

    var $filters = array
        (
            'index' => array
            (
                'FilteredRow' => array
                (
                    'RelatedRow.value'
                )
            )
        );
)

But what about filtering with other rows?

var $filters = array
    (
        'index' => array
        (
            'FilteredRow' => array
            (
                'OtherRow.value'
            )
        )
    );

Hm, well, that doesn't work, does it? So what do we do? Simply, create a filter with a field which doesn't exist. For example:

var $filters = array
    (
        'index' => array
        (
            'FilteredRow' => array
            (
                'FilteredRow.dummy_value' => array('condition' => '=')
            )
        )
    );

Now, when you try to run this, if it will not work of course. If you try to submit a form like this, you'll probably get something like SQL Error: 1054: Unknown column 'FilteredRow.dummy_value' in 'where clause'. But that's where our callbacks come into play.

If you do a debug($query) in afterDataFilter, you should see something like this:

Array
(
    [conditions] => Array
        (
            [FilteredRow.dummy_value] => 5
        )

    [fields] => 
    [joins] => Array
        (
        )

    [limit] => 
    [offset] => 
    [order] => Array
        (
            [0] => 
        )

    [page] => 1
    [group] => 
    [callbacks] => 1
)

What we need to do here is:

  1. Remove the dummy_value condition
  2. Join the models properly to include other_rows table
  3. Add a new condition which will filter with value from other_rows.value

This is the final method which accomplishes all of those things:

function afterDataFilter($query, $options)
{
    unset($query['conditions']['FilteredRow.dummy_value']);

    $query['joins'][] = array
        (
            'table'         => 'related_rows',
            'alias'         => 'DummyRelatedRow',
            'type'          => 'INNER',
            'conditions'    => array
            (
                'FilteredRow.id = DummyRelatedRow.filtered_row_id'
            )
        );

    $query['joins'][] = array
        (
            'table'         => 'other_rows',
            'alias'         => 'DummyOtherRow',
            'type'          => 'INNER',
            'conditions'    => array
            (
                'DummyRelatedRow.id = DummyOtherRow.related_row_id'
            )
        );

    $query['conditions']['DummyOtherRow.value'] = $options['values']['FilteredRow']['dummy_value'];

    // don't forget to return the modified $query!
    return $query;
}

I'm not going to explain everything, I'm sure you can figure it out for yourself. ;)

Conclusion

As you can see, the callback usage allows for a lot of flexibility with filtering, because you can send anything you want from the form, and turn it into any kind of query you want internally. It is, of course, a bit messy, since you're mangling the insides of CakePHP's query. I'm guessing this could be avoided with a better plugin, and some day when I get a lot of spare time, I'll make it so that you can filter by "OtherRow" values. Until then, this is your best shot :)

I'm also accepting patches on GitHub, you know? Hint-hint, nudge-nudge...

Happy baking!

Article comments — View · Add


Page 1 of 4
1 · 2 · 3 · 4

Doug :: 10.05.2011 06:04:08
You're a legend. I just sat down and returned to your site to work on a problem of extending your filter to use customer-created fields and you've created this post - brilliant! :)
jbmere :: 26.07.2011 16:40:22
Hi, I've spent some time by trying to produce insensitive case filter through the beforeDataFilter without success.
I need to have much more skills that I currently have :-(

Should it be possible to get from you some hit about how to address the issue ?

Thnkx
lecterror :: 31.07.2011 15:11:05
@jbmere: For starters, you're supposed to use afterDataFilter, not before :)

Think of it this way: your conditions after a submit will be "normal", case-sensitive. You need to remove those, just like in this article, and then insert your own conditions where you could (for example) do a UPPER() on your field and strtoupper on your data from the form.

Does that help a bit?
Bart :: 31.07.2011 15:59:40
Awesome plugin and just what I needed :) Do you know how I can get the form to work with the standard issue Cake ajax pagination? I'd like to have the filter operate without needing to manually submit, so the user feedback is immediate.
lecterror :: 31.07.2011 16:30:59
Hey Bart!

Unfortunately, I haven't done anything to make the plugin work with AJAX, but it's a good idea for a future development. If you manage to accomplish anything, feel free to contribute ;)

Sorry I can't be more helpful with this. If only I was rich, then I wouldn't have to work and I could do all my hobbies like this one.. :)