In my program I have the goal to select objects from the table, which have many-to-many associations with objects from other table, which have a specified value in the particular column.
It's quite standart situation with two classes, which was 'many-to-many' associated by means third table with composite primary key. I write that HQL and it somehow work.
Code:
select obj.ID from MyObject obj where obj.MyAssociation.SomeValue = ?
But when I write something like this it gives unexpected results.
Code:
select obj.ID from MyObject obj where NOT( obj.MyAssociation.SomeValue = ? )
I look at generated SQL and find out the reason. The query used joint tables instead of the subquery. It cut off objects, that have no any association.
Code:
select slide0_.ID as col_0_0_
from sb_Slide slide0_, sb_SlideClassifier slideclass2_, sb_Slide_To_SlideClassifier slideclass1_
where (not(?=slideclass2_.SomeValue)
and slide0_.ID=slideclass1_.SlideID
and slideclass1_.SlideClassifierID=slideclass2_.ID)
Now question:
How to make Hibernate generate WHERE condition as subselect?I tried to write something like this,
Code:
select obj.ID from MyObject obj where ? in elements( obj.MyAssociation.SomeValue)
and it seems to work in right way, BUT...
I get SQL error from database!!! And generated SQL have error.
Code:
select slide0_.ID as col_0_0_
from sb_Slide slide0_, sb_SlideClassifier slideclass2_, sb_Slide_To_SlideClassifier slideclass1_
where (? in (
select slideclass2_.SlideClassifierID
from sb_Slide_To_SlideClassifier slideclass1_, sb_SlideClassifier slideclass2_
where slide0_.ID=slideclass1_.SlideID
and slideclass1_.SlideClassifierID=slideclass2_.ID
))
Expression '
select slideclass2_.SlideClassifierID' is incorrect. It must be '
select slideclass2_.SomeValue'. The column 'SlideClassifierID' exist in the table 'sb_Slide_To_SlideClassifier', which associate two classes with each other.
I try to submit this error as bug, but got rejected without any explanation.
May be function 'elements()' can be used only with mapped collection of objects? But it looks as it can work with values of that objects. It just need litle adjusting. In any case, if it does not work, it must give Hibernate exception instead of database-side error.
Login
FAQ
Search