CakePHP: Turn an input form into a query form (Version 2)

CakePHP makes constructing input forms easy. The Form helper object's input() methods provides us with the necessary intelligence, to display and get input data from our users, then store it into the controller's $this->data property using the format required by the framework's Model->save() method, so they can eventually be written to the database, with no need for any elaborate code. Furthermore, given the fact that cake's cake script will bake all that standard code in a flash. it becomes a matter of minutes to create a full featured CRUD application, starting from just a good database design.

One thing I find missing from the baked code, is the ability to create input forms whose data will be used as search criteria -- in a QBE fashion -- in an index or display page. My solution requires us to convert the submitted data into a form that is compatible with the Model->find()'s $conditions parameter and then feed this to the Model->find() or the Controller->paginate() methods in order to limit the number of returned items.

As Richard pointed out during the first version of this post, cake offers the postConditions() method, which does this out of the box and the truth of the matter is that I had completely missed that when I started coding. After looking at the CakePHP code and comparing it with my approach, I can only say that with the code below, you don't have to worry about operators and providing complex parameters. The latest version will allow users to type their desired operator before the value in a QBE like fashion. The standard Cake approach gives more accurate control over the entire process, but I am not sure how to create a UI that will allow users to change operators dynamically .

So, to get started with my alternative version, the typical index() method, for a model named Product, using this approach will have to look somehow like this:

    function index()
    {        
        $this->Product->recursive = 0;
        $conditions = $this->getSearchConditions($this->Product->name);
        $products = $this->paginate('Product', $conditions);

        $this->set( 'products', $products);
        $this->prepareCombos();
    }

The important part here s obviously the $this->getSearchConditions() function that will get the controllers $data property and transform it into the $conditions array. The perfect place to put the code for that would be our application's controller.

class AppController extends Controller {
    const CONDITIONS_SESSION_KEY = 'SRCH_COND';
    const FORM_DATA_SESSION_KEY = 'SRCH_DATA';

    private $SQL_OPERATORS = array(
        'IN', '<>', '>=', '<=',
        '>', '<'        
    );

    /**
     * @name: getSearchConditions()
     * @access: protected
     * @author: Thanassis Bakalidis
     * @param : string $modelName name of the model to search controller data
     * Return an array to be used as search conditions in a Model's find 
     * method based on the controller's current data
     * @version: 1.4
     */
    protected function getSearchConditions($modelName = null)
    {
        if ($modelName == null)
            return null;

        // create speciffic keys for the model andcontroller
        $sessionConditionsKey = sprintf("%s-%s-%s",
                                self::CONDITIONS_SESSION_KEY,
                                $this->name,
                                $modelName
                            );
        $sessionDataKey = sprintf("%s-%s-%s",
                                self::FORM_DATA_SESSION_KEY,
                                $this->name,
                                $modelName
                            );

        if (empty($this->data)) {
            // attempt to read conditions from sesion
            $conditions = $this->Session->check($sessionConditionsKey)
                ? $this->Session->read($sessionConditionsKey)
                : array();
            $this->data = $this->Session->check($sessionDataKey)
                ? $this->Session->read($sessionDataKey)
                : array();
        } else {
            // we have posted data. Atempt to rebuild conditons
            // array
            $conditions = array();
            foreach( $this->data[$modelName] as $key => $value) {
                if (empty($value))
                    continue;

                $operator = $this->extractOperator($value);
                
                if (is_array($value)) {
                    // this can only be a date field

                    $month = $value['month'];
                    $day = $value['day'];
                    $year = $value['year'];

                    // We want all three variables to be numeric so we 'll check their
                    // concatenation. After all PHP numbers as just strings with digits
                    if (is_numeric($month.$day.$year) && checkdate( $month, $day, $year)) {
                        $conditionsKey ="$modelName.$key";
                        $conditionsValue = "$year-$month-$day";
                    } else
                        continue;                        
                } else {
                    // we have normal input check the operator given                    
                    if ($operator == '' && !is_numeric($value)) {
                        // turn '=' to 'LIKE' for non numeric data
                        // numeric data will be treated as if they
                        // have an wquals operator
                        $operator = 'LIKE';
                        $value = str_replace('*', '%',  $value);
                        $value = str_replace('?', '.',  $value);                        
                    } else if ($operator === 'IN') {
                        // we need to convert the input string to an aray
                        // of the designated values
                        $operator = '';
                        $value = array_filter(explode( ' ', $value));
                    } 
                    
                    $conditionsValue = $value;
                    $conditionsKey = "$modelName.$key $operator";                    
                }

                // add the new condition entry
                $conditions[trim($conditionsKey)] = $conditionsValue;
            }

            // if we have some criteria, add them in the sesion
            $this->Session->write($sessionConditionsKey, $conditions);
            $this->Session->write($sessionDataKey, $this->data);
        }

        return $conditions;
    }

    private function extractOperator(&$input)
    {     
        $operator = strtoupper((strtok($input, ' '));
        
        if (in_array($operator, $this->SQL_OPERATORS)) {
            $opLength = strlen($operator);
            $inputLength = strlen($input);
            $input = trim(substr( $input, $opLength, $inputLength - $opLength));                        
        } else {            
            $operator = '';
        }
        
        return $operator;
    }

This was the hard part. The next ones have to do with only copy and paste. So get the form that cake created from your add or edit views and copy it before the table in the index view page. You may possibly wish to remove some unwanted input fields and change the value of the $this->Form->end() function's parameter from "Submit" to "Search".

So we are almost there. One last stroke and we are ready. Remember the $this->prepareCombos(); last comand on the index action? Well, that is a simple private method I created by copying all the find)'list', ...) commands that the cake bake script created after the add() and edit() methods, so that all foreign key fields have correct value ranges, when the input form gets displayed. For my product's controller for example the function looks like this:

    private function prepareCombos()
    {
        $productTypes = $this->Product->ProductType->find('list');
        $productCategories = $this->Product->ProductCategory->find('list');
        $suppliers = $this->Product->Supplier->find(
                                                'list',
                                                array(
                                                    'order' => array(
                                                        'id' => 'asc'
                                                    )
                                                )
                                            );
        $qualities = $this->Product->Quality->find('list');        
        $this->set(compact('productTypes', 'productCategories', 'suppliers', 'qualities'));
    }

Notes

• In case your model contains validation rules like range then the default input element that the form helper creates , will contain an appropriate maxlength attribute, that will not allow your users to type more than the maximum allowed characters for the field. Say, for example that you have a rule for a field named width, that looks like this

         'width' => array (
              'rule' => array('range', 1, 999),
              'message' => 'Width must be between 1 and 999 mm',
              'last' => true
         )
        

  ... then the actual HTML input element will have a maxlength attribute of 3. This is not going to make your query UI very usable, so make sure that the search form's echo $this->Form->input( ... statement provides an appropriate hardcoded value for maxlength.
• The next thing to consider, is what will happen when our users provide wrong inputs, say they type =< 13 instead of >= 13. In that case the code -- as it is now -- will create an SQL statement of the form select ... where Model.Field LIKE '=< 13' , that will provide an empty result set, but is still valid SQL. Any workaround for this type of situation would make our code even more complicated than it is now, so I am not going to spend any more thought on this.
• After going this far, I believe that the next reasonable thing would be to move the functionality into a new component, that will take away all the complexity from the AppController class, but this is probably going to be the subject of a forthcoming post.