I have a class, called "Folder", which has a mapped "map":



I want to paginate folders, and allow sorting of the folder based on the map--for example, if one of the map keys is "yearPublished", then I'd like to sort the folders based on the yearPublished value, so that folders with a map that have "1990" for the yearPublished value come before folders with a map that have "2000" for the yearPublished value.

I've been able to actually accomplish most of what I'm trying to do with the following query:



I create a Query object with that string, and then call setFirstResult and setMaxResults on the query object, and everything works. Note that by using the above string, I'm not actually fetching all attributes at that time, as that would mess up the setFirstResult and setMaxResults.

The problem is that if a particular folder does not have the map key in question, it won't show up in the list of results, but I'd like to see all folders in the results, even if they don't have the map key in question.

I can't do a union all of the first set of folders with something like "select folder from Folders folder where folder not in (theFirstQuery)", because hql doesn't support union all. I suppose I could do it in two separate queries--see how many results come back from the first query, and if it is less than the page size, run the "not in" query to get some more results. Alternatively, I could drop down to native sql, where union all is supported. But, before I pursue one of those options, I'm wondering if there is just a simple way I'm overlooking to get the folders that don't have the map key in question included in the original results.

Any help would be appreciated.

First off, thanks for the question. For some unknown reason I kept thinking about this for hours and I actually could sort out a couple of things on the way. Anyhow, it's hard to come to an HQL that is able to do this due to a couple of HQL limitations, for some of which I have no explanation and I do believe it is just a matter of implementation. However, you can still use a native query for this. I did a little example that resembles much of your situation: a configuration class that has a map of properties. Assume the mapping for the properties is:




then the following code would do what you want:



'p1' in this example is a property name which some configurations have and some might not have. The returned list of configurations are in an ascending order of p1 values for the configurations that have it and the result of configurations following that last configuration with a p1 property.

You can still do two queries and merge the results manually if a native query is not a solution for you. Meanwhile we can still see if there is any other way to solve this.


Farzad-