CODEWITHSUNDEEP
javahibernatejpacriteria-api
Maxim Markov
Maxim Markov

Reputation: 101

JPA Criteria query with inner join of aggregation

I'm trying to write a CriteriaQuery which will query latest observation for each city. City is defined by city_code field, while latest record is defined by observation_time field.

I can easily write it in a plain SQL, but I cant understand how to do it with jpa criteria api.

select distinct m.* from 
 (select city_code cc, max(observation_time) mo
 from observations group by city_code) mx, observations m 
 where m.city_code = mx.cc and m.observation_time = mx.mo`

Upvotes: 0

Views: 878

Answers (2)

snieguu
snieguu

Reputation: 2283

It is possible when You are open for loose efficiency. So first let's transform our query to logical equivalent one:

select distinct m.* from observations m where 
m.observation_time = (select max(inn. observation_time) from observations inn 
                      where inn.city_code = m.city_code);

then let's translate it to JPA CriteriaQuery:

public List<Observation> maxForEveryWithSubquery() {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Observation> query = builder.createQuery(Observation.class);
    Root<Observation> observation = query.from(Observation.class);
    query.select(observation);

    Subquery<LocalDateTime> subQuery = query.subquery(LocalDateTime.class);
    Root<Observation> observationInner = subQuery.from(Observation.class);
    subQuery.where(
            builder.equal(
                    observation.get(Observation_.cityCode),
                    observationInner.get(Observation_.cityCode)
            )
    );
    Subquery<LocalDateTime> subSelect = subQuery.select(builder.greatest(observationInner.get(Observation_.observationTime)));
    query.where(
            builder.equal(subSelect.getSelection(), observation.get(Observation_.observationTime))
    );
    TypedQuery<Observation> typedQuery = entityManager.createQuery(query);
    return typedQuery.getResultList();
}

Upvotes: 1

Konstantin Triger
Konstantin Triger

Reputation: 1741

Unfortunately JPA does not support sub queries in FROM clause. You need to write a native query or use framework like FluentJPA.

Upvotes: 0

Related Questions