I have a Measurements table like this:
Measurements
___________________________________________________________________________________________________
| id | created_at | person_id | temperature |..... several columns with other values .... |
I want to use Spring Data Specification to query this table with dynamic list of search criteria. The challenge is that I want to get only the latest entries for each person_id that meet the search criteria. The query to get the latest results for each person can be something like this:
select *
from Measurements m
inner join (
select person_id, max(created_at) as MaxDate
from Measurements
group by person_id
) tm on p.person_id = tm.person_id and p.created_at = tm.MaxDate
How can I incorporate similar subquery to Specification with predicates for search criteria.
public static Specification<Measurements> findByCriteria(final SearchCriteria searchCriteria) {
return new Specification<Measurements >() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Patient> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<>();
if(searchCriteria.getTempMinLimit() != null) {
predicates.add(cb.greaterThanOrEqualTo(root.get("temperature"), searchCriteria.getTempMinLimit()));
}
/*
other predicates
*/
OK so I have found that subquery can be placed only to the WHERE part of the query. I had to divide it into two queries in the end.
I am still confused about the toPredicate method parameters (public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {).
When I tried to call select on the query parameter nothing happened. Can I use the query parameter directly or I have to create a new query intance with the CriteriaBuilder?
