Hello,
linked to my previous topic : https://forum.hibernate.org/viewtopic.php?f=1&t=1035182 and with the same entity model, I have another trouble.
I want to query all the lamps which are in the floor location and its sub-locations. So I created a new query which is :
Quote:
@Query("SELECT lamp FROM Lamp lamp WHERE (lamp.location.class = Floor AND lamp.location.code = :code) OR (lamp.location.class = Room AND lamp.location.floor.code = :code)")
public List<LampEntity> findAllLocatedInsideFloor(@Param("code") String code);
The compilation and execution of the query works fine but I don't have expected results, only the lamps located in room are returned.
The generated SQL query is the following (fileds have been renamed for clarify) :
Code:
select lamp.ID as ID1_3_, ... (other fields)...
from LAMP lamp
cross join LOCATION location
cross join LOCATION floor
where
lamp.LOCATION_ID=location.ID
and location.OWNER_ID=floor.ID
and (location.DTYPE='floor' and location.CODE=? or location.DTYPE='room' and floor.CODE=?)
The first indice is on the "OR" clause which doesn't have brackets
With the following query I'm able to get the correct results :
Quote:
@Query("SELECT lamp FROM Lamp lamp LEFT JOIN lamp.location floor LEFT JOIN lamp.location.floor roomFloor WHERE floor.code = :code or roomFloor.code = :code)")
public List<DeviceEntity> findAllLocatedInsideFloor(@Param("code") String code);
Is there an error in my first query or is there a bug ?
Thanks in advance
Login
FAQ
Search