Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Left outer join with HQL on tables that are not mapped
PostPosted: Fri Feb 23, 2007 8:57 am 
Beginner
Beginner

Joined: Thu Feb 08, 2007 10:40 am
Posts: 46
Hi,

I need to create an hql query that does a left outer join on from table A to table B, while the two tables are not mapped with hibernate.

I can easily do this with sql:

select A.a_field, B.another_field
from A
left outer join B on id = ext_id

However, it seems that the HQL syntax does not support the "on id = ext_id" part. The documentation just states, that hibernate would find out itself, which fields to use. Well.. in my case, it can't cause it is not mapped.

It will not help me, if you tell me that I should map the two tables - i can't do that, because i don't want B to be associated with A, when loading, updating, deleting, or doing anything on A.

It will not help me, if you tell me that I should use an SQL query instead - the overall query is large and hibernate creates several sub queryies from it. I just can't write it in sql. And after all: that's what hql is for - not having to use sql.

Any chance to get that problem solved? Maybe in a near future version of hibernate?


Top
 Profile  
 
 Post subject:
PostPosted: Sat Feb 24, 2007 10:41 am 
Newbie

Joined: Mon Jan 15, 2007 9:51 am
Posts: 17
I am working on a similiar problem, with a report query where I have "unmapped" (unidirectional mappings) classes. Here's the relevant parts of my mapping files:


Category.hbm.xml:
Code:
<many-to-one name="collection" class="com.Collection" >
  <column name="collectionid" sql-type="int" not-null="true" />
</many-to-one>


Collection.hbm.xml:
Code:
<set name="items" table="collectionitem" lazy="false">
  <key column="collectionid" />
  <many-to-many class="com.Item" column="itemid" />
</set>


Item.hbm.xml:
Code:
<set name="collections" table="collectionitem" lazy="false" inverse="true">
  <key column="itemid" />
  <many-to-many class="com.Collection" column="collectionid" />   
</set>


Image.hbm.xml:
Code:
<many-to-one name="item" class="com.Item">
  <column name="itemid" sql-type="int"/>      
</many-to-one>


The mapping in Image.hbm.xml is unidirectional, there is no reference to Images from an Item.
The mapping in Category.hbm.xml is also unidirectional, there is no reference to the Category from a Collection

What I want to know is how many images exists per Category (including those with zero images). What I have so far is:
Code:
String query = "select cat.name, count(image) " +
      "from Category cat, Image image " +
      "join cat.collection.items item " +
      "where item = image.item " +
      "group by cat.name";


This works, except that no row/object is returned when there are 0 images under a category (which is to expect according to the way a where clause works (limiting the results)). I have tried a dozen variations using only joins, but I just can't get it right. Since some of the mappings are unidirectional it makes matters worse. As etwcn pointed out, Hibernate can't figure out itself what fields to use, and Hibernate (HQL) doesn't support the the ON clause.
I believe I could worked it out if they were bi-directional (ie. the classes were fully mapped), but that option is a last resort, I don't want Item to know about it images (analogous to previous post: B to be associated with A)...

Could anyone please help shed some light on this issue and/or suggest suitable modifications to the query?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 28, 2007 12:54 pm 
Beginner
Beginner

Joined: Thu Feb 08, 2007 10:40 am
Posts: 46
Since there does not seem to be a useable solution to that problem:

Is there any place, where I can post a kind of change request to have support for the "ON fielda = fieldb" clause in a future hibernate version?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 16, 2007 9:03 am 
Expert
Expert

Joined: Tue Dec 28, 2004 7:02 am
Posts: 573
Location: Toulouse, France
http://opensource.atlassian.com/project ... browse/HHH

_________________
Baptiste
PS : please don't forget to give credits below if you found this answer useful :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 11, 2008 4:28 am 
Newbie

Joined: Fri Apr 11, 2008 4:25 am
Posts: 1
Is this problem fixed in newer versions?

Is there a solution?
Workaround?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 03, 2008 7:13 pm 
Beginner
Beginner

Joined: Wed Jun 09, 2004 8:10 am
Posts: 28
Any luck? For now one can use sub queries. I hope they will add this feature.


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Wed Jan 20, 2010 9:46 am 
Newbie

Joined: Thu Sep 24, 2009 12:46 am
Posts: 10
Use "WITH" instead of "ON".
it may work for you.


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Wed Mar 24, 2010 6:18 am 
Regular
Regular

Joined: Thu Dec 10, 2009 10:53 am
Posts: 50
replacing "on" with "with" does not work:

I get the error:
Quote:
[ERROR,PARSER] Path expected for join!


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Fri Jun 04, 2010 8:39 am 
Newbie

Joined: Fri Jun 04, 2010 8:27 am
Posts: 2
Did you get the solution for this?


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Mon Jun 07, 2010 2:16 am 
Beginner
Beginner

Joined: Thu Feb 08, 2007 10:40 am
Posts: 46
sharada53 wrote:
Did you get the solution for this?

No.


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Fri Aug 06, 2010 4:51 am 
Newbie

Joined: Thu Sep 24, 2009 12:46 am
Posts: 10
Hi,
Quote:
[ERROR,PARSER] Path expected for join!


This error will come when your from clause classes are not mapped.
I think as per hibernate we can make left join with out having reference to each other objects (relation).


Top
 Profile  
 
 Post subject: Re: Left outer join with HQL on tables that are not mapped
PostPosted: Sat Apr 12, 2014 5:34 am 
Newbie

Joined: Sat Apr 12, 2014 5:32 am
Posts: 1
Any luck .. was your issue resolved. I am getting the same issue.


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