Hi
I am using Hibernate 3.2.4 on oracle 10.
I am trying to force order of joins in hql but with no sucess.
i have following objects and relationships:
Instrument
CurrentPricing
Currency
Instrument has many currentPricings (set) and currentPricing object has single currency object.
I would like to create query that searches instrument by code and eagerly loads currentPricings and currency.
Since relationship from instrument to currentPricings is not obligatory i am using outer join:
Code:
select i from Instrument i left join fetch i.currentPricings cp join fetch cp.currency where i.instrCode =?
The generated sql looks like:
Code:
from
"INSTRUMENTS" instrument0_
left outer join
"CURRENT_PRICINGS" currentpri2_
on instrument0_."INSTR_ID"=currentpri2_.INSTR_ID
inner join
"CURRENCIES" currency3_
on currentpri2_.CURR_ID=currency3_."CURR_ID"
where
instrument0_."INSTR_CODE"=?
The joins are made from left to right and it means that INSTRUMENTS and CURRENT_PRICINGS are joined together (using left outer join) and then the result of the operation is merged to Currency table.
As a result instruments with no currentPricings are not retrieved...
Obviously i could rewrite the query to use left outer join to join currency
Code:
select i from Instrument i left join fetch i.currentPricings cp left join fetch cp.currency where i.instrCode =?
But this time i could retireve currenctPricing that do not have currency - i would like to avoid it
Using plain sql i could do sth like:
Code:
from
"INSTRUMENTS" instrument0_
left outer join (
"CURRENT_PRICINGS" currentpri2_
inner join
"CURRENCIES" currency3_
on currentpri2_.CURR_ID=currency3_."CURR_ID"
)
on instrument0_."INSTR_ID"=currentpri2_.INSTR_ID
It uses parenthesis to define order of joins - first joining currentPricing with currency and then the result is joined to instruments.
Is it possible to specify order of joins in HQL ???
Login
FAQ
Search