CODEWITHSUNDEEP
hibernatejoinwhere-clausefetch
ReggieK123
ReggieK123

Reputation: 121

Hibernate JOIN FETCH when no relation exists

I have a Customer entity with @OneToMany Account children. I want perform fetch join to return a customer with its corresponding active accounts (having account.isActive = true). If the customer does not have any accounts it needs to be returned as well.

For the following JPQL, for a customer having inactive accounts, they are indeed filtered out, but a customer without any accounts is not returned:

   @NamedQuery(name = "Customer.findById", query = "SELECT c FROM Customer c LEFT JOIN FETCH c.accounts a WHERE c.id = :id AND a.isActive=true")

What would be the clean way to write the query so that both cases would be covered (customer with no accounts and customer with active accounts) ?

Upvotes: 3

Views: 1999

Answers (2)

ReggieK123
ReggieK123

Reputation: 121

So, basically, answering my own question - the solution was pretty easy:

 @NamedQuery(name = "Customer.findById", query = "SELECT c FROM Customer c LEFT JOIN FETCH c.accounts a WHERE c.id = :id AND (a is null OR a.isActive=true)")

Upvotes: 2

Ossin Java guy
Ossin Java guy

Reputation: 365

Use an inner query:

select c FROM Customer c LEFT JOIN FETCH (select a from accounts a WHERE a.isActive=true) WHERE c.id = :id

Upvotes: 0

Related Questions