Order by (chained) column which can be null

Question

I am currently encountering a problem with Hibernate where I create a HQL statement automatically which looks like

FROM table ORDER BY a.b ASC NULLS LAST

My intention was to order all entries by a.b and setting all entries where a or b is NULL to the end of the table. Hibernate does not complain about the statement, but just ignores all entries where a is NULL already. I experimented with setting:

FROM table ORDER BY NULLIF(a.b, NULL) ASC NULLS LAST

and again, Hibernate does not complain but again ignores all entries where a is NULL.

Thank you for your help!

Answer 1

I guess a is a property of type table_two, and this property is defined with a many-to-one relation or sth. similar.

You order over a column which is not a member of table but of the related table_two. Sql can't do this directly, and Hibernate does this by generating a join between table and table_two. This join generated by Hibernate is a normal join, not an outer join, so the select does not fetch the rows which do not have a related entry in table_two.

You can solve the problem by defining the outer join manually. Something like this should work:

FROM table t
   LEFT OUTER JOIN t.a u
   ORDER BY u.b ASC NULLS LAST

Answer 2

Thank you for the answer, I found a different solution which was easier to implement. I now create a request as follows:

FROM table ORDER BY a ASC NULLS LAST, a.b ASC NULLS LAST

For me, this works for any dimensions of chains as long as these orders are ok. This is much easier for me to implement since the query is generated automatically. However, thank's for the advice. I tried it and your solution works fine as well but would require me to adjust my overall setup.