[NFR] PostgreSQL row sorting: implement [NULLS { FIRST | LAST }]

[globalundo]

Currently, on executing following piece of code:

$robots = Robots::find(array(
    "order" => "name DESC NULLS LAST"
));

Exception rises:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token IDENTIFIER(NULLS), near to ' LAST'

[phalcon]

How could that syntax be rewritten to work on Mysql and Sqlite?

[globalundo]

As far as I'm concerned, there is no native NULLS { FIRST | LAST }, but there are some workarounds http://stackoverflow.com/questions/1498648/sql-how-to-make-null-values-come-last-when-sorting-ascending
http://stackoverflow.com/questions/12503120/how-to-do-nulls-last-in-sqlite for mysql and sqlite respectively.

[roman-kulish]

@phalcon Why should it even work in Sqlite and MySQL? I see no reason for it. If somebody wants to use advanced database features then framework should provide this possibility, otherwise it is a framework limitation. If framework tends to be compatible with all databases it should be flexible, but not impose limitations.

[phalcon]

Because the goal of PHQL is transparent switch between the supported databases. Support every possible no sql-standard syntax is almost impossible. Also, we can't automatically escape columns/table names or rename columns if PHQL is not used.

[igorgolovanov]

Can be solved by #653

$di->modelsManager->registerSimpleQueryFunction('ORDER_BY_NULLS_LAST', '?0 NULLS LAST');

$robots = Robots::find(array(
    "order" => "name ORDER_BY_NULLS_LAST(DESC)"
));

// SELECT u.* FROM users AS u ORDER BY u.name DESC NULLS LAST 

[sergeyklay]

This belongs in Incubator Dialect.

[sergeyklay]

This issue was moved to phalcon/incubator#801