Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL where condition problem
JPQL where condition problem [message #631189] Wed, 06 October 2010 10:11 Go to next message
No real name is currently offline No real name
Messages: 5
Registered: April 2010
Junior Member
Hi all,

I have a problem with where condition in my query.
The situation:
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
....{other joins}
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.

Michael
Re: JPQL where condition problem [message #631266 is a reply to message #631189] Wed, 06 October 2010 14:32 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1018
Registered: July 2009
Senior Member
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:
query.setHint(QueryHints.LEFT_FETCH, "a.b");

Best Regards,
Chris
Re: JPQL where condition problem [message #631345 is a reply to message #631189] Thu, 07 October 2010 03:22 Go to previous message
No real name is currently offline No real name
Messages: 5
Registered: April 2010
Junior Member
I' ve modified my query and now it works.

Thank you!

Michael
Previous Topic:Compatibility of TABLE_PER_CLASS inheritance strategy with composite keys
Next Topic:EclipseLink 2.1.1 / Problem with @ManyToOne(fetch = FetchType.LAZY)
Goto Forum:
  


Current Time: Tue Sep 02 00:21:14 EDT 2014

Powered by FUDForum. Page generated in 0.01742 seconds