CODEWITHSUNDEEP
sqlhibernatejpa
Marko Zadravec
Marko Zadravec

Reputation: 8730

JPA CriteriaBuilder for join in subquery

I have SQL statement something like this : 

SELECT * FROM person inner join (select max(validityId) as maxID from person group by personId) maxID on maxID.maxID = person.validityid;

So it give me "distinct" row for personID:

If I have table like this : 

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    10      | 400     |
|    1       |    11      | 500     |
|    1       |    12      | 600     |
|    2       |    13      | 700     |
|    2       |    14      | 800     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

it would return 

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    12      | 600     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

Now I try to do this by JPA CriteriaBuilder.

My first thought was sub query : 

final CriteriaBuilder cb = this.em.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Long> subquery = cq.subquery(Long.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(validityIDSQ.get(Person_.validityId));
subquery.groupBy(validityIDSQ.get(Person_.personId));

cb.where(cb.in(root.get(Person_.validityId)).value(subquery));

But this produce error 

 ERROR: column "person1_.validityid" must appear in the GROUP BY clause or be used in an aggregate function

How to this right?

Marko

Upvotes: 7

Views: 24276

Answers (1)

Marcelo Barros
Marcelo Barros

Reputation: 1048

I think the solution is simpler than it seems. You forgot to include cb.max () in the CriteriaBuilder subquery. The following code executes the query you are looking for.

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Integer> subquery = cq.subquery(Integer.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(cb.max(validityIDSQ.get(Person_.validityID)));
subquery.groupBy(validityIDSQ.get(Person_.personID));

cq.where(cb.in(root.get(Person_.validityID)).value(subquery));

This code will create the following query:

select
    person0_.id as id1_0_,
    person0_.personID as personID2_0_,
    person0_.validityID as validity3_0_,
    person0_.value as value4_0_
from
    person person0_
where
    person0_.validityID in (
            select
                max(person1_.validityID)
            from
                person person1_
            group by
                person1_.personID)

I think you are using Postgres. Without cd.max() it generates the error you cited because you use the GroupBy without using an aggregate function. I tested it on Postgres and MySQL. Runs like a charm on both.

Upvotes: 16

Related Questions