CODEWITHSUNDEEP
javahibernatejpaormjpql
Konstantin Bakaras
Konstantin Bakaras

Reputation: 1

'ORDER BY' in JPQL-query adds 'CROSS JOIN' to produced sql thus reducing final results

I’m to create query with ordering by field of related entity, like this:

SELECT p FROM Patient p ORDER BY p.doctor.name

And sql-query that Hibernate builds based on that of JPQL uses CROSS JOIN with condition (effectively, inner join):

select patient0_.id as id1_1_, patient0_.doctor_id as doctor_i3_1_, patient0_.name as name2_1_
from patient patient0_ cross join doctor doctor1_
where patient0_.doctor_id=doctor1_.id
order by doctor1_.name

As a side-effect, all patients with nulls in ‘doctor’ field are excluded from result set. Is there any option I can switch to hint Hibernate to use LEFT JOIN in such cases? Can’t such a behavior be considered a bug? From common sense’ point, just adding ordering shoud not affect result count.

Example is intentionally simplified. In real world it’s a dynamically built criteria-query with variable set of filters and sortings. So I can not work it around and use explicit LEFT JOIN in JPQL.

I reproduced behavior of simplified example in version 5.3.9.Final and in latest 5.4.15.Final.

Appreciate any help.

Upvotes: 0

Views: 1089

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16430

This is how the implicit join is defined for JPA, as an inner join. So if you want to use left join semantics, you will have to use a an explicit left join.

If you want something more dynamic, I can recommend you take a look at a query builder like Blaze-Persistence that has left join semantics for implicit joins.

Here is a Spring WebMvc example application that supports dynamic filtering and sorting for paginated datatables: https://github.com/Blazebit/blaze-persistence/tree/master/examples/spring-data-webmvc

Upvotes: 0

Sibel Genç
Sibel Genç

Reputation: 54

You should use explicit join instead of hibernate implicit join.

SELECT p FROM Patient p left join fetch p.doctor d ORDER BY d.name

If there is lazy relation and it is not wanted that doctor is loaded, fetch should not be used. 

SELECT p FROM Patient p left join p.doctor d ORDER BY d.name

Upvotes: 1

Related Questions