ZF-2818: quoting identifier in order

Issue Type:
Bug
Created:
2008-03-06T16:41:48.000+0000
Last Updated:
2009-08-27T08:40:46.000+0000
Status:
Resolved
Fix version(s):
  • 1.9.3 (22/Sep/09)
Reporter:
Marc Bennewitz (GIATA mbH) (mben)
Assignee:
Ralph Schindler (ralph)
Tags:
  • Zend_Db_Select
Related issues:
Attachments:

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

Posted by Wil Sinclair (wil) on 2008-03-25T21:31:56.000+0000

Please categorize/fix as needed.

Posted by Travis Crowder (spechal) on 2008-12-15T13:45:18.000+0000

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.

Posted by Wil Sinclair (wil) on 2009-01-06T10:50:17.000+0000

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

Posted by Ralph Schindler (ralph) on 2009-01-10T11:28:42.000+0000

Will evaluate within 2 weeks

Posted by Ralph Schindler (ralph) on 2009-08-27T08:40:45.000+0000

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)