If an entity is requested by a distinct query and the result should be ordered by an attribute of a joined entity e.g. of an OneToMany relation the query created by hibernate does not contain the order attribute in the result list.
Databases like postgres and h2 can not process this query. The reason why is decribed in places like this:
https://github.com/h2database/h2database/issues/408#issuecomment-262641613
This functionality is essential for many cases where complex data is filtered and ordered in paged tables with complex queries.
Without distinct there is no problem, as soon as distinct is necessary and can't be avoided the query fails.
Example (also provided as testcase):
@Entity
public class Task {
@OneToMany
private List<User> user;
}
@Entity
public class User {
private String name;
}
...
// query with distinct an orderBy producing error
Root<Task> from = query.from(Task.class);
query.distinct(true);
query.orderBy(cb.asc(from.join("user").get("name")));
entityManager.createQuery(query).getResultList();
|
|
