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.



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.

Hi,
You told that you tried Order By, please also try using fetch and check if it works.
select distinct Dog d
left join fetch d.dogMetadata dMeta
where ( (dMeta.desc = "furry") OR (dMeta.desc = "small") )
order by dMeta.desc asc

Thank you, that *almost* works. There are two problems.

1. I need to Dogs who match multiple desc to rank higher in the list (example: a dog who matches both 'fluffy' and 'friendly' would rank higher than a dog who only matches 'fluffy'. Do I need to use sum or count? I am not sure how to do that with HQL.

2. I can only get the Dog objects to sort correctly when I use desc. If I use asc, there doesn't seem to be any ordering. I am using Oracle 11 and Hibernate 3.2.1.

So I am working with this query:



But can't get it to work... "ORA-00979 not a GROUP BY expression".

This led me to this bug report:
http://opensource.atlassian.com/project ... tion_24530

So.. I am confused.

(1) is my new query the right way to solve my dog ordering problem?

(2) if so, how do I work around that bug?