search

nhibernate / nhibernate-core

NHibernate Object Relational Mapper
https://nhibernate.info
GNU Lesser General Public License v2.1
2.13k stars 931 forks source link

NH-1743 - Order by nulls first/last #967

Open nhibernate-bot opened 7 years ago

nhibernate-bot commented 7 years ago

Kenneth Siewers Møller created an issue — :

Using PostgreSQL 8.3 it's possible to add an extra parameter to the order by clause defining whether to order with nulls first or last.

At the moment it is not possible to use the Order object for manipulating the order clause, which would have been a possible solution. Since not all DBMS support this feature (and the feature is different between the DBMS's), I have no clue how a solution to this would be approached.

Hibernate has the same issue and can be found here: http://opensource.atlassian.com/projects/hibernate/browse/HHH-465

I guess it could be solved somehow by giving the user the ability to append/inject something to the end of the order by clause.

My current solution has been to inherit from the Order class, overwrite the ToSqlString and append the "nulls last" to the sql string.

Kenneth Siewers Møller added a comment — : I've tried a two different solutions and both are working.

One way to order with nulls last is by using the following construct:

order by case when tbl1.col1 is null then 1 else 0 end, col1 asc

I've tried implementing it in my custom order class, and it works as expected. Does anyone have any comments on this?

Basically what I've done is this:

fragment.Append(" case when ").Append(criteriaQuery.GetColumn(criteria, propertyName)).Append(" is null then 1 else 0 end, "); fragment.Append(columns);

As I have not idea what implications this might have, I don't know how it works in subselects etc.

Fabio Maulo added a comment — : and why you don't do it explicitly in your query instead inject something in the SQL ?

Kenneth Siewers Møller added a comment — : Well, I guess what I'm asking is if there's a way to do it more generally? I am thinking of think an extension to the Order class... perhaps like "Order.Asc("PropertyName").Nulls.Last()" (or something similar) would be very nice.

brunoamancio commented 4 years ago

No changes here :( That's a missing but useful feature.