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 ?

Well, do you need a sub-select for the List and then maybe you can put the order by in the sub-select? I don't have time to look at it right now. — K.Nicholas, Jun 29 '16 at 20:52
Thank you @Nicholas; how would you do the inner query with criteria api? — Andrea Ligios, Jun 29 '16 at 21:22
[This answer](http://stackoverflow.com/a/35915532/4754790) might be helpful. — Dragan Bozanovic, Jul 05 '16 at 17:30
I don't use Criteria API, but it should be straightforward to write subqueries there also. — Dragan Bozanovic, Jul 07 '16 at 11:00
@DraganBozanovic: it should, I agree, but I've no clues on how to do it (for the select-ed fields with Criteria API). BTW I've found that the problem was in the joins (what a donk). Feel free to answer with something canonical and more general purpose on the distinct and joins (without involving Criteria API), and I'll award you the bounty. Cheers — Andrea Ligios, Jul 07 '16 at 12:08

score 6 · Accepted Answer · answered Jul 07 '16 at 12:05

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.

score 4 · Answer 2 · answered Jun 02 '17 at 12:24

4

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

answered Jun 02 '17 at 12:24

luboskrnac

23,973
10
81
92

SELECT DISTINCT + ORDER BY in JPA 2 Criteria API

2 Answers2

Linked