EclipseLink Criteria API count in results after joining table

Question

After ours of searching I was still unable to find a way to write this SQL equivalent in EclipseLink criteria query:

SELECT preke.*, count(nuotrauka.prid) AS cnt FROM preke LEFT JOIN nuotrauka ON nuotrauka.prid=preke.prid WHERE trash = 1 GROUP BY preke.prid ORDER BY cnt DESC

I tried joins, multiselects and etc. I need getResultList() to return me List within List like list[0] - (Preke)preke1, list[1] - (Integer)count1; list[0] - (Preke)preke2, list[1] - (Integer)count2 ... .

EDIT 1:

CriteriaBuilder cb = EntityManager.getInstance().getCriteriaBuilder();
CriteriaQuery criteriaQuery = cb.createQuery(Tuple.class);
Root<Preke> from = criteriaQuery.from(Preke.class);                    
Expression<Long> count = cb.count(from.get("images"));

criteriaQuery.where(cb.equal(from.get("trash"), true));
criteriaQuery.multiselect(from.alias("preke"), count.alias("count"));
criteriaQuery.groupBy(from.get("prid"));

TypedQuery<Tuple> typedQuery = EntityManager.getInstance().createQuery(criteriaQuery);
typedQuery.setFirstResult(PAGE * ITEMS_PER_PAGE);
typedQuery.setMaxResults(ITEMS_PER_PAGE);

prekes = typedQuery.getResultList();

...

for(Tuple t : prekes) {
    Preke p = (Preke)t.get("preke");
    long count = (long)t.get("count");
    ...
}

It give me following JPQL statement:

SELECT  t0.prid AS a1, 
        ..., 
        COUNT(t1.id) 
FROM    preke t0, 
        nuotrauka t1 
WHERE 
        ((t0.trash = ?) AND (t1.prid = t0.prid))
GROUP BY t0.prid LIMIT ?, ?

This is almost fine, but it doesn't include results where count is 0.

As above JPQL statement says - t1.prid = t0.prid should be the bad part, how to replace it? I think what I need here is a LEFT JOIN. But how to do it?

It seems correct that the result doesn't include items where count is 0, because they don't satisfy the _where_ condition `trash=1`... — perissf, Nov 09 '12 at 12:42
'trash' and 'images count' is absolutely different things. trash is a field in preke (possible values 1 and 0) and count is records count in image table where prid is primary key. And BTW, the SQL statement is correct - it returns all the needed results. — Minutis, Nov 09 '12 at 12:48
Ok. You can see the resulting query by setting EclipseLink logging level =FINE in persistence.xml. However the problem seems to be in the missing join in your criteria query — perissf, Nov 09 '12 at 12:53
I updated an answer with JPQL statement and possible bad part, but I don't know how to fix it. — Minutis, Nov 09 '12 at 13:01
Can't help now, but you can see how joins work using these examples: http://stackoverflow.com/a/9025656/870122 and http://www.ibm.com/developerworks/java/library/j-typesafejpa/ — perissf, Nov 09 '12 at 13:23

score 1 · Accepted Answer · edited Nov 09 '12 at 13:41

1

Instead of using

Expression<Long> count = cb.count(from.get("images"));

try using

Join<Preke, Nuotrauka> images = from.join("images", JoinType.LEFT);
Expression<Long> count = cb.count(images);

edited Nov 09 '12 at 13:41

Minutis

1,193
1
17
47

answered Nov 09 '12 at 13:32

Chris

20,138
2
29
43

EclipseLink Criteria API count in results after joining table

1 Answers1