How to use multiple values in WHERE using JPA CriteriaBuilder

Question

I have an example MyTable with 3 columns - id, common_id, creation_date, where common_id groups entries.

Now I would like to select using CriteriaBuilder all newest entries from each group (that is for each common_id get me latest creation_date).

In SQL the query would look like this:

select * from MyTable where (common_id, creation_date) in (select common_id, max(creation_date) from MyTable group by common_id)

Now I have tried to create the where predicate by writing something like (cb is CriteriaBuilder, root is a Root):

cb.array(root.get('common_id'), cb.max(root.get('creation_date')))
                                .in(
                                    query.subquery(MyTable.class)
                                            .select(cb.array(root.get('common_id'), cb.max(root.get('creation_date'))))
                                            .groupBy(root.get('common_id')))

But unfortunately cb.array is not an Expression (it's a CompoundSelect), so I cannot use .in() on it.

Thanks for pointers!

Answer 1

could you create it using JPQL? As far that I know, that is not possible.

I looked at the Spect (4.6.16 Subqueries) and it talk about "simples select expression":

simple_select_clause ::= SELECT [DISTINCT] simple_select_expression

I believe that only one return is possible, if you look at the examples there you will not find anything like it.

You will need to use NativeQuery for it.