SELECT DISTINCT + ORDER BY in JPA 2 Criteria API

Question

I've a class Lawsuit, that contains a List<Hearing>, each one with a Date attribute.

I need to select all the Lawsuits ordered by the date of their Hearings

I've a CriteriaQuery like

CriteriaBuilder           cb = em.getCriteriaBuilder();
CriteriaQuery<Lawsuit>    cq = cb.createQuery(Lawsuit.class);
Root<Lawsuit>           root = cq.from(Lawsuit.class);

I use distinct to flatten the results:

cq.select(root).distinct(true);

I then join Lawsuit with Hearing

Join<Lawsuit, Hearing> hearing = root.join("hearings", JoinType.INNER);

to create Predicates

predicateList.add(cb.isNotNull(hearing.<Date>get("date")));

and Orders:

orderList.add(cb.asc(hearing.<Date>get("date")));

Everything works fine if I avoid distinct, but if I use it, it complains about not being able to order based on fields that are not in the SELECT:

Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The List<Hearing> is already accessible through the Lawsuit classes returned, so I'm confused: how should I add them to the select list ?

Answer 1

I've discovered the source of the problem somewhere else, and solving it has made unnecessary to do what asked in the question; as described in other answers, it should be unnecessary to perform the distinct here.

The duplicate rows were originated by erroneous left joins that were performed on collections (attributes of the root object) even if the predicates were not been used:

Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.LEFT);
if (witnessToFilterWith!=null) {
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

The join should obviously be performed as inner and only if needed:

if (witnessToFilterWith!=null) {
    Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.INNER);
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

So, if you're here because you're getting the same problem, search the problem in the joins.

Answer 2

You can also de-duplicate via group by based on primary key column of root table:

 cq.groupBy(root.get("id")); // Assuming that Lawsuite.id is primary key column