Left outer join with HQL on tables that are not mapped

etwcn · **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?

daijavad · **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?

etwcn · **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?

batmat · **Posted:** Fri Mar 16, 2007 9:03 am

http://opensource.atlassian.com/project ... browse/HHH

corhibernate · **Joined:** Fri Apr 11, 2008 4:25 am **Posts:** 1

Is this problem fixed in newer versions?

Is there a solution?
Workaround?

PavelKrupets · **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.

scharan07 · **Joined:** Thu Sep 24, 2009 12:46 am **Posts:** 10

Use "WITH" instead of "ON".
it may work for you.

kossmo · **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!

sharada53 · **Joined:** Fri Jun 04, 2010 8:27 am **Posts:** 2

Did you get the solution for this?

etwcn · **Joined:** Thu Feb 08, 2007 10:40 am **Posts:** 46

sharada53 wrote:

Did you get the solution for this?

No.

scharan07 · **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).

tushar19892002 · **Joined:** Sat Apr 12, 2014 5:32 am **Posts:** 1

Any luck .. was your issue resolved. I am getting the same issue.