|JPQL where condition problem [message #631189]
||Wed, 06 October 2010 10:11
| No real name
Registered: April 2010
I have a problem with where condition in my query.
There is a table A and a table B, the relation between these tables (A to B) is many-to-one. I want to fetch all A entities, whitch have a relation to B or a column of the A table has a specific value. So I wrote this query:
select a from A a
left join fetch a.b
where ... and (a.b is not null or a.type in(1,2,3,4)) and ....
This query fetches only entities with relation to B, but not the entities without this relation and with type 1,2... When I remove the part "a.b is not null or", the query fetches entites with type 1,2,3... as expected. So there is a problem in or. I'm not very experienced in JPLQ, so maybe i'm doing something wrong.
I'm using Eclipselink 2.0.2, JDK 1.6.20, query tested with JPA query tool.
Thank you for any help.
|Re: JPQL where condition problem [message #631266 is a reply to message #631189]
||Wed, 06 October 2010 14:32
| Chris Delahunt
Registered: July 2009
This is a common problem, as using A.b in JPQL forces an inner join to be used on the tables. Even though you have defined an explicit outer join in the fetch clause, you are again defining a different A->B relationship in the where clause, and it must use the inner join due to JPQL rules. If you see the generated SQL, you will likely see two joins occuring; 1 for the fetching of the related B objects, and then an inner join associated to the conditions in the where clause. |
What you will need to do is specify an explicit outer join, something like:
"select a from A a left join a.b b ... where ... and (b is not null or ...)"
This will give you the results you want, but will not fetch join the B relationship. You can either add another left join fetch for the a.b relationship, or use the EclipseLink fetch query hint to keep the JPQL query a bit cleaner:
Powered by FUDForum
. Page generated in 0.06321 seconds