I hope this is a simple one.

I have two classes, Button and Category. A Button has a nullable Category property. That is, a Button can belong to a single Category, or it can be "uncategorized".

Both the Button and Category classes have a displayOrder property.

I want to pull a list of Buttons, ordered first by the Category's displayOrder property, then by the Button's displayOrder property. I'd like all uncategorized Buttons to come last.

I am using HQL to write the query, which is running in Spring against a MySQL database. Conceptually, I'd like it to be something like
"from Button b order by isNull(b.category.displayOrder, 100000), b.displayOrder"

This does not work, of course, because the category itself is null (nor am I sure if you can use the isNull() construction on DBs other than SQLServer).

I can write a simple Java comparator class to sort the list after I retrieve it, but it seems more efficient if I just do it right in the query.

Anyone have a suggestion for how to do this in HQL?

Hi,
First you probably should join category.

The following works with an underlying ora db:

from Button b left join b.category as cat order by nvl(cat.displayOrder, 100000), b.displayOrder

Rate if this helps.

With a couple of modifications, I was able to get mooritze's suggestion to work.

First, I use "coalesce" instead of "nvl". Coalesce is supported by most major DBs (Oracle, MySQL, SQLServer) so it is more portable. nvl does not exist on MySQL, the database I am using.

Second, I added a "select b" clause to my HQL at the front. Otherwise, with the join on Category, I get back a list of object arrays, with a Button and a Category. I just want a list of Buttons.

So the final statement looks like

"select b from Button b left join Category cat order by coalesce(cat.order, 10000), b.order"

The only thing I'm not happy with is the hard-coded 10000 in the coalesce expression. I want the buttons without a category to be last, and as long as no category has a display order greater than 10000, this will work. But it's not very clean.