Can you order hql results based on matching restrictions?

Question

I have an object Dog which holds a set of DogMetadata.

Each DogMetadata has two values: a String ("desc") and an int ("rank").

Descriptions of dogs have different rankings such as: "small" is 5, "furry" is 2, "friendly" is 9, "dalmation" is 11, "mutt" is 22.

I need to search for dogs based on any of their desc values (for example, find "furry" OR "small" dogs).

This query returns the matching dogs, but they are not in any order.

select distinct Dog as d
left join d.dogMetadata as dMeta
where ( (dMeta.desc = 'furry') OR (dMeta.desc = 'small') )

How do I order the list of matching Dog objects by the total "rank" values of any matching DogMetadatas?

I've been working like a dog on this all day (trying "Group By" and "Order By") but I think I have been barking up the wrong tree.

Answer 1

Just from the top of my head (without actually verifying it in HQL)

from Dog d where d.dogMetadata.desc in ('furry','small','whatever') order by d.dogMetadata.rank

Answer 2

This query won't work:

select d from Dog as d
left join fetch d.dogMetadata as dMeta
where ( (dMeta.desc = 'furry') OR (dMeta.desc = 'small') )
group by d
order by sum(dMeta.rank) desc

Because of this bug: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615?focusedCommentId=24530#action_24530

I worked around this limitation by only requesting the Dog IDs:

select d.id from Dog as d
left join fetch d.dogMetadata as dMeta
where ( (dMeta.desc = 'furry') OR (dMeta.desc = 'small') )
group by d.id
order by sum(dMeta.rank) desc