Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
sergeyklay opened this issue Jul 17, 2017 · 9 comments
Open

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

sergeyklay opened this issue Jul 17, 2017 · 9 comments

Comments

@sergeyklay
Copy link
Contributor

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
Copy link
Contributor Author

From @phalcon on April 15, 2013 14:30

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

@sergeyklay
Copy link
Contributor Author

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
Copy link
Contributor Author

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
Copy link
Contributor Author

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
Copy link
Contributor Author

From @igorgolovanov on May 21, 2013 12:7

Can be solved by phalcon/cphalcon#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
Copy link
Contributor Author

sergeyklay commented Jul 17, 2017

This belongs to Incubator's Dialect.

@Jurigag
Copy link
Contributor

Jurigag commented Aug 1, 2017

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

@MayconGhizzi
Copy link

MayconGhizzi commented May 8, 2018

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
Copy link

MayconGhizzi commented May 9, 2018

I solved this with:

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

=*

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants