Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA CriteriaBuilder for join in subquery

Tags:

sql

hibernate

jpa

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

like image 962
Marko Zadravec Avatar asked Dec 25 '22 09:12

Marko Zadravec


1 Answers

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.

like image 167
Marcelo Barros Avatar answered Dec 27 '22 04:12

Marcelo Barros