JPA Criteria : in clause with many columns

Question

I am trying to write the following SQL query using the JPA Criteria API

SELECT * FROM Table1 a
WHERE (a.category, a.priority) IN (
SELECT a1.category, max(a1.priority) FROM Table1 a1 GROUP BY a1.category
)

Functionally, the query select the element of Table1 with the highest priority for each category.

categoryPriority = // defines the pair category/priority
mySubQuery = // defines the subquery
query.where(categoryPriority.in(mySubQuery)

This is schematically what I am looking for.

Defining the subquery is not a problem since it is well documented.

But I cannot find a way to define the couple (a.category, a.priority).

Answer 1

Multiple columns in a IN clause is not provided for in JPA. I think you would need to use a native query.

Reference: query for select which multiple values in the “IN” clause

Answer 2

An alternative approach is to use field concatenation

Create a method that returns the two fields you want to search in your DTO/Entity.

  public String getField1Field2Concatenated() {
    return field1+ field2;
  }


List<String> ids = list.stream().map(r -> r.getField1Field2Concatenated()).collect(Collectors.toList());

You can concatenate two fields and do the search.

Select e from Entity e where concat(e.field1,  c.field2) in (:ids)

If any of the fields are not text you can cast

Select e from Entity e where concat(cast(c.field1 as string),  c.field2) in (:ids)