I have the following SQL query that I'm trying to write using Criteria JPA.
select op3.* from operator op3 where (op3.mp, op3.date_to, op3.date_from) in
(select op.mp, op.date_to, op.date_from from operator op
group by op.mp, op.date_to, op.date_from
having count(*) > 1 or min(gross_price) < 0);
I've written the code, but i am not able to select multiple fields from subquery. Right now i select only the mp, but i need to select also the date_from and date_to and to add them in the "in" condition from criteriaBuilder.
fun method(): Specification<Operator> {
return Specification { root, query, cb ->
val subquery =
query.subquery(Long::class.java)
val groupBySubQueryRoot =
subquery
.from(Operator::class.java)
subquery.select(
groupBySubQueryRoot.get(
Operator_.mp
)
)
.groupBy(
groupBySubQueryRoot.get(Operator_.mp),
groupBySubQueryRoot.get(Operator_.dateFrom),
groupBySubQueryRoot.get(Operator_.dateTo)
)
.having(
cb.or(
cb.gt(cb.count(groupBySubQueryRoot), 1),
cb.lt(cb.min(groupBySubQueryRoot.get(Operator_.grossPrice)), 0)
)
)
cb.`in`(root.get(Operator_.mp)).value(subquery)
}
}