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 
/
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:
OR
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:
Ok, explaining the simple solution and the bad consequences here is the ultimate helper for this – the filter component:
/**
* 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:
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.
Nik Chankov is a web developer, with experience in various fields and frameworks. He has 10+ years of experience in web development. Currently working in his own company 
Loading ...
well, thank you again.
little remark, for thouse which allready startet with filter story, and alread have a form.
// needed an hour to find
in view
> $form->create(‘Country’, array(‘action’=>’/index’));
where ‘Country’ is a correct Model name you using.
so FilterComponent can find needed Fieldstype.
I didn’t understand you completely, but I mention in the beginning – get the form from your Add action
Sweeeet
is there any way to store the sort and direction variable from $paginate as well? This seems to get lost after ie deleting or editing an entry.
Thanks a lot mate
wirtsi
Wirtsi, great idea and I think it’s easy to be implemented. I am going to work on it these days.
Nice concept – will be more automagical if you create a beforeFilter instead of process and set filter directly from your component into the controller. All that the user will need to do then would be $this->paginate(null, $this->filter)
Tarique Sani – Good idea but, it would be more complex, because in the beforeFilter function you need to determine the index submission, from insert or edit submit. Otherwise it’s true, if you set it in beforeFilter function in AppController it will be very elegant
Determining index submission is easy – $this->action has the name of the current action
with multiply select box did’t work =(
Hello, I’ve changed the returned array this way:
if(isset($this->fieldFormatting[$columns[$k]])){
$ret[$key.'.'.$k] = sprintf($this->fieldFormatting[$columns[$k]], $v);
} else {
$ret[$key.'.'.$k] = $v;
}
And
var $fieldFormatting = array(
“string”=>”LIKE %%%s%%”,
“date”=>”‘%s’”
);
because I got an sql error in some case (colname ambiguos). Now the query is something like
(single field filter, using latest core version -7008- from svn)
.. WHERE ‘ModelName.colname’ LIKE ‘%enteredstring%’
instead of
.. WHERE ‘colname’ LIKE ‘enteredstring%’
I don’t know if it is always correct this way, but it works for me
anyone getting:
Warning: Call-time pass-by-reference has been deprecated – argument passed by value; If you would like to pass it by reference, modify the declaration of [runtime function name](). If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file. However, future versions may not support this any longer. in /home/htdocs/app/controllers/components/filter.php on line 38
in function process(&$controller) {
line38: $this->_prepareFilter(&$controller);
then it doesn’t work…
This is great. I’ve changed the code slightly, now it also supports associations with a different name:
function process(&$controller){
$this->_prepareFilter(&$controller);
$controllerModelName = Inflector::singularize($controller->name);
$controllerModel = $controller->{$controllerModelName};
$associated = $controllerModel->getAssociated();
$ret = array();
if(isset($controller->data)){
//Loop for models
foreach($controller->data as $key=>$value){
$modelName = $key;
if(!isset($controllerModel->{$key}) && isset($associated[$key])){
$assoc = $associated[$key];
$modelName = $controllerModel->{$assoc}[$key]['className'];
}
if(isset($controllerModel->{$modelName})){
$columns = $controllerModel->{$modelName}->getColumnTypes();
foreach($value as $k=>$v){
if($v != ''){
//Check if there are some fieldFormatting set
if(isset($this->fieldFormatting[$columns[$k]])){
$ret[$key.'.'.$k] = sprintf($this->fieldFormatting[$columns[$k]], $v);
} else {
$ret[$key.'.'.$k] = $v;
}
}
}
//unsetting the empty forms
if(count($value) == 0){
unset($controller->data[$key]);
}
}
}
}
return $ret;
}
</pre>
Really great component! I have made some changes so I don’t have to put filter data into session. You can see my code at http://blog.uplevel.pl/index.php/2008/06/cakephp-12-filter-component/ . Thanks a lot!
Thanks all for your comments
I personally prefer to have filter stored in a Session, butcause it’s “stored” until you leave the browser, or you logged out.
@Maciej – nice approach, but It’s a matter of taste.
I am glad that this helps
thanks youu.
Just a tiny change to let the component work with cake 1.2 RC – (move the operator to the “key” side)
$this->_prepareFilter($controller);
$ret = array();
if(isset($controller->data)){
//Loop for models
foreach($controller->data as $key=>$value){
if(isset($controller->{$key})){
$columns = $controller->{$key}->getColumnTypes();
foreach($value as $k=>$v){
if($v != ''){
//Check if there are some fieldFormatting set
if(isset($this->fieldFormatting[$columns[$k]])){
$ret[$key.'.'.$k .' LIKE '] = sprintf($this->fieldFormatting[$columns[$k]], $v);
} else {
$ret[$key.'.'.$k .' LIKE '] = $v;
}
}
}
//unsetting the empty forms
if(count($value) == 0){
unset($controller->data[$key]);
}
}
}
}
return $ret;
}
@Stefano – thanks! I haven’t used this component with 1.2RC so far. I will change in the core right now.
Not returning correct result. Due to `$fieldFormatting` has “LIKE” defined in it, then again in `process()`:
$ret[$key.'.'.$k .' LIKE '] = sprintf($this->fieldFormatting[$columns[$k]], $v);
So the resulting query is (in my example):
WHERE `Photographer`.`first_name` LIKE ‘LIKE Bre%’
Yeah, that’s wrong …
Small, easy fix.
@Chris,
Except it doesn’t retain the current controller model’s fields … if the current controller is dealing with ModelA that has an association to ModelB … your code seems to setup ModelB’s stuff, but forgets about ModelA’s.
Nik, You said this:
‘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’
BUT, my imagination is NOT very GOOD
Can you (or someone) post some code that will reset the session variables (or another way) to remove the filter that can be assigned to a separate button.
Johnny,
Clearing the filter is simply submit an empty form. If the form has values in the text fields you need to go and manually delete their values. Of course if you have a javascript function attached to onclick event to a button could reset them very easy.
Here is a simple example with 2 text fields could be:
document.getElementById('field1').value = '';
document.getElementById('field2').value = '';
form.submit();
}
after this you attach this onclick event like:
Of course, it could be more elegant and common including reset of select etc, but this one is a quick and dirty example.
I built a reporting system… It’s quite different. It does not use sessions, however I do need to rewrite and optimize it for the changes in RC3..
https://sites.google.com/a/esninteractive.com/cakephp-documentation/extensions/cake-reporting-filtering-extension
-d
I built a reporting system… It’s quite different. It does not use sessions, however I do need to
http://www.turkyoutube.org/populer.php
does anyone have some nice examples of this being used?
Hello Nik,
Is it possible to generate a query something like “(Field1 like %blah% OR Field2 like %blah% OR Field3 like %blah% OR …) ” using your filter component? What should be changed?
Regards,
Dmitry
Hi Dmitry,
I’ve just updated the component’s code. And now it’s possible.
Basically your inputs need to have names in the following format:
$this->Form->input(‘OR.ModelName.field_name’)
and all the fields with prefix OR will be used in the (field1 OR field2 …)
Hello Nik,
I think the change doesn’t work.
It should be something like
$pos = strrpos($model, “OR”);
if($pos > 0)
instead of
if($model == ‘OR’) should be replace with
But also the code doesn’t work in my case.
In the form I have
Form->input(‘OR.View1.field1′, array(‘label’ => ‘label’));?>
Form->input(‘field2′, array(‘type’ => ‘hidden’, ‘value’ => ’1′));?>
In this case the the select statement has only
OR.View1.field2 LIKE ’1%’
If I change to
Form->input(‘OR.View1.field2′, array(‘type’ => ‘hidden’, ‘value’ => ’1′));?>
the sql statment has just WHERE 1 = 1
If I have
Form->input(‘field1′, array(‘label’ => ‘label’));?>
Form->input(‘field2′, array(‘type’ => ‘hidden’, ‘value’ => ’1′));?>
the sql statement has
View1.field1 LIKE ‘INPUT%’ AND View1.field2 LIKE ’1%’
the next question is how to replace automatically View1.field2 LIKE ’1%’ with View1.field2 LIKE ‘INPUT%’
Actually it’s not a filter component anymore, it’s more full search component.
Dmitry,
Use this:
$this->Form->input(‘OR.View1.field1?, array(‘label’ => ‘label’));
$this->Form->input(‘OR.View1.field2?, array(‘type’ => ‘hidden’, ‘value’ => ’1?));
This way you will have where condition like
(View1.field1=’something%’ OR View1.field2=’other thing%’)
If you have something like:
$this->Form->input(‘OR.View1.field1?, array(‘label’ => ‘label’));
$this->Form->input(‘OR.View1.field2?, array(‘type’ => ‘hidden’, ‘value’ => ’1?));
$this->Form->input(‘View1.field3?, array(‘label’ => ‘label’)); //Note there is no OR
the where clause will be:
(View1.field1=’something%’ OR View1.field2=’other thing%’) AND View1.field3=’third field%’
I’ve just checked it and it is generated the correct where condition.
Hi Nik!
there are two issues here.
1. I used the changed component with an Oracle view which has many joined tables. I used also the component with a Table and in the model of the table has $belongsTo. The Result is if you have in the form just “OR.Table.Field1″ and “OR.Table.Field2″ it’s working fine.
But If you have in the Form
“OR.Table.Field1″
“OR.BelongsToTable.Field2″
then it doesn’t work:
Form->input(‘OR.Table1.name’);?>
Form->input(‘OR.BelongsToTable.name’,array(‘label’ => ‘label’, ‘type’ => ‘hidden’, ‘value’ => ’1′));?>
2. As you see I use ‘type’ => ‘hidden’, ‘value’ => ’1′. I want to have just one visible input field on the form. If user types “WORD” in this input field I want to get a statement:
select field1, field2, … from Table where field1 like ‘%WORD%’ OR field2 LIKE ‘%WORD%’ OR …
All fields which should be user in where clause are in the Form and just one of them is visible, all ohter fields are hidden and have default value=1 to be found by the component.
What should be done with the component to replace the default ’1′ to the ‘%WORD%’ for string datatypes?
For integer it could be “= WORD”.
Dmitry,
About the belongsTo relation, it’s not written, but the syntax of the fields should be:
$this->Form->input(‘OR.View1.BelongsToTable.field1′);
$this->Form->input(‘OR.View1.BelongsToTable.field2?’);
$this->Form->input(‘OR.View1.BelongsToTable.BelongsToBelongsToTable.field3?’);
btw I am not sure if it will work with OR, but let’s say 99% I am convinced that it would work
i.e. if you have relation Vew1->Vew2->Vew3 you can filter the Vew3 by
Vew1.Vew2.Vew3.field_name (it depends if there are such table in the constructed SQL otherwise theoretically it should work no matter how many levels are used.
About the second question. I would do it by leaving in the form only one field i.e. search_value (even not in the model).
Then in the controller, just before using the filter do distribution of the search_value to the all required fields i.e.
if(!empty($this->data) && $this->data['search_value']){
$this->data['Model1']['field1'] = $this->data['search_value'];
$this->data['Model1']['Model2'] = $this->data['search_value'];
… etc …
}
this will do the trick. Finally you can store the search_value in the session too, so on refresh the field to be populated too. Probably this could be wrapped in a component too
Nik,
thank you for your support! The first part works fine! About the second question, I don’t catch you, I’m just starting to use cakephp
Did you mean following:
Form:
Form->create(‘Model’,array(‘action’=>’/index’));?>
Form->input(‘search_value’);?>
Controller:
function index() {
$this->Model->recursive = 0;
if(!empty($this->data) && $this->data['search_value']){
$this->data['Model']['field1'] = $this->data['search_value'];
$this->data['Model']['field2'] = $this->data['search_value'];
$this->data['Model']['ModelBelongsTo']['field3'] = $this->data['search_value'];
}
$filter = $this->Filter->process($this);
$this->set(‘Table’, $this->paginate(null,$filter));
}
Yep that’s what I meant.
SQL Error: ORA-00904: “Model”.”SEARCH_VALUE”: invalid identifier
“search_value” will be seen as a part of the model but it’s not a part…
I don’t know what syntax is the proper syntax but I think it should something like
$this->Form->create(‘null’,array(‘action’=>’/index’));
When setting the other fields do:
unset($this->data['Model']['search_value']);
Notice (8): Undefined index: search_value [APP/controllers/models_controller.php, line 10]
String 10 is “if(!empty($this->data) && $this->data['search_value']){”
Notice (8): Undefined index: search_value [APP/controllers/components/filter.php, line 62]
String 62 is “if(isset($this->fieldFormatting[$columns[$field]])){“
Ok.
It should be:
if(!empty($this->data['Model']['search_value']) && $this->data['Model']['search_value']){
$this->data['Model']['field1'] = $this->data['Model']['search_value'];
$this->data['Model']['field2'] = $this->data['Model']['search_value'];
unset($this->data['Model']['search_value']);
}
Hi Nik,
I have one more question about the $fieldFormatting. It’s concerning case insensitive search.
I would like to have something like
select ‘TEST’ from dual
where (LOWER(‘sera’) LIKE ‘%’ || LOWER(‘SERA’) || ‘%’);
How should I change the $fieldFormatting?
It should be something like
“string”=>array(“LOWER(%1\$s) LIKE “, “%%% ||| LOWER(2\$s) || %%”),
Do you know other solution for the case insensitive search?
Dmitry,
Doing following format I believe it’s enough:
$this->Filter->fieldFormatting["string"]=array(“LOWER(%1\$s) LIKE”, “LOWER(%2\$s%%)”);
This way either the field and string will be lower cased and this will be pretty much enough.
If this doesn’t working (the second part of the array is quoted) try
$this->Filter->fieldFormatting["string"]=array(“LOWER(%1\$s) LIKE LOWER(%2\$s%%)”);
If you don’t want to touch the component you can add this code in the AppController::beforeFilter() otherwise just replace the variable in the Filter component.
Hi Nik,
nothing of them works.
I would save the types “string”, “text”, “date”, “datetime”.
Even if I use
var $fieldFormatting = array(
array(“LOWER(%1\$s) LIKE LOWER(%2\$s%%)”)
);
I get
((FIELD = ‘VALUE’) OR …
Lower will not be translated prerly.
I’m almoust at the goal, if you write
“string”=>array(“LOWER(%1\$s) LIKE LOWER”, “(%%%2\$s%%)”),
you will get
((LOWER(FIELD) LIKE LOWER ‘(%VALUE%)’) OR …
Only the last “)” is wrong, I don’t know how to fix it.
please try “string”=>array(“LOWER(%1\$s) LIKE LOWER(“, “%%%2\$s%%)”),
it has just one problem :
%)’)
Dmitry,
bacause this component is written for MySQL probaby that’s why with Oracle it doesn’t work properly. I mean data types. Probably you have to check what kind of columns are returned from the table and modify the array keys (“string”, “text”, “date”, “datetime”) and to change them in the $fieldFormatting array.
About the second thing – Cake wraps the value of the string with quotes (especially if it’s a text). That’s why use:
“string”=>array(“LOWER(%1\$s) LIKE LOWER(%%%2\$s%%)”),
note that now everything is array node rather the key=>value pair – “,” are removed.
It’s not the oracle syntax problem it’s a wrong translation of the string:
If I use
“string”=>array(“LOWER(%1\$s) LIKE LOWER(%%%2\$s%%)”),
I get
WHERE ((LOWER(FIELD) LIKE LOWER(%VALUE%) ”) OR …
the proper version would be
WHERE ((LOWER(FIELD) LIKE LOWER(‘%VALUE%’)) OR …
I’ve changed the component (please get the new version of it from the post). Basically the change is in generateCondition() function.
Now it will accept
“string”=>array(“LOWER(%1\$s) LIKE LOWER(‘%%%2\$s%%’)”),
BTW, thanks for pointing this out. it’s nice addition to the component.
Hi Nik!
Cool! It’s working! Thanx a lot!!!!
Best regards,
Dmitry