SQL Conditions - A CakePHP Component Class

“aka: How to add advanced search functionality to your CakePHP Application”

Advanced search functionality is something that I’ve always built into my applications.  Even back in the days of writing CGI/PERL applications, I’ve always had some class or set of functions to help build SQL conditions (aka WHERE expressions) based on user input (GET/POST). When I started to work with CakePHP, I noticed that it lacked this functionality. There was no “cake way” of defining advanced search parameters. Luckily CakePHP makes up for it by being a great MVC! There is a way to input conditions into the paginate/find methods, but you have to define the conditions yourself. So, I decide to take some old PHP search code and rewrite it to build the conditions based on search criteria. Hence, this sql_conditions.php component class was born.

A CakePHP Component Class for Management of Search Conditions

This class is used to define complex conditions arrays for use in CakePHP model find/paginate methods. It provides an easy way to “map” conditions from input form fields (GET/POST) to database field(s). This is a common task involved in creating advanced search forms. This class also saves the defined criteria in Session for retrieval.

Advanced Search Example – CakePHP Blog:

Let’s take the CakePHP Blog Tutorial as an example. Make sure you complete the Blog Tutorial before proceeding.

1. First download, extract and copy this component (sql_conditions.php) to your app/controllers/components/ directory.

Download sql_conditions.1.0a.zip

2. Open your posts_controller.php and add the following $component (SqlConditions) and search criteria variables to your controller class:

var $components = array(
    'SqlConditions' => array(
        'conditions' => 'criteria_conditions',
        'admin_conditions' => 'admin_criteria_conditions',
           'active_actions' => array('index', 'admin_index'),
       ),
   );
// For use with SqlConditions component.
// Regular Site Search Conditions
var $criteria_conditions = array(
    array('name' => 'search_keywords', 'fields' => array('Post.title','Post.body')),
    array('name' => 'search_min_created', 'fields' => 'Post.created', 'type' => 'date', 'condition' => '>'),
);
// Admin Search Conditions
var $admin_criteria_conditions = array(
    array('name' => 'admin_keywords', 'fields' => array('Post.title','Post.body')),
    array('name' => 'admin_min_created', 'fields' => 'Post.created', 'type' => 'date', 'condition' => '>'),
);

What we’re doing with this code is defining how the component should be used with this controller. This is only a very simple example! There are two criteria arrays to define; one for non-admin actions and one for admin actions. You can call the criteria variables whatever you like, just make sure to set ‘conditions’ and ‘admin_conditions’ to the variable names when calling the component. Also, you can set ‘active_actions’ if you need to use this component in actions other than the default two: ‘index’ and ‘admin_index’. Typically, the ‘active_actions’ setting is not needed.

A quick note on criteria naming conventions. I like to prefix my non-admin type criteria names with ‘search_’ and prefix my admin type criteria names with ‘admin_’. This helps to differentiate the two types of criteria conditions as well as “help prevent” any conflicts with might occur with other named parameters.

The criteria arrays can be made up of many different criteria which are applied to build the conditional array. Each criteria contains several parameters. The criteria supports comparison against multiple fields, such as the ‘search_keywords’ criteria above which will be compared against ‘Post.title’ or ‘Post.body’. This example would allow you to search for keywords in the title “or” body of the posts. Input can be compared as a string, a number or even as a date.

Criteria Parameters: ‘name’, ‘fields’, ‘condition’, ‘type’
- name: variable name to be used in the advanced search Forms or URLs.
- field name(s): field(s) to be use for comparison (array or ,;| separated)
- type of comparison: (default: string)
numeric – compared as a number
date – compare as a date
string – compare as a string
string_equal – check for exact string (Ex: Category Name)
- condition: logical comparison operator
examples: <, <=, =, <=>, >= or > (default: =)
- boundary: Set to true for boundary word matching (default: false).

3. Next open your default layout: app/views/layouts/default.ctp and add the following two simple forms just after id=”content” DIV; or in your content section. You will probably want to move this to a custom CakePHP element later.

echo $form->create('Post', array('action' => 'index', 'class' => 'advanced_search'));
echo $form->input('search_keywords', array('default' => @$this->params['named']['search_keywords'],'div' => false));
echo $form->submit('Go!', array('div' => false,));
echo $form->end();

echo $form->create('Post', array('action' => 'index', 'class' => 'advanced_search'));
echo $form->input('clear_conditions', array('type' => 'hidden', 'value' => 1, 'div' => false,));
echo $form->submit('Reset', array('div' => false,));
echo $form->end();

The first form is used to perform the keyword search. The second form is to clear the search criteria from the session. Note, you can also search using a URL with named parameters. Please see the CakePHP documentation for more information on using named parameters.

4. Last, we call the build method and then add the returned SQL Conditions array to the index action’s paginate call.

function index() {
    // SQL Conditions Component (Criteria)
    $sql_conditions = $this->SqlConditions->build();
    $this->Post->recursive = 0;
    $this->set('posts', $this->paginate($sql_conditions));
}

This is where we actually build out the SQL conditions array to be used. All the work is done for you based on the criteria set and named parameters input. Note, you may wish to use debug($sql_conditions) to view the resulting array to gain a better understanding of this method.

5. That’s it. Test out your new advanced keyword search for your blog entries! Also, try to use the ‘search_min_created’ criteria to search by minimum date created.

Final thoughts: Use at your own risk. While some of this code logic has been around for years, this component has not been used in a production environment. Also, feel free to post any comments and/or bugs you may wish to come across.

Cheers,
Jeff Walters