Need some helps on HQL

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

I have the following classes:

Code:

Category
CategorizedOrganization
Organization
Address

The relationships between them are the followings:

Category & CategorizedOrganization: one-to-many
CategorizedOrganization & Organization: one-to-one (the first one contains the second one)
Organziation & address: one-to-many

I need to find out all organizations under a given category and in a given city (one attribute of the address) .

The following HQL doesn't yield a right result.

Code:

select o from Category c left join c.categorizedOrganizations co left join fetch co.organzation o left join c.addresses a where c.id = ? and a.city = ? 

Can someone help me out on this HQL, please.

Hibernate version:
3.x

hm0 · **Joined:** Wed Feb 07, 2007 7:55 pm **Posts:** 18

Since the first half of the HQL is a condition in fact, a subquery seems to be a good fit. How about the folloing HQL?

Code:

select o from organzation o left join c.addresses a where o in (select  o2 from Category c left join c.categorizedOrganizations co left join co.organization o2 where c.id = ?) and a.city = ? 

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Shouldn't the statement be

select o from Category c left join c.categorizedOrganizations co left join co.organzation o left join o.addresses a where c.id = ? and a.city = ?

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

sethrohit1977 wrote:

Shouldn't the statement be

select o from Category c left join c.categorizedOrganizations co left join co.organzation o left join o.addresses a where c.id = ? and a.city = ?

The desired object won't be created without "fetch"?

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

hm0 wrote:

Since the first half of the HQL is a condition in fact, a subquery seems to be a good fit. How about the following HQL?

Code:

select o from organzation o left join c.addresses a where o in (select  o2 from Category c left join c.categorizedOrganizations co left join co.organization o2 where c.id = ?) and a.city = ? 

The subquery approach seems to make the thought clear.