Friday 5 August 2011

CakePHP The Query by Example Component

After the previous post, regarding how to create and use a input QBE form, here is my simple QBE component.

To use it, paste the following code in a file named qbe.php into your APP/controllers/components directory.

<?php
/**
 * @class QbeComponent
 * Convert posted data entered in a pseudo Query by Example fashion
 * from a CakePHP Form into Model::find() acceptable conditions.
 *
 * @author: Thanassis Bakalidis
 * @version: 1.0
 */
class QbeComponent extends Object {
    // sesion keys for saving and retrieving controller data
    const CONDITIONS_SESSION_KEY = 'SRCH_COND';
    const FORM_DATA_SESSION_KEY = 'SRCH_DATA';

    // supported SQL operators
    private $SQL_OPERATORS = array(
        'IN', '<>', '>=', '<=',
        '>', '<'
    );

    var $owner;     // the controller using the component

    /**
     * @name initialize
     * The initialize method is called before the controller's
     * beforeFilter method.
     */
    function initialize(&$controller, $settings=array())
    {
        $this->owner =& $controller;
    }

    /**
     * @name: getSearchConditions()
     * Return an array to be used as search conditions in a find
     * based on the controller's current data
     * @param : string $modelName name of the model to search controller data
     * @version: 1.3
     */
    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->owner->name,
                                $modelName
                            );
        $sessionDataKey = sprintf("%s-%s-%s",
                                self::FORM_DATA_SESSION_KEY,
                                $this->owner->name,
                                $modelName
                            );

        if (empty($this->owner->data)) {
            // attempt to read conditions from sesion
            $conditions = $this->owner->Session->check($sessionConditionsKey)
                ? $this->owner->Session->read($sessionConditionsKey)
                : array();
            $this->owner->data = $this->owner->Session->check($sessionDataKey)
                ? $this->owner->Session->read($sessionDataKey)
                : array();
        } else {
            // we have posted data. Atempt to rebuild conditons
            // array
            $conditions = array();
            foreach( $this->owner->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, remove any leading and trailing blanks
                    $value = trim($value);                          
                    // and 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->owner->Session->write($sessionConditionsKey, $conditions);
            $this->owner->Session->write($sessionDataKey, $this->owner->data);
        }

        return $conditions;
    }

    private function extractOperator(&$input)
    {
        if (is_array($input))
            return '';

        $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;
    }
}

Next modify your AppController to use the component by adding 'Qbe' to the $components array. An example would be:

    var $components = array(
        'RequestHandler',
        'Auth',
        'Session',
        'Qbe'
    );

Now crate a query form before the results table into your index view. Copying the form from the corresponding model's create or edit pages is usually enough to get you started.

Finally, modify the corresponding controller method to use the posted data in order to create search conditions. A typical example for a model named Product would be something like that:

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

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

The prepareCombos() method is a simple private function I created by copying all the find)'list', ...) commands that the cake bake script created after the add() and edit() controller methods, so that all foreign key fields have correct value ranges, when the input form gets displayed. For more information and example code, see the previous post which presents the same functionality, by adding code to the AppController.

No comments :