Order by (chain) column, which may be zero

Question

I am currently facing a problem with Hibernate, where I automatically create an HQL statement that looks like

FROM table ORDER BY ab ASC NULLS LAST 

My intention was to arrange all records ab and set all records in which a or b is NULL at the end of the table. Hibernate does not complain about the statement, but simply ignores all records in which a already NULL . I experimented with setting:

 FROM table ORDER BY NULLIF(ab, NULL) ASC NULLS LAST 

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

Thank you for your help!

Answer 1

Thanks for the answer, I found another solution that was easier to implement. Now I create a query as follows:

 FROM table ORDER BY a ASC NULLS LAST, ab ASC NULLS LAST 

For me, this works for any circuit measurements, as long as these orders are in order. This is much easier for me to implement, since the request is generated automatically. However, thanks for the tip. I tried it and your solution works fine, but I will need to configure my general setup.

Answer 2

I assume that a is a property of type table_two, and this property is determined by a many-to-one relationship or sth. similarly.

You order by a column that is not a member of the table, but is associated with the table table_two. Sql cannot do this directly, and Hibernate does this by creating a connection between the table and table_two. This join generated by Hibernate is a regular join, not an outer join, so the selection does not retrieve rows that do not have a related record in table_two.

You can solve the problem by specifying an external connection manually. Something like this should work:

 FROM table t LEFT OUTER JOIN ta u ORDER BY ub ASC NULLS LAST 

Answer 3

I had the same problem, I performed a left outer join ... I have Cost and Order domains, the Order property is an instance of nested relationships; it may be NULL . For clarity, i.e.:

 class Cost { User owner Order order etc... } Cost.executeQuery('SELECT c FROM Cost AS c LEFT OUTER JOIN c.order AS o WHERE c.owner = ? ORDER BY o.orderNumber ASC, c.id DESC' , cost_qry_params, params) 

You can put your pagination / sorting information in options ... Hope this helps someone :)