CakePHP Filter Component

For a recent project I've been working on the index pages required the data to be both sortable and filterable. Luckily CakePHP comes with the Pagination class which covers the directional sorting of data and I went looking online for something that will filter the data.

I'm going to be using the most upto date version of CakePHP which at this time is 1.2.3.8166

Existing Solutions

I found two existing components that deal with filtering data, the second one I found builds upon the first and I've taken the solution and altered it slightly so that it's a little bit more efficient code wise and also to get my head around how it works. These can be found here and here.

Filter Component

Here is the filter component in its entirety, the component is quite complex but it basically loops through the Controller's filter data and builds up a number of SQL where clauses that will be used to filter the data. Filtering works on both the current Model and also to related Models that use the "belongsTo" association.

<?php
// file: /app/controllers/components/filter.php
/**
 * Filter component
 *
 * @original concept by Nik Chankov - http://nik.chankov.net
 * @modified and extended by Maciej Grajcarek - http://blog.uplevel.pl
 * @modified again by James Fairhurst - http://www.jamesfairhurst.co.uk
 * @version 0.1
 */
class FilterComponent extends Object {
    /**
     * fields which will replace the regular syntax in where i.e. field = 'value'
     */
    var $fieldFormatting = array(
		"string"	=> "LIKE '%%%s%%'",
		"text"		=> "LIKE '%%%s%%'",
		"datetime"	=> "LIKE '%%%s%%'"
	);

	/**
	 * Paginator params sent in URL
	 */
   	var $paginatorParams = array(
		'page',
		'sort',
		'direction'
   	);

   	/**
   	 *  Url variable used in paginate helper (array('url'=>$url));
   	 */
   	var $url = '';

    /**
     * Function which will change controller->data array
     * @param object $controller the class of the controller which call this component
     * @param array $whiteList contains list of allowed filter attributes
     * @access public
     */
	function process($controller, $whiteList = null){
        $controller = $this->_prepareFilter($controller);
        $ret = array();
        if(isset($controller->data)){
            // loop models
            foreach($controller->data as $key=>$value) {
				// get fieldnames from database of model
				$columns = array();
                if(isset($controller->{$key})) {
                    $columns = $controller->{$key}->getColumnTypes();
				} elseif (isset($controller->{$controller->modelClass}->belongsTo[$key])) {
                    $columns = $controller->{$controller->modelClass}->{$key}->getColumnTypes();
				}

				// if columns exist
				if(!empty($columns)) {
					// loop through filter data
                    foreach($value as $k=>$v) {
						// JF: deal with datetime filter
						if(is_array($v) && $columns[$k]=='datetime') {
							$v = $this->_prepare_datetime($v);
						}

						// if filter value has been entered
                        if($v != '') {
							// if filter is in whitelist
                        	if(is_array($whiteList) && !in_array($k,$whiteList) ){
                        		continue;
                        	}

							// check if there are some fieldFormatting set
							if(isset($this->fieldFormatting[$columns[$k]])) {
								// insert value into fieldFormatting
								$tmp = sprintf($this->fieldFormatting[$columns[$k]], $v);
								// don't put key.fieldname as array key if a LIKE clause
								if (substr($tmp,0,4)=='LIKE') {
									$ret[] = $key.'.'.$k . " " .  $tmp;
								} else {
									$ret[$key.'.'.$k] = $tmp;
								}
							} else {
								// build up where clause with field and value
								$ret[$key.'.'.$k] = $v;
							}

							// save the filter data for the url
							$this->url .= '/'.$key .'.'.$k.':'.$v;
                        }
                    }

                    //unsetting the empty forms
                    if(count($value) == 0){
                        unset($controller->data[$key]);
                    }
				}
            }
        }

	return $ret;
    }


    /**
     * function which will take care of the storing the filter data and loading after this from the Session
	 * JF: modified to not htmlencode, caused problems with dates e.g. -05-
	 * @param object $controller the class of the controller which call this component
     */
    function _prepareFilter($controller) {
		$filter = array();
        if(isset($controller->data)) {
			//pr($controller);
            foreach($controller->data as $model=>$fields) {
				if(is_array($fields)) {
					foreach($fields as $key=>$field) {
						if($field == '') {
							unset($controller->data[$model][$key]);
						}
					}
				}
            }

            App::import('Sanitize');
            $sanit = new Sanitize();
            $controller->data = $sanit->clean($controller->data, array('encode' => false));
            $filter = $controller->data;
        }

        if (empty($filter)) {
      		$filter = $this->_checkParams($controller);
        }
        $controller->data = $filter;
		
	return $controller;
    }


    /**
     * function which will take care of filters from URL
	 * JF: modified to not encode, caused problems with dates
	 * @param object $controller the class of the controller which call this component
     */
     function _checkParams($controller) {
     	if (empty($controller->params['named'])) {
     		$filter = array();
     	}

        App::import('Sanitize');
        $sanit = new Sanitize();
        $controller->params['named'] = $sanit->clean($controller->params['named'],array('encode' => false));

     	foreach($controller->params['named'] as $field => $value) {
     		if(!in_array($field, $this->paginatorParams)) {
				$fields = explode('.',$field);
				if (sizeof($fields) == 1) {
	     			$filter[$controller->modelClass][$field] = $value;
				} else {
	     			$filter[$fields[0]][$fields[1]] = $value;
				}
     		}
     	}

     	if (!empty($filter))
     		return $filter;
     	else
     		return array();
     }


	/**
	 * Prepares a date array for a Mysql where clause
	 * @author James Fairhurst
	 * @param array $arr
	 * @return string
	 */
	function _prepare_datetime($date) {
		// init
		$str = '';
		// reverse array so that dd-mm-yyyy becomes yyyy-mm-dd
		$date = array_reverse($date);
		// loop through date
		foreach($date as $key=>$value) {
			// if d/m/y has been entered
			if(!empty($value)) {
				// seperate with '-'
				$str .= '-'.$value;
				// remove first '-'
				if($key=='year') {
					$str = str_replace('-', '', $str);
				}
				// only add if day is empty
				if($key=='month' && empty($date['day'])) {
					$str .= '-';
				}
				// add final space
				if($key=='day') {
					$str.=' ';
				}
			}
		}

	return $str;
	}
}

?>

App Controller

The Filter Component is included at the top of the file along with a variables to save the Filter data and a default form options array. The filtering logic will only take place on "index" actions and the url that was created by the component will be saved in the "filter_options" variable. This will be used to keep the filter data when pagination is required.

<?php
// file: /app/app_controller.php

class AppController extends Controller {
	// class variables
	var $_Filter = array();

	// setup components
	var $components = array('Filter');
	// default datetime filter
	var $_Form_options_datetime = array();

	/**
	 * Before any Controller action
	 */
	function beforeFilter() {
		// for index actions
		if($this->action == 'index') {
			// setup filter component
			$this->_Filter = $this->Filter->process($this);
			$url = $this->Filter->url;
			if(empty($url)) {
				$url = '/';
			}
			$this->set('filter_options',array('url'=>array($url)));
			// setup default datetime filter option
			$this->_Form_options_datetime = array('type'=>'date','dateFormat'=>'DMY','empty'=>'-','minYear'=>date("Y")-2,'maxYear'=>date("Y"));
			// reset filters
			if(isset($this->data['reset']) || isset($this->data['cancel'])) {
				$this->redirect(array('action'=>'index'));
			}
		}
	}

	/**
	 * Builds up a selected datetime for the form helper
	 * @param string $fieldname
	 * @return null|string
	 */
	function process_datetime($fieldname) {
		$selected = null;
		if(isset($this->params['named'][$fieldname])) {
			$exploded = explode('-',$this->params['named'][$fieldname]);
			if(!empty($exploded)) {
				$selected = '';
				foreach($exploded as $k=>$e) {
					if(empty($e)) {
						$selected .= (($k==0) ? '0000' : '00');
					} else {
						$selected .= $e;
					}
					if($k!=2) {$selected.='-';}
				}
			}
		}
	return $selected;
	}
}
?>

Your Controller

To use the filter data in your Controller you simply need to pass the data in the $this->paginate() method, if you print out the data you will see an array of conditions that will be used to find specific data from the database.

I've included the datetime options so that you have an idea of how to filter your data by dates. I've had to process the date input so that you can seperately filter by day, month and year or a combination of the three.

// file: app/controllers/your_controller.php

function index() {
	$this->set('date_options_created', array_merge($this->_Form_options_datetime, array('selected'=>$this->process_datetime('Model.created'))));
	$this->set('date_options_modified', array_merge($this->_Form_options_datetime, array('selected'=>$this->process_datetime('Model.modified'))));
	$this->Model->recursive = 0;
	$this->set('data', $this->paginate(null, $this->_Filter));
}

Your Index View

I've included a form input for each column which allows the columns to be filtered. For date inputs I've passed the options from the Controller, this is only because these are quite specific and the processing of these is a little different from normal fields.

// file: /app/views/model/index.ctp

<?php echo $form->create('Model',array('action'=>'index','id'=>'filters')); ?>
<table cellpadding="0" cellspacing="0">
	<thead>
		<tr>
			<th><?php echo $paginator->sort('Name', 'name', $filter_options);?></th>
			<th><?php echo $paginator->sort('Created', 'created', $filter_options);?></th>
			<th><?php echo $paginator->sort('Modified', 'modified', $filter_options);?></th>
			<th class="actions">Actions</th>
		</tr>
		<tr>
			<th><?php echo $form->input('name'); ?></th>
			<th><?php echo $form->input('created', $date_options_created); ?></th>
			<th><?php echo $form->input('modified', $date_options_modified); ?></th>
			<th>
				<button type="submit" name="data[filter]" value="filter">Filter</button>
				<button type="submit" name="data[reset]" value="reset">Reset</button>
			</th>
		</tr>
	</thead>
	<tbody>
		// loop through and display your data
	</tbody>
</table>
<?php echo $form->end(); ?>
<div class="paging">
	<?php echo $paginator->prev('<< '.__('previous', true), $filter_options, null, array('class'=>'disabled'));?>
 | 	<?php echo $paginator->numbers($filter_options);?>
	<?php echo $paginator->next(__('next', true).' >>', $filter_options, null, array('class'=>'disabled'));?>
</div>

Note that I've also passed the filter options to the Paginator links so that the filters will be saved when passing from one page to the next.

Filtering Related Data

To filter related data just change the form inputs slightly to include the related Model name, note that only the belongsTo association is currently supported.

<?php echo $form->input('Model.name'); ?>

Wrapping Up

Theres a lot of code in this article and most of the credit goes to original people who created and extended the Filter Component. This will probably be quite complicated for a beginner but hopefully if you're trying to use this in your application the process explained here will be relatively easy to follow. As usual any questions just leave a comment.

Back to Home Page