Issues

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.

<pre class="highlight">
// ...
$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

<pre class="highlight">`.

myColumn

<pre class="highlight"> 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.

<pre class="highlight">
$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

<pre class="highlight">
$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:

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

Will produce:

<pre class="highlight">
SELECT `foo`.* FROM `foo` ORDER BY `j`.`job_datetime` ASC

(specific to mysql)

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts