[FIXED] hibernate sql query with distinct and order by

Issue

I have this query:

"select distinct d from Dance d " +
    "inner join d.meisterschaftDances ms " +
    "inner join ms.danceRegistrations dr " +
    "inner join dr.user u " +
    "where (u.id = :userId " +
    "or d.user.id = :userId) " +
    "AND ms.meisterschaft.open = FALSE " +
    "order by ms.meisterschaft.organizer.id, " +
    "d.discipline, d.age, d.category, " +
    "d.class"

The logic would be perfect for me but as we know distinct is not so easy with order by.

I get the same dance several times because the inner join to meisterschaftDances can have several references.

Sadly I dont know how to rewrite my query in such a way that it is working. Can somebody show me an example which would work. Thanks

Solution

As it's stated in the hibernate documentation:

For JPQL and HQL, DISTINCT has two meanings:

1. It can be passed to the database so that duplicates are removed from a result set

2. It can be used to filter out the same parent entity references when join fetching a child collection

You are interested in the second case, so, you need to add the QueryHints.HINT_PASS_DISTINCT_THROUGH hint, like below:

List<Dance> dances = entityManager.createQuery(
    "select distinct d " +
    "from Dance d " +
    "inner join d.meisterschaftDances ms  ...", Dance.class)
.setHint( QueryHints.HINT_PASS_DISTINCT_THROUGH, false )
.getResultList();

P.S. But the above approach may be not workable in some hibernate versions due to bugs (See for example HHH-13517 and HHH-13280)

Answered By - SternK