Search code examples
javajpagroup-byeclipselinkcriteria-api

Eclipselink group by is not working

How to add groupBy criteria to the code below? Because if I add criteriaQuery.groupBy(from.get(minutis.Preke_.prId)); - I get exactly the same SQL statement (without groupBy):

CriteriaBuilder cb = MinutisManager.getInstance().getCriteriaBuilder();
CriteriaQuery criteriaQuery = cb.createQuery(minutis.Preke.class);
Root<minutis.Preke> from = criteriaQuery.from(minutis.Preke.class);
from.fetch(minutis.Preke_.tiekejai, JoinType.LEFT).fetch(minutis.PrekeTiekejas_.tiekejas, JoinType.LEFT);
//criteriaQuery.groupBy(from.get(minutis.Preke_.prId));

TypedQuery<minutis.Preke> typedQuery = MinutisManager.getInstance().createQuery(criteriaQuery);
typedQuery.setFirstResult(0);
typedQuery.setMaxResults(100);
typedQuery.getResultList();

EDIT 1:

criteriaQuery.distinct(true) is not an option for me. Because that command hangs the whole statement and if I use EXPLAIN: When distinct is uesd

If I use GROUP BY on the query, then EXPLAIN is: enter image description here

EDIT 2:

I get this SQL statement with and without criteriaQuery.groupBy(from.get(minutis.Preke_.prId));

SELECT ... FROM preke t1 LEFT OUTER JOIN preke_tiekejas t0 ON (t0.pr_id = t1.pr_id) LEFT OUTER JOIN tiekejas t2 ON (t2.tiek_id = t0.tiek_id) LEFT OUTER JOIN gamintojas t3 ON (t3.gam_id = t1.gam_id) LEFT OUTER JOIN google_compare t4 ON (t4.pr_id = t1.pr_id) LEFT OUTER JOIN grupe t5 ON (t5.pgs_id = t1.pgs_id) LEFT OUTER JOIN preke_kaina t6 ON (t6.pr_id = t1.pr_id) ORDER BY t1.pr_id LIMIT ?, ?
Solution

  • I will file a bug in Eclipselink, because if I change fetch line with QueryHints, everything works:

    Change this (with this line I get duplicate Preke entities with populated children entities):

    from.fetch(minutis.Preke_.tiekejai, JoinType.LEFT).fetch(minutis.PrekeTiekejas_.tiekejas, JoinType.LEFT);

    To this (with this lines I get unique Preke entities with populated children entities):

    typedQuery.setHint(QueryHints.LEFT_FETCH, "Preke.tiekejai");
typedQuery.setHint(QueryHints.LEFT_FETCH, "Preke.tiekejai.tiekejas");

    I get my desired result.

    EDIT 1:

    The bug really exists, now max resulsts is not working. Both cases typedQuery is identical.

    typedQuery.setMaxResults(100);
System.out.println(typedQuery.getResultList().size()); //prints 73

typedQuery.setMaxResults(500);
System.out.println(typedQuery.getResultList().size()); //prints 413