-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
DJDaveMark
 Post subject: JPA JPQL - How to Order the Joins
PostPosted: Fri Feb 27, 2009 11:32 am 
Newbie

Joined: Mon Dec 29, 2008 7:40 pm
Posts: 6
Location: France
Hi all,

I'm having trouble ordering the joins in JPQL (with Hibernate 3). I have a database with about 20 tables and they're arranged in a sort of circle with two parts branching out, eg.

"-->" = "is joined to"

T1 --> T2 --> T3 --> T4 --> T5 --> T6 --> T7 --> T8 --> T1
and
T3 --> T3B --> T3C
and
T7 --> T7B --> T7C

I have to join all of these tables using "left join fetch". Joining T1, T2, ... T8, T1 is no problem...

Code:
select t1 from T1 t1 
left join fetch t1.t2 t2 
[...] 
left join fetch t7.t8 t8 
left join fetch t8.t1 t11 

but then I get an error when attempting to join one of the 'branches'...

Code:
select t1 from T1 t1 
left join fetch t1.t2 t2 
[...] 
left join fetch t7.t8 t8 
left join fetch t8.t1 t11 
left join fetch t3.t3b t3b 

What is the correct ordering for joins?

Explanation :
The object/table t1 is actually the user and based on the user and based on their group/profile etc. we build menus and other stuff.
Why :
We're not allowed to use multiple requests to the database (we had 52 requests). Joining 'the circle' (T1, T2 ... T8, T1) with 'left join fetch' brings us down to 20 requests, but this is still too much. The application will be used by a huge amount of people all starting at 9 o'clock, so we don't want the log-in/create-menu phase to cripple the systems response time.

Any help much appreciated.
Top
 Profile  
 
DJDaveMark
 Post subject: Found the right order
PostPosted: Mon Mar 02, 2009 6:49 am 
Newbie

Joined: Mon Dec 29, 2008 7:40 pm
Posts: 6
Location: France
I found the right order. In short I followed 'the branches' before continuing round 'the circle' so I did...

Code:
T1 --> T2 --> T3 --> T3B --> T3C
then
T3 --> T4 --> T5 --> T6 --> T7 --> T7B --> T7C
then
T7 -->  T8 --> T1


My case was actually more complicated than I've described as I actually have a branch which is connected to two parts of 'the circle'. This branch itself having more branches.

So I finally ended up with something like this...

Code:
T1 --> T2 --> T3 --> T3B --> T3C
then
T3 --> T4 --> T5 --> 3B
then
T5 --> T6 --> T7 --> T7B --> T7C
then
T7 -->  T8 --> T1


...which put all together became...

Code:
select t1 from T1 t1
left join fetch t1.t2 t2
left join fetch t2.t3 t3
left join fetch t3.t3b t3b
left join fetch t3b.t3c t3c
left join fetch t3.t4 t4
left join fetch t4.t5 t5
left join fetch t5.t3b t53b
left join fetch t5.t6 t6
left join fetch t6.t7 t7
left join fetch t7.t7b t7b
left join fetch t7b.t7c t7c
left join fetch t7.t8 t8
left join fetch t8.t1 t11
where t1.Id = :t1Id
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.