Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: HQL join on explicit columns (unrelated entities)
PostPosted: Fri Oct 22, 2010 5:22 am 
Beginner
Beginner

Joined: Mon Feb 19, 2007 4:22 am
Posts: 22
Location: Poland
Hi,

Is there a way in HQL to join two unrelated entities by specifying columns in join clause, just like in sql?

What I mean is something like that (no path component here, EntityA is not related to EntityB on mapping level):
Code:
"from EntityA a left join EntityB b on/with a.field1=b.field2 where ...."


I found http://opensource.atlassian.com/projects/hibernate/browse/HHH-16 topic, but it was created 6 years ago and I can't figure out if it is has been fixed already or not. However I could not persuade Hibernate 3.5.6 to perform such query.

Marcin.


Top
 Profile  
 
 Post subject: Re: HQL join on explicit columns (unrelated entities)
PostPosted: Tue Oct 26, 2010 10:49 am 
Newbie

Joined: Tue Oct 27, 2009 7:37 am
Posts: 5
Hi ponetny_swintuch,
I have to find a solution for exactly the same problem. You can do something like that if all you need is an inner join:

Code:
from EntityA a left join EntityB b where a.field1=b.field2

But like you, I need a left join. I'm working on a health application for an hospital and as you can guess, almost everything has a link to the patient. I map the relation from the other entities to the patient, but I don't map it the other way because I would then have a hundred associations on my Patient entity.

What do people do in this situation?

Thanks,
Sylvain Catudal


Top
 Profile  
 
 Post subject: Re: HQL join on explicit columns (unrelated entities)
PostPosted: Tue Oct 26, 2010 12:30 pm 
Senior
Senior

Joined: Fri Oct 08, 2010 8:44 am
Posts: 130
scatudal, try to use the following:

Quote:
from Someobject o full outer join Patient o.patient with o.id is not null


Top
 Profile  
 
 Post subject: Re: HQL join on explicit columns (unrelated entities)
PostPosted: Tue Oct 26, 2010 2:16 pm 
Newbie

Joined: Tue Oct 27, 2009 7:37 am
Posts: 5
r,
I could not make the syntax you proposed work. I kept on getting this error :
Code:
org.hibernate.AssertionFailure: undefined join type 23


I googled it and no one has figured out how to do a full join. Also, there is practically no documentation on what result it is suppose to give.

Here is my case:

It's a report query where I'm trying to get all EntityA for a list of patients. I also want to get EntityB for the same patient if there is one. it would be quite simple in SQL :
Code:
SELECT a.*, b.* from table_a a left join table_b b on a.patientId = b.patientId where a.patientId in (:patientIdList)


If the relation between Patient and EntityB was mapped in both ways (it is currently only mapped from entityB to patient), the following HQL would do the trick:
Code:
select a, b from EntityA a inner join a.patient patient left join patient.entityB b where a.patient.patientId in (:patientIdList)


But as I said in previous post, I don't want to start mapping all existing relations on the patient object or should I? With time, more than a 100 entities will have a relation to the patient.

Help me out! ;)

Sylvain Catudal

ps: for the moment, while looking for a better solution, I'm doing two queries...


Top
 Profile  
 
 Post subject: Re: HQL join on explicit columns (unrelated entities)
PostPosted: Tue Oct 26, 2010 3:18 pm 
Newbie

Joined: Tue Oct 27, 2009 7:37 am
Posts: 5
Finally found out how to do it:
Quote:
select a, b from EntityA a inner join a.patient, EntityB b right join b.patient p_b where a.patient = p_b and a.patient.patientId in (:patientIdList)


Leaving work now, will come back to explain further tomorrow.

edit:

The example above should now be working. Changed the following restriction :
Code:
a.patient = b.patient --> a.patient =  p_b

The previous restriction would cause the equivalent of an inner join.

Sylvain


Last edited by scatudal on Thu Oct 28, 2010 9:55 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: HQL join on explicit columns (unrelated entities)
PostPosted: Wed Oct 27, 2010 6:07 am 
Senior
Senior

Joined: Fri Oct 08, 2010 8:44 am
Posts: 130
scatudal wrote:
Leaving work now, will come back to explain further tomorrow.


Yes, you are right. Right join is even better here as you do not need "with" part. Sorry for confusion.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 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.