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

[sergeyklay]

From @globalundo on April 15, 2013 13:36

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'

Copied from original issue: phalcon/cphalcon#548

[sergeyklay]

From @phalcon on April 15, 2013 14:30

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

[sergeyklay]

From @globalundo on April 15, 2013 15:25

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.

[sergeyklay]

From @roman-kulish on May 10, 2013 1:20

@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.

[sergeyklay]

From @phalcon on May 12, 2013 0:58

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.

[sergeyklay]

From @igorgolovanov on May 21, 2013 12:7

Can be solved by https://github.com/phalcon/cphalcon/issues/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 to Incubator's Dialect.

[Jurigag]

So someone only need to add it to postgresql dialect, quite easy thing to do.

[MayconGhizzi]

I have same problem, any solution ?

I try this, but I thing is obsolete: $di->modelsManager->registerSimpleQueryFunction('ORDER_BY_NULLS_LAST', '?0 NULLS LAST');

registerSimpleQueryFunction doesn`t exist. (phalcon 3.2)

thanks.

[MayconGhizzi]

I solved this with:

"order" => "(ref_year is not null) desc, (ref_number is not null) desc".

=*