HQL related to collections in many-to-many relationship

shanika · **Joined:** Fri Jun 25, 2004 5:31 am **Posts:** 31

Hibernate version: 2.1.7c

I'm having a problem in getting the proper HQL for the follwong senario:

Tables : ITEM (ItemID, name)
ORDER (OrderID, Description)
ITEM_ORDER (OrderID,ItemID)

I have done the many-to-many relationship mapping. (In this, the ITEM has a set of Orders and The Orders has a set of Items)
ITEM is defined as the inverse end. The records for ITEM_ORDER table gets inserted when calling the session.save(order) and works fine.

Now, how to write the HQL to find out all the Orders objects that contains Item "ITM_01" ? In other words, i need to get all the Orders objects, that has Item "ITM_01" in their Item set.

please help.

shanika · **Joined:** Fri Jun 25, 2004 5:31 am **Posts:** 31

oh, i found it and so impressed to see how simple it is :

Code:

select order from ORDER as order,ITEM as item where item.itemID like 'ITM_01' and item in elements(order.items) 

gavin · **Posted:** Tue Dec 07, 2004 2:38 am

No. Use:

Code:

select order 
from Order as order 
  join order.items as item
where item.itemID like 'ITM_01'

shanika · **Joined:** Fri Jun 25, 2004 5:31 am **Posts:** 31

thanks Gavin.

I had a good look at the SQL query that was generated behind the query that i had used and the query that you have given.

The difference i saw was that mine was using a subquery whereas the use of Join did it without using a subquery. According to my knowledge use of sub queries are meant to be much efficient in terms of performance.

So, was my query not the best way, since it violates the hibernate concept in terms of database independance? (Not all databases support subqueries). Keen to know why it was incorrect.

thanks again
Shanika

anthony · **Posted:** Thu Dec 09, 2004 3:11 am

Quote:

The difference i saw was that mine was using a subquery whereas the use of Join did it without using a subquery. According to my knowledge use of sub queries are meant to be much efficient in terms of performance.

false, run the two queries with a database with a lot of datas, Gavin's query will run much faster.

shanika · **Joined:** Fri Jun 25, 2004 5:31 am **Posts:** 31

thanks. so it's the performance difference.

thanks for the prompt reply.