2

I want to execute following query:

from Item i where i.categoryItems.catalogId = :catId

That however yields in following exception: illegal attempt to dereference collection So I googled, found this Hibernate forum post https://forum.hibernate.org/viewtopic.php?p=2349920 that recommended me to do the following:

from Item i, IN (i.categoryItems) WHERE i.catalogId = :catId

This kind of works, but there's a problem with this: It returns me an Object array with Item object and CategoryItem object. I'm only interested in the single Item object (List)

My mapping of 'Item': 

<hibernate-mapping package="be.xx.xx.xx.xx.domain" default-access="field">
  <class name="Item" table="ITEM">  
    <id name="articleId" column="article_id" type="long">
        <generator class="assigned" />
    </id>
...
...
        <set name="categoryItems" table="CATEGORY_ITEM">
            <key column="item_id" />
            <one-to-many class="be.xx.xx.xx.xx.domain.CategoryItem" />
    </set>
</class>
</hibernate-mapping>

Anybody got any ideas?

Thanks

Bart Vangeneugden
  • 3,436
  • 4
  • 33
  • 52
  • According to you commend: please read my answer again. -- The key point is not the SELECT, the key is "inner join" -- then your first attempt sould work too. – Ralph Dec 20 '10 at 12:57

1 Answers1

5

Try:

SELECT i FROM Item i inner join i.categoryItems cat WHERE cat.id = :catID

Explanation: The navigation you have tried: i.categoryItems.catalogId works only for 1:1 or n:1 relations, but not for 1:n. -- For 1:n you have to use the explicite join operation.

Ralph
  • 118,862
  • 56
  • 287
  • 383
  • Is it possible to select `i.categoryItems` instead of `i`, while applying the filter on the returned list? – sp00m Mar 13 '13 at 10:20
  • @Ralph I'm afraid the whole list of `categoryItems` is returned, without being filtered by the query's conditions. – sp00m Mar 13 '13 at 10:36
  • @Ralph Selecting `cat` seems to work though (see [this answer](http://stackoverflow.com/a/15382581/1225328)). – sp00m Mar 13 '13 at 11:08