-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Getting started with HQL - searching sets
PostPosted: Fri Jan 30, 2009 3:28 pm 
Newbie

Joined: Thu May 01, 2008 11:52 am
Posts: 17
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.

Code:
   "Select distinct ppl from Person as ppl " +
   "join ppl.metadata as meta " +
   "where " +
   "(meta.key='hair_color' and meta.value='red') " +
   "and " +
   "(meta.key='eye_color' and meta.value='green') "


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.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 31, 2009 6:41 am 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
To check for too different values from the same base collection you have to join the collection 2 times.

Something like that:

Code:
"Select distinct ppl from Person as ppl " +
   "join ppl.metadata as meta " +
   "join ppl.metadata as meta2 " +
   "where " +
   "(meta.key='hair_color' and meta.value='red') " +
   "and " +
   "(meta2.key='eye_color' and meta2.value='green') "


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 31, 2009 12:43 pm 
Newbie

Joined: Thu May 01, 2008 11:52 am
Posts: 17
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:

Code:
"Select distinct ppl from Person as ppl " +
"where ( " +
"exists (from Metadata as meta where (meta.key = 'hair_color') and (meta.value = 'red') and (meta.person = ppl)) " +
"and " +
"exists (from Metadata as meta where (meta.key = 'eye_color') and (meta.value = 'hair_color') and (meta.person = ppl)) " +
") "


Are there benefits / drawbacks to this approach?


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 31, 2009 1:26 pm 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.