Sql::orderBy doesn't quote columns

[mrsoto]

I've this query which does not quote columns and then mysql retrieve an incorrect order. The table has timestamp column

$mapper->news->fetchAll(Sql::orderBy('timestamp', 'id')->desc()->limit($initial, $pageSize))

Resulting query:

SELECT news.* FROM news ORDER BY timestamp, id DESC LIMIT 0, 200

expected:

SELECT news.* FROM news ORDER BY `timestamp`, `id` DESC LIMIT 0, 200

Is there a way to instruct Sql to quote columns?

[mrsoto]

Actually, the real issue is that the order by should be :

ORDER BY `timestamp` DESC, `id` DESC

[felipecwb]

To solve this issue now you can use this ugly way:

Sql::orderBy('`timestamp` DESC','`id` DESC'); // ORDER BY `timestamp` DESC, `id` DESC 

I'm seeing to improve this.

Something like:

Sql::orderBy(array('timestamp' => 'DESC', 'id' => 'DESC'));

I'm looking too for the identifier escape.

[alganet]

Unfortunately, quoting is dbms-specific. Each database has its own quoting model (for example, SQL Sever uses brackets to quote e.g. [column_name]).

The Sql class should be dbms independent, but you can extend it...

<?php
use Respect\Relational\Sql as RespectSql;
/**
 * The SQL dialect with extra statements
 */
class FooBarSql extends RespectSql
{
    /**
     * Method used to translate from php method calls to SQL instructions.
     * It is closely related to __call for the Respect\Relational\Sql class.
     */
    protected function build($operation, $parts)
    {
        switch ($operation) {
            case 'orderBy':
                return $this->buildParts($parts, '`%s` ');
            default:
                return parent::build($operation, $parts);
        }
    }
}

... and then use it on your Mapper or Db instance:

<?php
$pdo = new PDO(/* your own PDO config */);
$sqlPrototype = new FooBarSql(); // The class we've extended before
$db = new \Respect\Relational\Db($pdo, $sqlPrototype);
$mapper = new \Respect\Relational\Mapper($db);

Both the Mapper and the Db classes will now use the $sqlPrototype instance as a blueprint for building query expressions. When using them outside the Mapper or Db, you'll need to address it like FooBarSql::orderBy.

I've used this technique before to make Respect\Relational support CREATE TABLE instructions. You can find the implementation here for reference.