File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes JPQL left outer join with condition on the right side Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPQL left outer join with condition on the right side" Watch "JPQL left outer join with condition on the right side" New topic
Author

JPQL left outer join with condition on the right side

Frankie Fuentes
Ranch Hand

Joined: Mar 28, 2010
Posts: 41
Hi everyone,

We know that left outer join will retrieve the entities from the left table even if entities from the right table are not present. Suppose we have the following relationship:



Now, to retrieve all A entities with or without B's, we execute the following statement:



What about for something like this?

1. Give me all entities from A and its B instances
2. B instances doesn't need to exist
3. If B instances exist, select only B instances whose ID is equal to x

In this case, we are putting a condition on the optional right table or right side instead of the putting condition on the left side.

In native SQL, it's something like this:



which is not the same as



The former retrieves all records from A regardless if records from B exist, in addition, records from B must have its ID equal to something.

The latter retrieves all records from A that has B records whose ID is equal to something.

My question is what is the JPQL for the former?

Thanks!
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 105

Hello!

First of all, my full apologies for taking an old thread back, but I think that this post from Frankie reflects my doubt exactly.

I've found lots of lacks in JPQL for these years (compared to the possibilities of pure SQL). In version 1.0, and also in 2.0. One of the most frustrating ones has been not being able to do conditional left outer joins, just like this thread exposes.

Fortunately, the API which I'm using (Hibernate) has a way to achieve this with the keyword WITH, which is equivalent in some way to ON in SQL, and even admits references of type many-to-one inside, using a simple dot notation.

But... Do you know if there is any plan from the creators of the JPQL standard to include a feature like this one?

Thank you.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
I believe this is planned for JPA 2.1 with the ON clause.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 105

Thank you James. I've looked for some information about JPA 2.1 and I've found this article: http://jaxenter.com/jpa-2-1-early-draft-reveals-java-ee-7-progress-40482.html . This mentions that ON will be a reserved keyword too .
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPQL left outer join with condition on the right side