java - SELECT DISTINCT + ORDER BY in JPA 2 Criteria API -

i've class lawsuit, contains list<hearing>, each 1 date attribute.

i need select lawsuits ordered date of hearings

i've criteriaquery like

criteriabuilder           cb = em.getcriteriabuilder(); criteriaquery<lawsuit>    cq = cb.createquery(lawsuit.class); root<lawsuit>           root = cq.from(lawsuit.class); 

i use distinct flatten results:

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

i join lawsuit 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 avoid distinct, if use it, complains not being able order based on fields not in select:

caused by: org.postgresql.util.psqlexception: error: select distinct, order by expressions must appear in select list

the list<hearing> accessible through lawsuit classes returned, i'm confused: how should add them select list ?

i've discovered source of problem somewhere else, , solving has made unnecessary asked in question; described in other answers, should unnecessary perform distinct here.

the duplicate rows originated erroneous left joins performed on collections (attributes of root object) if predicates 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 performed inner , 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 same problem, search problem in joins.