Missing HAVING clauses for Column Aliases - Fixed (with full code).

[syosoft]

Had to rework sqlJoinsAndWhere method to work with alias's properly. Probably a few other changes that I don't recall.

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
 * The MIT License (MIT)
 *
 * Copyright (c) 2015 Paul Zepernick
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 */

/**
 * Codeigniter Datatable library
 *
 *
 * @author Paul Zepernick
 * @author Sean Benoit (5/10/2016)
 */
class Datatable
{

    private static $VALID_MATCH_TYPES = array('before', 'after', 'both', 'none');

    private $model;

    private $CI;

    private $rowIdCol;

    private $preResultFunc = FALSE;

    // assoc. array.  key is column name being passed from the DataTables data property and value is before, after, both, none
    private $matchType = array();

    private $protectIdentifiers = FALSE;


    /**
     * @params
     *        Associative array.  Expecting key "model" and the value name of the model to load
     */
    public function __construct($params)
    {
        $CI =& get_instance();

        if (isset($params['model']) === FALSE) {
            throw new \Exception('Expected a parameter named "model".');
        }

        $model = $params['model'];

        $this->rowIdCol = isset($params['rowIdCol']) ? $params['rowIdCol'] : NULL;

        try {
            try {
                $CI->load->model($model);
            } catch(\Exception $ex) {
                # Specific to my cfg
                $CI->$model = new $model();
            };
        } catch(\Exception $ex) {
            throw new \Exception($ex->getMessage());
        }

        if (($CI->$model instanceof DatatableModel) === false) {
            throw new \Exception('Model must implement the DatatableModel Interface');
        }

        //even though $model is a String php looks at the String value and finds the property
        //by that name.  Hence the $ when that would not normally be there for a property
        $this->model = $CI->$model;
        $this->CI = $CI;

    }


    /**
     * Turn on/off protect identifiers from the Query Builder
     *
     * @param $boolProtect should database identifiers be protected?
     * @return $this
     */
    public function setProtectIdentifiers($boolProtect)
    {
        $this->protectIdentifiers = $boolProtect;
        return $this;
    }

    /**
     * Register a function that will fire after the JSON object is put together
     * in the library, but before sending it to the browser.  The function should accept 1 parameter
     * for the JSON object which is stored as associated array.
     *
     * IMPORTANT: Make sure to add a & in front of the parameter to get a reference of the Array,otherwise
     * your changes will not be picked up by the library
     *
     *        function(&$json) {
     *            //do some work and add to the json if you wish.
     *        }
     */
    public function setPreResultCallback($func)
    {
        if (is_object($func) === FALSE || ($func instanceof Closure) === FALSE) {
            throw new Exception('Expected Anonymous Function Parameter Not Received');
        }

        $this->preResultFunc = $func;

        return $this;
    }


    /**
     * Sets the wildcard matching to be a done on a specific column in the search
     *
     * @param col
     *        column sepcified in the DataTables "data" property
     * @param type
     *        Type of wildcard search before, after, both, none.  Default is after if not specified for a column.
     * @return    Datatable
     */
    public function setColumnSearchType($col, $type)
    {
        $type = trim(strtolower($type));
        //make sure we have a valid type
        if (in_array($type, self:: $VALID_MATCH_TYPES) === FALSE) {
            throw new Exception('[' . $type . '] is not a valid type.  Must Use: ' . implode(', ', self:: $VALID_MATCH_TYPES));
        }

        $this->matchType[$col] = $type;

        //  log_message('info', 'setColumnSearchType() ' . var_export($this -> matchType, TRUE));

        return $this;
    }

    /**
     * Get the current search type for a column
     *
     * @param col
     *        column sepcified in the DataTables "data" property
     *
     * @return search type string
     */
    public function getColumnSearchType($col)
    {
        //  log_message('info', 'getColumnSearchType() ' . var_export($this -> matchType, TRUE));
        return isset($this->matchType[$col]) ? $this->matchType[$col] : 'after';
    }

    /**
     * @param formats
     *            Associative array.
     *                Key is column name
     *                Value format: percent, currency, date, boolean
     */
    public function datatableJson($formats = array(), $debug = FALSE)
    {

        $f = $this->CI->input;
        $start = (int)$f->post_get('start');
        $limit = (int)$f->post_get('length');


        $jsonArry = array();
        $jsonArry['start'] = $start;
        $jsonArry['limit'] = $limit;
        $jsonArry['draw'] = (int)$f->post_get('draw');
        $jsonArry['recordsTotal'] = 0;
        $jsonArry['recordsFiltered'] = 0;
        $jsonArry['data'] = array();

        //query the data for the records being returned
        $selectArray = array();
        $customCols = array();
        $columnIdxArray = array();

        foreach ($f->post_get('columns') as $c) {
            $columnIdxArray[] = $c['data'];
            if (substr($c['data'], 0, 1) === '$') {
                //indicates a column specified in the appendToSelectStr()
                $customCols[] = $c['data'];
                continue;
            }
            $selectArray[] = $c['data'];
        }
        if ($this->rowIdCol !== NULL && in_array($this->rowIdCol, $selectArray) === FALSE) {
            $selectArray[] = $this->rowIdCol;
        }

        //put the select string together
        $sqlSelectStr = implode(', ', $selectArray);
        $appendStr = $this->model->appendToSelectStr();
        if (is_null($appendStr) === FALSE) {
            foreach ($appendStr as $alias => $sqlExp) {
                $sqlSelectStr .= ', ' . $sqlExp . ' ' . $alias;
            }

        }


        //setup order by
        $customExpArray = is_null($this->model->appendToSelectStr()) ? array() : $this->model->appendToSelectStr();

        foreach ($f->post_get('order') as $o) {
            if ($o['column'] !== '') {
                $colName = $columnIdxArray[$o['column']];

                //handle custom sql expressions/subselects
                if (substr($colName, 0, 2) === '$.') {
                    $aliasKey = substr($colName, 2);

                    if (isset($customExpArray[$aliasKey]) === FALSE) {
                        throw new Exception('Alias[' . $aliasKey . '] Could Not Be Found In appendToSelectStr() Array');
                    }

                    $colName = $customExpArray[$aliasKey];
                    $colName = $aliasKey;
                }

                $this->CI->db->order_by($colName, $o['dir']);
            }
        }

        //echo $sqlSelectStr;

        $this->CI->db->select($sqlSelectStr, $this->protectIdentifiers);
        $whereDebug = $this->sqlJoinsAndWhere('data');
        $this->CI->db->limit($limit, $start);
        $query = $this->CI->db->get();

        $jsonArry = array();

        if (!$query) {
            $jsonArry['errorMessage'] = $this->CI->db->_error_message();
            return $jsonArry;
        }

        if ($debug === TRUE) {
            $jsonArry['debug_sql'] = $this->CI->db->last_query();
        }

        //process the results and create the JSON objects
        $dataArray = array();
        $allColsArray = array_merge($selectArray, $customCols);
        foreach ($query->result() as $row) {
            $colObj = array();
            //loop rows returned by the query
            foreach ($allColsArray as $c) {
                if (trim($c) === '') {
                     continue;
                }

                $propParts = explode('.', $c);

                $prop = trim(end($propParts));
                //loop columns in each row that the grid has requested
                if (count($propParts) > 1) {
                    //nest the objects correctly in the json if the column name includes
                    //the table alias
                    $nestedObj = array();
                    if (isset($colObj[$propParts[0]])) {
                        //check if we alraedy have a object for this alias in the array
                        $nestedObj = $colObj[$propParts[0]];
                    }

                    // $nestedObj[$propParts[1]] = $this->formatValue($formats, $prop, $row->$prop);
                    $nestedObj[$propParts[1]] = $this->formatValue($formats, $c, $row->$prop);
                    $colObj[$propParts[0]] = $nestedObj;
                } else {
                    // $colObj[$c] = $this->formatValue($formats, $prop, $row->$prop);
                    $colObj[$c] = $this->formatValue($formats, $c, $row->$prop);
                }
            }

            if ($this->rowIdCol !== NULL) {
                $tmpRowIdSegments = explode('.', $this->rowIdCol);
                $idCol = trim(end($tmpRowIdSegments));
                $colObj['DT_RowId'] = $row->$idCol;
            }

            $dataArray[] = $colObj;
        }


        $this->sqlJoinsAndWhere('count');
        $totalRecords = $this->CI->db->count_all_results();


        $jsonArry['start'] = $start;
        $jsonArry['limit'] = $limit;
        $jsonArry['draw'] = (int)$f->post_get('draw');
        $jsonArry['recordsTotal'] = $totalRecords;
        $jsonArry['recordsFiltered'] = $totalRecords;
        $jsonArry['data'] = $dataArray;
        //$jsonArry['debug'] = $whereDebug;

        if ($this->preResultFunc !== FALSE) {
            $func = $this->preResultFunc;
            $func($jsonArry);
        }

        return $jsonArry;

    }

    private function formatValue($formats, $column, $value)
    {
        if (isset($formats[$column]) === FALSE || trim($value) == '') {
            return $value;
        }

        if(is_array($formats[$column])) : 
            if(is_object($formats[$column][0])) : 
                $function_name = array_slice($formats[$column], 0, 2);
                $formats[$column] = array_slice($formats[$column], 2);
            else : 
                $function_name = array_shift($formats[$column]);
            endif;

            array_unshift($formats[$column], $value);
            return call_user_func_array($function_name, $formats[$column]);
        else : 
            switch ($formats[$column]) {
                case 'date' :
                    $dtFormats = array('Y-m-d H:i:s', 'Y-m-d');
                    $dt = null;
                    //try to parse the date as 2 different formats
                    foreach ($dtFormats as $f) {
                        $dt = DateTime::createFromFormat($f, $value);
                        if ($dt !== FALSE) {
                            break;
                        }
                    }
                    if ($dt === FALSE) {
                        //neither pattern could parse the date
                        throw new Exception('Could Not Parse To Date For Formatting [' . $value . ']');
                    }
                    return $dt->format('m/d/Y');
                case 'percent' :
                    ///$formatter = new \NumberFormatter('en_US', \NumberFormatter::PERCENT);
                    //return $formatter -> format(floatval($value) * .01);
                    return $value . '%';
                case 'currency' :
                    return '$' . number_format(floatval($value), 2);
                case 'boolean' :
                    $b = filter_var($value, FILTER_VALIDATE_BOOLEAN);
                    return $b ? 'Yes' : 'No';
                default : 
                    if(function_exists($formats[$column])) : 
                        return $formats[$column]($value);
                    endif;
                break;
            }
        endif;

        return $value;
    }



    //specify the joins and where clause for the Active Record. This code is common to
    //fetch the data and get a total record count
    private function sqlJoinsAndWhere($mode='count')
    {
        $debug = '';
        // protect_identifiers is a protected property in CI 3 and can no longer be turned off. must be turned off in the config
        // $this -> CI -> db-> _protect_identifiers = FALSE;
        $this->CI->db->from($this->model->fromTableStr());

        $joins = $this->model->joinArray() === NULL ? array() : $this->model->joinArray();
        foreach ($joins as $table => $on) {
            $joinTypeArray = explode('|', $table);
            $tableName = $joinTypeArray[0];
            $join = 'inner';
            if (count($joinTypeArray) > 1) {
                $join = $joinTypeArray[1];
            }
            $this->CI->db->join($tableName, $on, $join, $this->protectIdentifiers);
        }

        $customExpArray = is_null($this->model->appendToSelectStr()) ? array() : $this->model->appendToSelectStr();

        $f = $this->CI->input;

        $searchableColumns = array();
        foreach ($f->post_get('columns') as $c) {

            $colName = $c['data'];

            if (substr($colName, 0, 2) === '$.') {
                $aliasKey = substr($colName, 2);

                if (isset($customExpArray[$aliasKey]) === FALSE) {
                    throw new Exception('Alias[' . $aliasKey . '] Could Not Be Found In appendToSelectStr() Array');
                }

                $colName = $customExpArray[$aliasKey];
            }

            if ($c['searchable'] !== 'false') {
                if(isset($aliasKey)) : 
                    $searchableColumns[$aliasKey] = $colName;
                else : 
                    $searchableColumns[] = $colName;
                endif;
            }

            if ($c['search']['value'] !== '') {
                $searchType = $this->getColumnSearchType($colName);
                //log_message('info', 'colname[' . $colName . '] searchtype[' . $searchType . ']');
                //handle custom sql expressions/subselects

                $debug .= 'col[' . $c['data'] . '] value[' . $c['search']['value'] . '] ' . PHP_EOL;
                //  log_message('info', 'colname[' . $colName . '] searchtype[' . $searchType . ']');

                if(isset($aliasKey) && 'data' === $mode) : 
                    $this->CI->db->or_having($aliasKey.' LIKE "'.$this->CI->db->escape_like_str($c['search']['value']).'%"');
                else : 
                    $this->CI->db->like($colName, $c['search']['value'], $searchType, $this->protectIdentifiers);
                endif;

                unset($aliasKey);
            }
        }


        // put together a global search if specified
        $globSearch = $f->post_get('search');
        if ($globSearch['value'] !== '') {
            $gSearchVal = $globSearch['value'];
            $sqlOr = '';
            $sqlHaving = '';
            $op = '';

            foreach ($searchableColumns as $aliasKey => $c) {
                if(!is_numeric($aliasKey) && 'data' === $mode) : 
                    $sqlHaving .= $op . $aliasKey . ' LIKE \'' . $this->CI->db->escape_like_str($gSearchVal) . '%\'';
                else :  
                    $sqlOr .= $op . $c . ' LIKE \'' . $this->CI->db->escape_like_str($gSearchVal) . '%\'';
                endif;

                $op = ' OR ';
            }

            if(!empty($sqlOr)) : 
                $this->CI->db->where('(' . $sqlOr . ')');
            endif;

            if(!empty($sqlHaving)) : 
                $this->CI->db->having($sqlHaving);
            endif;
        }


        //append a static where clause to what the user has filtered, if the model tells us to do so
        $wArray = $this->model->whereClauseArray();
        if (is_null($wArray) === FALSE && is_array($wArray) === TRUE && count($wArray) > 0) {
            $this->CI->db->where($wArray, $this->protectIdentifiers);
        }

        return $debug;
    }


}

interface DatatableModel
{

    /**
     * @ return
     *        Expressions / Columns to append to the select created by the Datatable library.
     *        Associative array where the key is the sql alias and the value is the sql expression
     */
    public function appendToSelectStr();

    /**
     * @return
     *      String table name to select from
     */
    public function fromTableStr();

    /**
     * @return
     *     Associative array of joins.  Return NULL or empty array  when not joining
     */
    public function joinArray();

    /**
     *
     * @return
     *    Static where clause to be appended to all search queries.  Return NULL or empty array
     * when not filtering by additional criteria
     */
    public function whereClauseArray();
}

// END Datatable Class
/* End of file Datatable.php */

<?php defined('BASEPATH') OR die('No direct script access allowed');

class User_datatable implements DatatableModel {
     /**
     * @ return
     *        Expressions / Columns to append to the select created by the Datatable library.
     *        Associative array where the key is the sql alias and the value is the sql expression
     */
    public function appendToSelectStr() {
        return array(
            'full_name' => '(select meta.`value` from meta where meta.model = "user" and meta.model_id = user.user_id and meta.`key` = "full_name")',
            'last_login_date' => '(select meta.`value` from meta where meta.model = "user" and meta.model_id = user.user_id and meta.`key` = "last_login_date")',
        );
    }

    /**
     * @return
     *      String table name to select from
     */
    public function fromTableStr() {
        return 'user';
    }

    /**
     * @return
     *     Associative array of joins.  Return NULL or empty array  when not joining
     */
    public function joinArray() {
        return NULL;
    }

    /**
     *
     * @return
     *    Static where clause to be appended to all search queries.  Return NULL or empty array
     * when not filtering by additional criteria
     */
    public function whereClauseArray() {
        return array('user.deleted_date' => NULL);
    }
}

/* End of file user_datatable.php */
/* Location: ./application/models/user_datatable.php */

[gastongr]

Nice idea, i'll test and adopt this too if you don't mind. You could create a PR, i'm sure zepernick will appreciate the contribution.

[syosoft]

Thanks - yea, have at it. I tried to "self document" all of my changes by wrapping them in "if/else/endif" syntax so they would be easier to pickout (at least for the original author).
I'm not sure if there are edge cases that I may have screwed up -- so I'm going to skip on the PR for now. I figured I'd at least document my necessary changes here (having, column alias's, etc) in the hopes they'll help someone use this great library with fewer hurdles.

[syosoft]

Ignore this... See my pull request for an update. The where clause and having clauses didn't mesh. I think the underlying problem was when codeigniter tried to escape my DatatableModel::appendToSelectStr() array values.