-->
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.  [ 6 posts ] 
Author Message
 Post subject: Hibernate Query Problem
PostPosted: Fri Aug 03, 2007 4:59 pm 
Newbie

Joined: Thu Jul 12, 2007 8:57 am
Posts: 11
I've been trying to make a more complex query than I'm used to, but try as I might I can't seem to get the syntax correct.
Is it possible to query the database for a persisted object which has both a Unique foreign key, and a maximum primary key?

That is, if I have multiple objects with the same foreign key and I want all of the ones which have the highest primary key and unique foreign key

1: fk: 4 pk: 1
2: fk: 4 pk: 2
3: fk: 7 pk: 3
4: fk: 7 pk: 4
5: fk: 7 pk: 5
6: fk: 9 pk: 6
7: fk: 9 pk: 7

I want entry 2 because it is the highest id with fId 4
I want etnry 5 because it is the highest id with fId 7
I want entry 7 because it is the highest id with fId 9


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 07, 2007 11:24 am 
Newbie

Joined: Thu Jul 12, 2007 8:57 am
Posts: 11
Really? This isn't possible?
Could I do something to mimic this function? I have a method right now which is able to do what I want, but I feel it is really inefficient.

It uses two while loops, one to iterate through all of the primary keys of the parent object, and another nested while loop to iterate through the objects with foreign keys matching the current iteration's primary key and only retrieving the last of these objects.

There must be a better way!


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 07, 2007 11:56 am 
Expert
Expert

Joined: Fri Jul 13, 2007 8:18 am
Posts: 370
Location: london
You can use a subquery to find the max:

from Entry entry where pk=(select max(pk) from Entry entry1 where entry.fk = entry1.fk)


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 07, 2007 12:08 pm 
Expert
Expert

Joined: Thu Jul 05, 2007 9:38 am
Posts: 287
There are is a way to do this, which should work in hibernate:

create a subquery, selecting the maximum PK grouped by the foreign key.

Your main query selects all rows with the PK included in the subquery.

This aproach should work in any sql dialect supporting subselects and it should be possible to formulate this as

Hibernate Criteria; HQL and definetly as SQL.


If you happen to work against a Oracle Database you can use analytic functions to do this much faster in many cases.

I doubt Hibernate supports analytics through Criteria or HQL, though I might me wrong. But you always use the SQL syntax.

If you're interested in more information about the oracle specific solution let me know.

Jens


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 07, 2007 12:13 pm 
Expert
Expert

Joined: Fri Jul 13, 2007 8:18 am
Posts: 370
Location: london
I like Jens' suggestion better - probably more efficient than mine.

from Entry entry where pk in (select max(pk) from Entry group by fk)


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 08, 2007 1:07 am 
Newbie

Joined: Thu Jul 12, 2007 8:57 am
Posts: 11
Thank you, in the end
exactly as you said.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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.