Duplicate column names conflicting even when using table aliases

[gastongr]

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]

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]

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]

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

[leirags]

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 <[email protected]mailto:[email protected]> 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//issues/21#issuecomment-262415271, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ADWF-Z4mhG2ofeBb7Bza-TKKZ41-HWYoks5rA5cAgaJpZM4IBac1.

[leirags]

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_search_type', 'both’); // — all other columns, colname LIKE %xxx%

$this->datatable->setPreResultCallback(

—————CODE

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_search_type_'); $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 > 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//issues/21#issuecomment-262415271, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ADWF-Z4mhG2ofeBb7Bza-TKKZ41-HWYoks5rA5cAgaJpZM4IBac1.