Search code examples
ormjpajpa-2.0jpql

Limit in subquery with JPQL

I'd like to get the average price of my top 100 products via JPA2. The query should look something like this (my sql is a little rusty):

select avg(price) from (
      select p.price from Product p order by p.price desc limit 100)

but that is not working at all. I also tried this:

select avg(p.price) from Product p where p.id = 
       (select pj.id from Product pj order by pj.price desc limit 100)

this is working up until the limit keyword.

I read that limit is not available in JPQL.

Any idea on how to do this? Criteria would also be fine.

Solution

  • LIMIT is not supported by JPQL. Below is the sample-code using Criteria-API.

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Double> criteriaQuery = builder.createQuery(Double.class);
Root<Product> productRoot = criteriaQuery.from(Product.class);
criteriaQuery.select(builder.avg(productRoot.get("price")));
criteriaQuery.orderBy(builder.desc(productRoot.get("price"));
Double average = (Double)entityManager.createQuery(criteriaQuery).setMaxResults(100).getSingleResult();

    or

    Double average = (Double)entityManager.createQuery("select avg(p.price) from Product p order by p.price").setMaxResults(100).getSingleResult();

    If this doesn't work, then you have to go for executing two queries – selecting definitely ordered records & then averaging them.

    Else, go for a native query if portability is not an issue. You can accomplish same using a single query as many RDBMSes support restricting the number of results fetched from a database.