Spring Data JPA: CriteriaQuery to get entities with max value for each unique foreign key

user_2596109 0
Viewed 2218

There's an Event class:

@Entity
public class Event {

    @Id
    private Integer id;

    @ManyToOne(cascade = CascadeType.ALL)
    private Company company;

    @Column
    private Long time;

    ...

}

I want to have an EventFilter class (implementing Specification) which will produce CriteriaQuery to select entities the same way as the following SQL query:

SELECT *
FROM events e1
WHERE e1.time = (
    SELECT MAX(time)
    FROM events e2
    WHERE e1.company_id = c2.company_id
)

Filtered result will contain only events with unique Company and max time value per company.

This is the EventFilter class with what I ended up with:

public class EventFilter implements Specification<Event> {

    @Override
    public Predicate toPredicate(Root<Event> root, CriteriaQuery<?> q, CriteriaBuilder cb) {
        Subquery<Long> subquery = q.subquery(Long.class);
        Root<Event> subRoot = subquery.from(Event.class);
        subquery.select(cb.max(root.get("time")))
                .where(cb.equal(root.get("company"), subRoot.get("company")));
        return cb.equal(root.get("time"), subquery);
    }

}

When EventRepository#findAll(EventFilter filter) is called, results are not filtered at all. Please help me to implement this logic correctly.

spring-data-jpa criteria-api jpa hibernate sql
1 Answers

After inspecting SQL statement generated by Hibernate I've found an error: root was used instead of subRoot. The correct method body is:

Subquery<Long> sub = q.subquery(Long.class);     
Root<Event> subRoot = sub.from(Event.class);     
sub.select(cb.max(subRoot.get("time")))
   .where(cb.equal(root.get("company"), subRoot.get("company")));
return cb.equal(root.get("time"), sub);
user_25961090
Related