HQL for min() over a referenced collection

korpe · **Joined:** Tue Nov 20, 2007 8:34 am **Posts:** 3

Hi,

i am looking for a way to get the minimal date of a collection of share prices for a given company. It should be something like:

Code:

select min(c.sharePrices.date) from Company c where c = :company

But I get an error message:

Code:

QueryException: illegal attempt to dereference collection [company0_.id.sharePrices] with element property reference [date]

Also the intuitive

Code:

select min(s.date) from SharePrice s, Company c where c = :company and s in c.sharePrices

does not work, because it is translated to:

Code:

select min(shareprice0_.date) as col_0_0_ from SharePrice shareprice0_, Company company1_, Company_SharePrice shareprice2_, SharePrice shareprice3_ where company1_.id=shareprice2_.Company_id and shareprice2_.sharePrices_id=shareprice3_.id and company1_.id=? and (shareprice0_.id in (.))

I am relatively new to Hibernate. Can anybody give me a hint on how to do this query properly?

Thanks!
korpe

ramnath · **Posted:** Thu Nov 22, 2007 3:19 am

Not tested the solution below give it a try,

select min(DATE as {c.sharePrices.date}) from Company c where c = :company

korpe · **Joined:** Tue Nov 20, 2007 8:34 am **Posts:** 3

Hi ramnath,

thanks for your help. Unfortunately that doesn't work neither. I get the following error message:

Code:

org.hibernate.QueryException: unexpected char: '{' [select min(DATE as {c.sharePrices.date}) from de.fraunhofer.iais.kd.sentigraph.model.Company c where c = :company]

Any other ideas?

korpe · **Joined:** Tue Nov 20, 2007 8:34 am **Posts:** 3

now I tried to use a left outer join:

Code:

select min(s.date)
from Company_SharePrice cs, Company c
left outer join SharePrice s with cs.sharePrices_id=s.id
where c = :company and cs.Company_id = c.id

But that doesn't work, because Company_SharePrice is no class, and thus, it is not mapped:

Code:

org.hibernate.hql.ast.QuerySyntaxException: Company_SharePrice is not mapped [select min(s.date)...

Do I have to write a SQL-Query here? Any hints?

Thanks,
korpe