[#ZF-2818] quoting identifier in order - Zend Framework Issue Tracker

Details

Type: Bug
Status: Resolved
Priority: N/A
Resolution: Not an Issue
Affects Version/s: None
Fix Version/s: 1.9.3
Component/s: Zend_Db_Select
Labels:
None

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

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Wil Sinclair added a comment - 25/Mar/08 9:31 PM

Please categorize/fix as needed.

Show

Wil Sinclair added a comment - 25/Mar/08 9:31 PM Please categorize/fix as needed.

Hide

Permalink

Travis Crowder added a comment - 15/Dec/08 1:45 PM - edited

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.

Show

Travis Crowder added a comment - 15/Dec/08 1:45 PM - edited 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.

Hide

Permalink

Wil Sinclair added a comment - 06/Jan/09 10:50 AM

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

Show

Wil Sinclair added a comment - 06/Jan/09 10:50 AM No action on this issue for too long. I'm reassigning to Ralph for re-evaluation and categorization.

Hide

Permalink

Ralph Schindler added a comment - 10/Jan/09 11:28 AM

Will evaluate within 2 weeks

Show

Ralph Schindler added a comment - 10/Jan/09 11:28 AM Will evaluate within 2 weeks

Hide

Permalink

Ralph Schindler added a comment - 27/Aug/09 8:40 AM

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)

Show

Ralph Schindler added a comment - 27/Aug/09 8:40 AM 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)

People

Assignee:

Ralph Schindler

Reporter:

Marc Bennewitz (GIATA mbH)

Vote (1)

Watch (1)

Dates

Created:

06/Mar/08 4:41 PM

Updated:

27/Aug/09 8:40 AM

Resolved:

27/Aug/09 8:40 AM