I would like to sort (orderBy) a collection of Content objects by the value of one of its associated elements stored in a map.

The Java for the value I want to order by is:



Where each part is represented by the following annotation mapped classes:

content: Content
content.getMetaData(): FreeFormRecord extends FreeFromDatabaseObject implements Map
content.getMetaData().get("netPay"): FreeFormField extends FreeFormDatabaseObject implements CharSequence
content.getMetaData().get("netPay").getValue(): String

FreeFormDatabaseObject is the superclass of FreeFormRecord, FreeFormField and the previously unmentioned FreeFormList. The approach allows me to store arbitrary trees of strings stored in the leaves (FreeFormField).

FreeFormRecord actually implements the Map interface via composition - it has the field:



In practice one could rewrite the Java as:



How do I do rewrite this in HQL to go in the method?

I've played with joins and the index() function but I can't get it to work.

I seem to get close with:



But this obviously doesn't contain and 'orderBy' clause and returns a list of Object arrays containing 5 elements from 3 different classes.

This get me even closer:



But unfortunately the list contains lots and lots of duplicates.
How do I get rid of them?

With some help from a friend this appears to work:



It returns a list of arrays each of which unsurprisingly contains a content object and the netPay value, but I can live with that.
I had to put the order by cmm.value in the distinct term otherwise it didn't like it.
The database isn't to know that there's only one netPay keyed value per metaData object - even if Java does know.