JPA Criteria Query to find the MAX ID for each unique name

Question

EDIT: Apparently my prior question was not sufficient so I will try to make it more clear with this try.

I have a set of rows in the table, selected by predicate p1 below each of which have a unique value in the id column. The pathName column may have multiple instances of the same value. What want is to return only the entities that have a distinct pathName with the highest value id.

 CriteriaBuilder builder = em.getCriteriaBuilder();
 CriteriaQuery<TextDocument> query = builder.createQuery(TextDocument.class);
 Root<TextDocument> root = query.from(TextDocument.class);

 // descending ids, and group similar names together

 query.orderBy(builder.desc(root.get(TextDocument_.id)));
 query.groupBy(root.get(TextDocument_.pathName));

 // filter

 Predicate p1 = builder.equal(root.get(TextDocument_.parent), rootdoc);
 Predicate p2 = builder.equal(root.get(TextDocument_.id), builder.max(root.get(TextDocument_.id)));
 query.where(p1, p2);

The predicate p1 selects the documents that all have the same parent document and this of course works as long as it is by itself.

Predicate p2 is all wrong, however. Adding it causes an exception ERROR 42903: Invalid use of an aggregate function..

The solution to a similar query problem is shown here in SQL but I want to do it using JPA Criteria Query. I can't figure out how to make the nested SELECT syntax work.

To reiterate the problem with my own example I have the following data set

id  parent  pathName  
--- ------- --------- 
101     22  alpha
130     22  beta
250     22  charlie
251     22  alpha
339     22  beta
400     22  alpha
401     22  delta

The correct result (for parent=22) would be:

id  parent  pathName  
--- ------- --------- 
250     22  charlie
339     22  beta
400     22  alpha
401     22  delta

Can anyone help me finish this query?

What have you tried? What part of that SQL can't you replicate? JPQL and JPA Criteria require entities not tables and you havent posted it — , Nov 19 '18 at 07:39
@BillyFrost I restated the question with code does it make more sense to you now? — AlanObject, Nov 24 '18 at 03:24
@AnchikaAgarwal I don't recall if we solved it in JPA. Ultimately we converted the project to Elasticsearch so this problem was moot. — AlanObject, May 28 '20 at 18:43

score 1 · Answer 1 · answered Nov 19 '18 at 13:58

1

you can do this

SELECT * from [tableName] t1
WHERE not exists(SELECT * from [tableName]t2 where t1.name = t2.name and t2.id > t1.id)

answered Nov 19 '18 at 13:58

Reza Nasiri

1,360
1
6
19

1

The question asks for JPA Criteria ... is that JPA Criteria? Nope – Nov 19 '18 at 14:57

m4rccc · Answer 2 · 2020-11-16T18:45:12.337

Using Reza example for sql:

SELECT * from [tableName] t1
WHERE not exists(SELECT * from [tableName]t2 where t1.name = t2.name and t2.id > t1.id)

And assuming the existing Root< TableEntity > root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder, you can write the JPA Criteria:

Subquery<TableEntity> subQuery = criteriaQuery.subquery(TableEntity.class);
Root<TableEntity> subQueryRoot = subQuery.from(TableEntity.class);
subQuery.select(subQueryRoot)
        .where(criteriaBuilder.and(
                criteriaBuilder.equal(subQueryRoot.get(TableEntity_.name), root.get(TableEntity_.name)),
                criteriaBuilder.greaterThan(subQueryRoot.get(TableEntity_.id), root.get(TableEntity_.id))
        ));
predicates.add(criteriaBuilder.not(criteriaBuilder.exists(subQuery)));

JPA Criteria Query to find the MAX ID for each unique name

2 Answers2