HQL Select problem:
I am trying to build an application that dynamically builds queries depending on what table fields a user wants to view and i am having a few problems selecting property values through a join table. I have a many-to-many table mapping going through a join table:
Code:
<class name="Officer" table="OFFICER">
<id ..../>
<set name="companies" table="COMPANY_OFFICER">
<key column="OFFICER_ID"/>
<many-to-many column="COMPANY_ID"
class="Company"/>
</set>
<properties.../>
</class>
<class name="Company" table="COMPANY">
<id ..../>
<set name="officers" table="COMPANY_OFFICER">
<key column="COMPANY_ID"/>
<many-to-many column="OFFICER_ID"
class="Officer"/>
</set>
<properties.../>
</class>
and I am running the following HQL:
Code:
SELECT baseTable.forename,
baseTable.surname,
baseTable.companies.companyName,
baseTable.companies.companyStatus
FROM Officer baseTable
WHERE baseTable.officerId IN ( :constaints )
ORDER BY baseTable.companies.companyName ASC
The generated SQL is:
Code:
SELECT searchoffi0_.forename AS col_0_0_,
searchoffi0_.surname AS col_1_0_,
searchcomp2_.company_name AS col_2_0_,
searchcomp4_.company_status AS col_3_0_,
searchoffi0_.nationality AS col_4_0_
FROM search_officer_public searchoffi0_,
search_company_officer companies1_,
search_company_public searchcomp2_,
search_company_officer companies3_,
search_company_public searchcomp4_,
search_company_officer companies5_,
search_company_public searchcomp6_
WHERE searchoffi0_.officer_id = companies5_.officer_id
AND companies5_.company_id = searchcomp6_.company_id
AND searchoffi0_.officer_id = companies3_.officer_id
AND companies3_.company_id = searchcomp4_.company_id
AND searchoffi0_.officer_id = companies1_.officer_id
AND companies1_.company_id = searchcomp2_.company_id
AND (searchoffi0_.officer_id IN (?, ?, ?))
ORDER BY searchcomp6_.company_name ASC
The more select options i add from Company the more join tables are added. I am not sure whether there is something i should be adding to my mapping file, whether this is a bug or whether i am asking hibernate to do something it isn't meant to do?
Does anybody have any ideas.
Thanks
Tam[/code]
Login
FAQ
Search