• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPQL left outer join with condition on the right side

 
Frankie Fuentes
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 145
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe this is planned for JPA 2.1 with the ON clause.
 
Avor Nadal
Ranch Hand
Posts: 145
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 .
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic