ZF-2818: quoting identifier in order

Description

I use table names with a dots, but on order the identifier is wrong quoted and i can't quote it manuelly.


// ...
$tableAs = 'my.table.as';
$column = 'myColumn';

$select->order($tableAs . '.' . $column . ' ASC'); // -> ORDER BY  `my`.`table`.`as`.`myColumn` ASC

$qTableAs = $dbAdapter->quoteIdentifier(array($tableAs, $column));  // -> `my.table.as`.`myColumn`
$select->order($qTableAs . ' ASC'); // -> ORDER BY  

my.table.as

`.

myColumn

 ASC

Comments

Please categorize/fix as needed.

I am able to replicate the problem in the latest release and all other methods allow for backticks. Backtick support should have been added and should be added to future releases.


$select = new Zend_Db_Select($this->_db);
$select->from(array('jd' => DB_TABLE_JOBS_DATA))
    ->joinRight(array('j' => DB_TABLE_JOBS), '`j`.`jobid` = `jd`.`jobid`')
    ->joinRight(array('sj' => DB_TABLE_SITES_JOBS), '`j`.`jobid` = `sj`.`jobid`')
    ->where('`sj`.`siteid` = '.$siteid)->where("`j`.`job_zip` IN ('".implode("', '", $zipcodes)."')")
    ->where("MATCH (`jd`.`job_company`) AGAINST ('".implode(' ', $keywords)."' IN BOOLEAN MODE)")
    ->order(array('j.job_datetime ASC'));

Above works


$select = new Zend_Db_Select($this->_db);
$select->from(array('jd' => DB_TABLE_JOBS_DATA))
    ->joinRight(array('j' => DB_TABLE_JOBS), '`j`.`jobid` = `jd`.`jobid`')
    ->joinRight(array('sj' => DB_TABLE_SITES_JOBS), '`j`.`jobid` = `sj`.`jobid`')
    ->where('`sj`.`siteid` = '.$siteid)->where("`j`.`job_zip` IN ('".implode("', '", $zipcodes)."')")
    ->where("MATCH (`jd`.`job_company`) AGAINST ('".implode(' ', $keywords)."' IN BOOLEAN MODE)")
    ->order(array('`j`.`job_datetime` ASC'));

Results in erroneous backticks and SQL error column does not exist.

No action on this issue for too long. I'm reassigning to Ralph for re-evaluation and categorization.

Will evaluate within 2 weeks

In cases where quoting the identifier of an order is not a simple task, its advised that you quote it on your own with $adapter->quoteIdentifier() (as it looks like you did), but also wrap that inside of a Zend_Db_Expr.

For example:


$select = $dbAdapter->select()
    ->from('foo')
    ->order(new Zend_Db_Expr('`j`.`job_datetime` ASC'));
echo $select;

Will produce:


SELECT `foo`.* FROM `foo` ORDER BY `j`.`job_datetime` ASC

(specific to mysql)