Duplicate column names conflicting even when using table aliases

gastongr commented 8 years ago

Hello @zepernick , just wanted to report the following problem:

I have two tables, 'products' and 'product_types', both having a 'name' column. I want to display the products table with the product type, so i joined both tables in products.product_type_id=product_types.id

In my Model:

    public function joinArray(){
        return array(
                'product_types pt|left outer' => 'pt.id = p.product_type_id',
        );
    }

    public function fromTableStr() {
        return 'products p';
    }

The javascript with column names:

        $('#recordsTable').dataTable( {
        processing: true,
        serverSide: true,
        ajax: {
            'url': BASE_URL + '/products/dataTable',
            'type': 'POST'
        },
        columns: [
            {data: 'p.name'},
            {data: 'pt.name'},
            {data: 'p.sale_price'},
            {data: 'p.barcode'}
        ],
    });

The problem is that the name fields are blank and in some cases both are filled with the same data.

In the json response values are wrong, but the db query is right. So i guess the problem is when building the response and i couldn't find how to fix it.

Alternatively i'm adding duplicate field names using appendToSelectStr() since it allows aliases, however it would be nice to get it right.

Please if you can help it will be greatly appreciated :)

Thanks for the great library!

gastongr commented 8 years ago

I changed the column names in the database so that they are all different and it works perfectly. So i can confirm that there is an issue when using duplicate column names even when using table aliases.

leirags commented 7 years ago

I fixit...

line 182(add) : $selectArrayAlias = array();

line 196 (replace) :

$selectArrayAlias[] = $c['data'];
if (isset($c['name'])) {
    $selectArray[] = $c['name'].' '.$c['data'];
} else {
    $selectArray[] = $c['data'];
}

line277(replace) : $allColsArray = array_merge($selectArrayAlias, $customCols);

line433(replace):

if (isset($c['name'])) {
    $searchableColumns[] = $c['name'];
} else 
    $searchableColumns[] = $colName;

Finally: Give a diferent name in "data" and set "name" as column name.

Now the name is the column (maybe table.column), and data is the alias for the column.

See the next example...

------- On JavaScript Call -------

columns: [
            { name : "cust.id" , data: "custid"},
            { name : "cust.name", data: "custname" },
            { name : "country.name", data: "cname" },
            { name : "states.name", data: "sname" },
            { name : "cust.city", data: "custcity" }
            { data : "$.city_state_zip" } //refers to the expression in the \"More Advanced DatatableModel Implementation"
        ]

gastongr commented 7 years ago

Thanks for taking the time to share your fix @leirags !

leirags commented 7 years ago

I make some other options, like set “LIKE” before, after or both in general search.

On Nov 22, 2016, at 6:29 PM, Gastón Greco notifications@github.com<mailto:notifications@github.com> wrote:

Thanks for taking the time to share your fix @leiragshttps://github.com/leirags !

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/zepernick/Codeigniter-DataTables/issues/21#issuecomment-262415271, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ADWF-Z4mhG2ofeBb7Bza-TKKZ41-HWYoks5rA5cAgaJpZM4IBac1.

leirags commented 7 years ago

Well by now, I like to share this changes I will sen to you a code… first I will show you how use

— New Options: setup type of search on each column if you need. Like this

$this->load->library('Datatable', array('model' => 'xcustomers_dt', 'rowIdCol' => 'cust.id'));

$this->datatable->setProtectIdentifiers(true);

$this->datatable->setColumnSearchType('custname', ‘before’); // specific column name, colname LIKE %xxx $this->datatable->setColumnSearchType('cust.name', ‘after’); // specific column name, colname LIKE xxx% $this->datatable->setColumnSearchType('_global_searchtype', 'both’); // — all other columns, colname LIKE %xxx%

$this->datatable->setPreResultCallback(

—————CODE <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /**

The MIT License (MIT) *
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 */ 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;

$CI->load->model($model);

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('error', '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(); $selectArrayAlias = array();

foreach ($f->post_get('columns') as $c) { //log_message('error', 'Column: '. print_r($c,true) ); $columnIdxArray[] = $c['data']; if (substr($c['data'], 0, 1) === '$') { //indicates a column specified in the appendToSelectStr() $customCols[] = $c['data']; continue; } $selectArrayAlias[] = $c['data']; if (isset($c['name'])) { $selectArray[] = $c['name'].' '.$c['data']; } else { $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];
    }
    $this->CI->db->order_by($colName, $o['dir']);
}
```
}

//echo $sqlSelectStr; //log_message('error', 'sqlSelectStr: '.$sqlSelectStr); //log_message('error', 'selectArray: '.print_r($selectArray,true) );

$this->CI->db->select($sqlSelectStr, $this->protectIdentifiers); $whereDebug = $this->sqlJoinsAndWhere(); $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(); } //log_message('error', 'SelectSQL: '.$this->CI->db->last_query() );

//process the results and create the JSON objects $dataArray = array(); / // fix by ariel $selectArrayAlias = array(); foreach($selectArray as $tofixAlias){ $cparts = explode(' ', $tofixAlias); if (count($cparts) > 1) { $selectArrayAlias[] = $cparts[1]; } else { $selectArrayAlias[] = $tofixAlias; } } //-- / //log_message('error', 'selectArrayAlias: '.print_r($selectArrayAlias,true) );

$allColsArray = array_merge($selectArrayAlias, $customCols); //$allColsArray = array_merge($selectArray, $customCols); foreach ($query->result() as $row) { $colObj = array();

//log_message('error', 'row: '.print_r($row,true) ); //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);
                $colObj[$propParts[0]] = $nestedObj;
            } else {
                $colObj[$c] = $this->formatValue($formats, $prop, $row->$prop);
            }
        }

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

    $this->sqlJoinsAndWhere();
    $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;

if ($debug === TRUE) { $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;
    }

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

    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()
{
    $debug = '';
    // this is protected 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($c['name'])) { $searchableColumns[] = $c['name']; } else $searchableColumns[] = $colName; }

        if ($c['search']['value'] !== '') {
            $searchType = $this->getColumnSearchType($colName);
            //log_message('error', '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 . ']');
            $this->CI->db->like($colName, $c['search']['value'], $searchType, $this->protectIdentifiers);
        }
    }

    // put together a global search if specified
    $globSearch = $f->post_get('search');
    if ($globSearch['value'] !== '') {
        $gSearchVal = $globSearch['value'];
        $gSearchVal = $this->CI->db->escape_like_str($gSearchVal);

$searchType = $this->getColumnSearchType('_global_searchtype'); $sqlOr = ''; $op = ''; foreach ($searchableColumns as $c) { if ($c!=='') { if ($searchType == 'both') { $sqlOr .= $op . $c . " LIKE '%{$gSearchVal}%'"; } else if ($searchType == 'before') { $sqlOr .= $op . $c . " LIKE '%{$gSearchVal}'"; } else if ($searchType == 'after') { $sqlOr .= $op . $c . " LIKE '{$gSearchVal}%'"; } else { $sqlOr .= $op . $c . " = '{$gSearchVal}'"; // none. } $op = ' OR '; } } $this->CI->db->where('(' . $sqlOr . ')'); }

    //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 */ ———————END CODE

On Nov 22, 2016, at 6:29 PM, Gastón Greco notifications@github.com<mailto:notifications@github.com> wrote:

Thanks for taking the time to share your fix @leiragshttps://github.com/leirags !

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/zepernick/Codeigniter-DataTables/issues/21#issuecomment-262415271, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ADWF-Z4mhG2ofeBb7Bza-TKKZ41-HWYoks5rA5cAgaJpZM4IBac1.

zepernick / Codeigniter-DataTables

Duplicate column names conflicting even when using table aliases #21