Filtering component for your tables

Update: The version of the component here could be outdated. Please get the actual version of the component from GitHub

Well, probably you wont believe me, but I wrote whole 3 pages while I realized that it’s so long and probably this is not the right way /and definitely it’s not easy/, so I scratched everything and start over.

It’s been quite long time since I post an article for CakePHP so here it is – shiny brand new post 🙂

This post is more a method how to make filtering, rather a rocket science component, because it’s possible to be used even without extra classes /helpers and components/, but I will post a component which will tune the functionality.

So what is all about? Have you ever miss a filtering functionality in your index actions? Well I really miss it 🙂

What is the best technique to make filter?
In this example there is a controller Countries which handle the countries in the application. table contain 3 columns, ID, NAME and CODE. Code column is numeric in this example and it’s not following any real naming or country convention.

1. You need a form in your index.ctp, so here is the code /basically it’s a form taken from add.ctp file ;)/

<?php
echo $form->create('Country', array('action'=>'/index'));
?><fieldset><legend><?php __('Filter');?></legend><?
echo $form->input('name');
echo $form->input('code');
?></fieldset><?
echo $form->end('Search');
?>

You can use your imagination how many fields and the position of them in the form, the tricky part is that you need to define the action parameter in the form tag, otherwise this will point to add action which is obviously not very smart 😉

Ok, That’s it … probably you don’t believe me, but it’s true, that’s it. The only thing which you need to do in the controller is this:

$this->set('countries', $this->paginate(null, $this->data));

OR

$this->set('countries', $this->{Model_name}->findAll($this->data), ...and the rest parameters which you need...);

and you are done, voila 😉

Ok, this was the good part, let’s continue with the bad part…
there are 2 things which are missing in the ideal world
1. if you have paginator and the filter return more results than you can see in a page then by clicking on the next>> link the filter will be lost and user will be confused from the results. A consequence of this is that the form will loose the data and user need to type it again…
2. if user search by part of a string let’s say try to search for all countries starting with “Bu” the result will be empty table instead of 3 countries starting with “Bu”, because SQL generated will search like:

SELECT ... FROM ... WHERE FIELD = 'searched string'

Ok, explaining the simple solution and the bad consequences here is the ultimate helper for this – the filter component:

<?php
/**
 * Filter component
 * Benefits:
 * 1. Keep the filter criteria in the Session
 * 2. Give ability to customize the search wrapper of the field types

 **
 * @author  Nik Chankov
 * @website http://nik.chankov.net
 * @version 1.0.0
 *
 */


class FilterComponent extends Object {
    /**
     * fields which will replace the regular syntax in where i.e. field = 'value'
     */

    var $fieldFormatting    = array(
                    "string"=>array("%1\$s LIKE", "%2\$s%%"),
                    "text"=>array("%1\$s LIKE", "%2\$s%%"),
                    "date"=>array("DATE_FORMAT(%1\$s, '%%d-%%m-%%Y')", "%2\$s"),
                    "datetime"=>array("DATE_FORMAT(%1\$s, '%%d-%%m-%%Y')", "%2\$s")
                    );
    /**
     * extra identifier (if needed to specify extra location (like requestAction))
     */

    var $identifier = '';
   
    /**
     * Function which will change controller->data array
     *
     * @param object $controller the class of the controller which call this component
     * @access public
     */

    function process(&$controller){
        $this->_prepareFilter($controller);
        $ret = $this->generateCondition($controller, $controller->data);
        return $ret;
    }
   
    /**
     * Function which loop the provided data and generate the proper where clause
     * @param object Controller or The model in the controller which has been provided in the post
     * @param array $data data which is posted from the filter
     */

    function generateCondition($object, $data=false){
        $ret = array();
        if(isset($data) && is_array($data)){
            //Loop for models
            foreach($data as $model=>$filter){
                if($model == 'OR'){
                    $ret = am($ret, array('OR'=>$this->generateCondition($object, $filter)));
                    unset($data[$model]);
                }
                if(isset($object->{$model})){ //This is object under current object.
                    $columns = $object->{$model}->getColumnTypes();
                    foreach($filter as $field=>$value){
                        if(is_array($value)){ //Possible that this node is another model
                            if(in_array($field, array_keys($columns))){ //The field is from the model, but it has special formatting
                                if(isset($value['BETWEEN'])){ //BETWEEN case
                                    if($value['BETWEEN'][0] != '' && $value['BETWEEN'][1] != ''){
                                        $ret[$model.'.'.$field.' BETWEEN ? AND ?']=$value['BETWEEN'];
                                    }
                                }
                            } else {
                                $ret = am($ret, $this->generateCondition($object->{$model}, array($field=>$value)));
                            }
                            unset($value);
                        } else {
                            if($value != ''){
                                //Trim the value
                                $value=trim($value);
                                //Check if there are some fieldFormatting set
                                if(isset($this->fieldFormatting[$columns[$field]])){
                                    if(isset($this->fieldFormatting[$columns[$field]][1])){
                                        $ret[sprintf($this->fieldFormatting[$columns[$field]][0], $model.'.'.$field, $value)] = sprintf($this->fieldFormatting[$columns[$field]][1], $model.'.'.$field, $value);
                                    } else {
                                        $ret[] = sprintf($this->fieldFormatting[$columns[$field]][0], $model.'.'.$field, $value);
                                    }
                                } else {
                                    $ret[$model.'.'.$field] = $value;
                                }
                            }
                        }
                    }
                    //unsetting the empty forms
                    if(count($filter) == 0){
                        unset($object->data[$model]);
                    }
                }
            }
        }
        return $ret;
    }
   
    /**
     * function which will take care of the storing the filter data and loading after this from the Session
     * @param object $controller
     * @return void
     */

    function _prepareFilter(&$controller){
        if(isset($controller->data)){
            foreach($controller->data as $model=>$fields){
                foreach($fields as $key=>$field){
                    if($field == ''){
                        unset($controller->data[$model][$key]);
                    }
                }
            }
            $controller->Session->write($controller->name.'.'.$controller->params['action'].$this->identifier, $controller->data);
        }
        $filter = $controller->Session->read($controller->name.'.'.$controller->params['action'].$this->identifier);
        $controller->data = $filter;
    }
}

How to use it:
1. Add this code into filter.php file under your /app/controllers/components
2. Add in your controllers which require filtering:

<?php
class CountriesController extends AppController {

    var $name = 'Countries';
    ...
    var $components = array(...,'Filter');

    function index() {
        $this->Country->recursive = 0;
        $filter = $this->Filter->process($this);
        $this->set('countries', $this->paginate(null, $filter));
    }
}
?>

That’s it now your filter forms will keep the values until you delete them or set a specific JavaScript function which would be “clear all fields and submit” – I leave it to your imagination 😉

Note that in the component there is an array which store all wrappers for different type of data, i.e. strings will be transformed with …field LIKE ‘filter_string%’ etc. you can modify this in the component directly, or by setting the custom array before using the prepare function.

Well, that’s it. nothing special, but doing miracles 😉

I would appreciate if you share your approach and ways to filter data.

Share it:
  • Facebook
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • Google Bookmarks
  • Yahoo! Buzz
  • Add to favorites
  • Identi.ca

52 thoughts on “Filtering component for your tables

  1. looks like i finally manage to solve it myself 😀
    $datePicker->picker(‘date_created.1’);
    $datePicker->picker(‘date_created.2’);
    for some reason i cant seems to use date_created.0

    if ( isset($this->params[‘form’][‘search’]) ) {
    isset($this->data[‘Issue’][‘date_created’][‘BETWEEN’]);
    $this->data[‘Issue’][‘date_created’][‘BETWEEN’]=array($this->data[‘Issue’][‘date_created’][1],$this->data[‘Issue’][‘date_created’][2]);
    }

  2. Made some other changes to the filter so that it will work with multiple-select drop down menu.
    add this before foreach:-
    $countloop=1;

    and these below the Between case:-
    elseif($countloop==1){ $ret[$model.’.’.$field]=$value; $countloop=2;//this is to stop any unnecessary looping.
    }

    the unnecessary looping occurs when i dont choose to filter out the dates. with this it will only loop once.

Leave a Reply

Your email address will not be published. Required fields are marked *