Select the last (or first) inserted element of a OneToMany relationship using Criteria Query?

Question

Consider entity A with a unidirectional @OneToMany relationship to entity B. B has a persistent timestamp field set when persisted and never changed thereafter.

How do I make a criteria (tuple) query that returns for each A the last (or first) B added to the collection?

Here's my take at it so far:

// Root query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<A> root = q.from(A.class);
Join<A, B> bItem = root.join("listOfB");

// Subquery 
Subquery<Date> sq = q.subquery(Date.class);
Join<A, B> sqBItem = sq.correlate(bItem);
Expression<Date> sqBItemCreatedOn = sqBItem.get("createdOn"); // <-- TS on sqBItem
sq.select(cb.greatest(sqBItemCreatedOn)).groupBy(root);

// Back to the root query
Expression<Date> bItemCreatedOn = bItem.get("createdOn"); // <-- TS on bItem    
q.multiselect(root, bItem).where(cb.equal(bItemCreatedOn, sq));

em.createQuery(q).getResultList(); // <-- fails

I also tried with

q.multiselect(root, cntItem).where(cb.in(cntCreatedOn).value(sq));

but to no avail.

Please note that the relationship is unidirectional at this stage so I wonder if the subquery part is working as it should. By making the relationship bidirectional, I guess I could query B instead and group on A but I have no control over the domain model.

Answer 1

Here is my attempt (without a subquery):

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<A> root = q.from(A.class);
Join<A, B> bItem = root.join("listOfB");
q.multiselect(
    root.alias("A"), 
    cb.greatest(bItem.<Date>get("createdOn")).alias("TS")); //MAX
//  cb.least(bItem.<Date>get("createdOn")).alias("TS")); //MIN
q.orderBy(cb.asc(root));
q.groupBy(root);

List<Tuple> tuples = em.createQuery(q).getResultList()
for (Tuple array : result) {
    System.out.println(array.get("A").toString() + ":\t" + array.get("TS"));
}

From JPQL perspective this might look like:

SELECT root, MAX(b.createdOn)
FROM A root JOIN root.listOfB bItem
GROUP BY root
ORDER BY root;