-->
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
  [ 12 posts ] 
  Previous topic | Next topic 
Author Message
PeterNilsson
 Post subject: HQL: Subquery on composite-element
PostPosted: Fri Sep 10, 2004 4:59 am 
Newbie

Joined: Fri Aug 20, 2004 10:07 am
Posts: 9
Location: Sweden
I have a one-to-many association mapped as a composite-element but I run into trouble when I need to create a subquery.

Example:
One Product has many Parts and I want to retrieve the Products that have at least one Part that satisfies a given condition.

Mapping documents:
Code:
<class name="Product" table="PRODUCT_TB">
    <id name="Id" column="PRODUCT_ID" type="long">
      <generator class="assigned" />
    </id>
    <set name="Parts" table="PART_TB">
      <key column="PRODUCT_FK" />
      <composite-element class="Part">
        <property name="Size" type="int" column="SIZE" />
        <property name="Cost" type="int" column="COST" />
      </composite-element>
    </set>
</class>

Given the above mapping I want HQL that corresponds to the following SQL:
select * from PRODUCT_TB where exists (select 1 from PART_TB where SIZE = 5 and COST < 10 and PRODUCT_FK = PRODUCT_ID)

I have studied the reference guide but have not been able to figure out how to formulate the HQL. Of course the HQL should really not be dependent on the mapping used but should only depend on the object model regardless of if the association is mapped as composite-element or one-to-many with parent-child semantics.

Is it possible to use subqueries with associations mapped as composite-elements?

Peter

Hibernate version:
2.1.4
Top
 Profile  
 
Teg
 Post subject:
PostPosted: Fri Sep 10, 2004 11:56 am 
Regular
Regular

Joined: Wed Aug 18, 2004 5:16 am
Posts: 69
Location: Modena, Italy
I have recreated your example and this query works:
Code:
from Product prod join prod.parts part where part.psize=5 and part.cost<10

Note: I suggest you to use lower case initial for java properties and to avoid special SQL or DBMS keywords as fields name or java properties (SIZE is reserved)
Top
 Profile  
 
PeterNilsson
 Post subject:
PostPosted: Mon Sep 13, 2004 3:24 am 
Newbie

Joined: Fri Aug 20, 2004 10:07 am
Posts: 9
Location: Sweden
The problem with using a join instead of a subquery is that I get multiple hits for Product if there are more than one Part that satisfies the condition.
The query I am looking for should return at most one copy of each Product. It is of course possible to remove redundant copies from the resulting list afterwards but it is neither elegant nor efficient especially since this type of query is needed in many parts of our application, sometimes more than one level deep (subqueries with subqueries).

The question is still open: Is it possible to use subqueries with associations mapped as composite-elements?

Peter
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Sep 13, 2004 3:29 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
So use "select distinct prod from .....", you don't need a subselect for this.

I'm not sure, but this might also work:

Code:
from Product prod
where 0 < ( select count(*) from prod.parts where part.psize=5 and part.cost<10 )
Top
 Profile  
 
Teg
 Post subject:
PostPosted: Mon Sep 13, 2004 3:44 am 
Regular
Regular

Joined: Wed Aug 18, 2004 5:16 am
Posts: 69
Location: Modena, Italy
I have tried this:
Code:
from Product prod
where 0 < ( select count(*) from prod.parts where part.psize=5 and part.cost<10 )

and the message in Hibern8IDE is:
Code:
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: ORA-00904: "PART"."COST": invalid identifier


How can Hibernate recognize "part" without a join?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Sep 13, 2004 3:50 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
obviously that was a mistype. add the alias
Top
 Profile  
 
Teg
 Post subject:
PostPosted: Mon Sep 13, 2004 3:53 am 
Regular
Regular

Joined: Wed Aug 18, 2004 5:16 am
Posts: 69
Location: Modena, Italy
Sorry ;-)
this work:
Code:
from Product prod where 0 < ( select count(*) from prod.parts part where part.psize=5 and part.cost<10 )
Top
 Profile  
 
PeterNilsson
 Post subject:
PostPosted: Mon Sep 13, 2004 5:30 am 
Newbie

Joined: Fri Aug 20, 2004 10:07 am
Posts: 9
Location: Sweden
Yes,
Code:
from Product prod where 0 < ( select count(*) from prod.parts part where part.psize=5 and part.cost<10 )

does work and solves my problem.

However, the generated SQL is not really efficient. The count prevents an early exit, it would be desireable to use a query similar to
Code:
from Product prod where exists ( select 1 from prod.parts part where part.psize=5 and part.cost<10 )


To get really good performance from subqueries we need to be able to generate SQL of two types:

Code:
SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM Table2 WHERE Table1.column1 = Table2.column1)

This would execute with a nested out-to-in loop and is preferable when Table1 is smaller or the query has additional restrictions on Table1.

Code:
SELECT * FROM Table1 WHERE Table1.column1 IN (SELECT Table2.column1 FROM Table2)

This would execute with a nested in-to-out loop and is preferable when Table2 is smaller or the query has additional restrictions on Table2.

Is it possible to get Hibernate to generate these types of subqueries? Hibernate3?

Peter
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Sep 13, 2004 5:42 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
The only thing that stops you is the HQL select clause does not support constants or expressions. So, I guess you could put one of the known SQL functions, eg. SYSDATE, or something, in the select clause. Check the Dialect.
Top
 Profile  
 
PeterNilsson
 Post subject:
PostPosted: Mon Sep 13, 2004 10:34 am 
Newbie

Joined: Fri Aug 20, 2004 10:07 am
Posts: 9
Location: Sweden
Ok, the exist subquery now works fine:

Code:
from Product prod where exists ( select part.psize from prod.parts part where part.psize=5 and part.cost<10 )


The remaining problems are the cases where the subquery is very restricted, ie satisfies very few rows of the inner table, while the outer query has few/no conditions and the outer table is large.

If we use the above query for that case the SQL will be very inefficient as it will evaluate the subquery for all rows in the outer table. What we need in this case is an "in"-subquery, like:

Code:
from Product prod where prod.id in (select part.product_fk from prod.parts part where part.psize=5 and part.cost<10 )


This HQL is of course not valid as "product_fk" is not a property of Part, it is only a key in the composite-element mapping.
Is it possible to write HQL that behaves like the above query?

Peter
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Sep 13, 2004 10:40 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
of course, just use part.product.id
Top
 Profile  
 
PeterNilsson
 Post subject:
PostPosted: Mon Sep 13, 2004 1:00 pm 
Newbie

Joined: Fri Aug 20, 2004 10:07 am
Posts: 9
Location: Sweden
Is this the query you mean?

Code:
from Product prod where prod.id in (select part.product.id from prod.parts part where part.psize=5 and part.cost<10 )

I get an exception when parsing the query:

Code:
net.sf.hibernate.QueryException: could not resolve property: product of: Part [from Product prod where prod.id in (select part.product.id from prod.parts part where part.psize=5 and part.cost<10 )]
   at net.sf.hibernate.persister.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38)
   at net.sf.hibernate.collection.AbstractCollectionPersister.toType(AbstractCollectionPersister.java:663)
   at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:249)
   at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:123)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
   at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:170)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
   at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:124)
   at net.sf.hibernate.hql.WhereParser.token(WhereParser.java:212)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:142)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1520)


I tried adding <parent name="product"/> to the composite element but it didn't help.

What does "product" in "part.product.id" correspond to? The name of the outer class? The name of a parent-mapping?

Peter
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
  [ 12 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.