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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With