I am moving from Criteria to HQL and enjoying the process, but have a a question.


I have a class "Person" with a one-to-many set of objects "Metadata". Metadata are simple objects; they only have a String key and a String value.

I would like to use HQL to query for person objects based on multiple metadata constraints.

For example, I want to find people objects with two types of specific types of metadata.



This doesn't return anything, even though there are people objects with red hair and green eyes in my database....

This is probably because I am asking for a specific metadata object's value ("key") to be two things at the same time.

If I use an 'or', I get results back, but I want red-haired green-eyed people specifically.

How should I express my desire to search though a set this way? Thanks.

To check for too different values from the same base collection you have to join the collection 2 times.

Something like that:

Wow, thanks.

I found another solution, although I don't know which solution is optimal (mine or yours).

Here is how I did it using a correlated nested subquery:



Are there benefits / drawbacks to this approach?

If you are only looking for Person object it shouldn't matter, you can even drop the "select distinct ppl" part because you only get one line per Person.

If you want to instantiate the objects for Metadata too my solution offers the possibility to use JOIN FETCH