help with HQL and collections please

teknokrat · **Joined:** Thu Aug 28, 2003 10:54 am **Posts:** 67

I have a customer object which has a collection of request objects. i want a query that will return all customers that have requests with a certain property - something like

from Customer c WHERE elements(c.requests).property='value'

unfortunately, the above does not work. Does anyone know how to acomplish this with HQL?

thanks

christian · **Posted:** Tue Oct 21, 2003 10:37 am

from Customer as c join c.requests as r where r.property = value;

feensturm · **Joined:** Wed Oct 22, 2003 5:24 am **Posts:** 5

Hi

i have the same problem with HQL and Collections.

i tried the proposal from Christian but i always get this exception:

net.sf.hibernate.QueryException: collection of values in from clause: searchat0_.searchEOList.elements [from foodexpert.model.SearchAttr as sa join sa.searchEOList as eolist where eolist.searchEO= :eoGroup]

i tried different things, none of them worked.

thanx for help!

regards pascal

gavin · **Posted:** Wed Oct 22, 2003 5:43 am

HIbernate 2.0.x doesn't allow collections of values in the from clause (only collections of entities). Hibernate 2.1 does.

feensturm · **Joined:** Wed Oct 22, 2003 5:24 am **Posts:** 5

hi

thanx Gavin that with the List works now. but how can i access the values of a map.

i tried everythig that is in the user guide described but nothing helped;(

i use 2.1 beta6
here my mapping:

<map name="propAmounts cascade="all">
<key column="expertobject"/>
<index column="property" type="long"/>
<element column="amount" type="float"/>
</map>

and here one of my attempts:

select food, from Food as food, food.propAmounts as pa, Property as p where pa=p.id and p.title='Vitamin' and pa.amount>12

error:
Error in DoSearchAction.perform net.sf.hibernate.QueryException: cannot derefere
nce scalar collection element: amount [select eo, pa from foodexpert.model.data.
Food as eo, eo.propAmounts as pa, foodexpert.model.data.Property as p where pa=p
.id and pa.amount = :value and p.title= :value2 order by eo.title asc]

thanx for help!

regards pascal

gavin · **Posted:** Thu Nov 13, 2003 9:22 am

Code:

select food
from Food as food, food.propAmounts as pa, Property as p 
where p.title='Vitamin' and pa>12 and index(pa) = p.id

Is that what you want??

you can also write that as:

Code:

select food
from Food as food,  Property as p 
where p.title='Vitamin' and food.propAmounts[p.id] >12 

Finally, you can use an <index-many-to-many>, if that is more elegant.

feensturm · **Joined:** Wed Oct 22, 2003 5:24 am **Posts:** 5

hi Gavin

that was exactly was i needed. thanx a lot.

regards pascal

gavin · **Posted:** Mon Nov 17, 2003 5:09 am

No problem - Good Luck!