JPA aggregation on aggregated subquery result

Question

I have following JPA entity (getters, setter and non relevant fields omitted):

@Entity
@Table(name = "transaction")
public class Transaction {

@Id
@GeneratedValue
@Column(name = "id")
private Long id;

@Column(name = "start_date", nullable = false)
private Date startDate;

}

My goal is to implement queries using JPQL or criteria API, which will return average amount of transactions per day and maximal amount of transactions per day.

Native SQL queries (MySQL database) giving the desired result look like this:

select max(cnt) from (
select date(start_date) start_date, count(t.id) cnt 
from transaction t 
group by date(t.start_date)
) t;

select avg(cnt) from (
select date(start_date) start_date, count(t.id) cnt 
from transaction t 
group by date(t.start_date)
) t;

Unfortunately usage of native SQL queries is discouraged and JPQL does not allow using subqueries in where clause.

Thank you in advance.

Addition:

I started with following Spring Data query:

@Query("select max(cnt) from ("
+ "select date(t.startDate) startDate, count(t.id) cnt "
+ "from Transaction t "
+ "group by date(t.startDate))")

But it obviously didn't work:

 org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select max(cnt) from (select date(t.startDate) startDate, count(t.id) cnt from Transaction t group by date(startDate))]

I can imagine, that using sorting and limiting the output it's possible to manage search for max, but that will not help for avg.

Could you give us the subquery you have tried to use with JPQL? — Ismail, Oct 11 '19 at 10:57

Konstantin Triger · Accepted Answer · 2019-10-13T15:55:23.780

There are 2 reasons why it's impossible to write what you want in JPQL (or Criteria):

Sub query in FROM, as you point
Date function - MySQL/Hibernate specific.

For this query you may ask portability only (keep native SQL as standard as possible).

One option is to use FluentJPA, which eliminates embedded Strings and tightly integrates with Java and JPA. So the query will looks like this:

public int getAvgCount() {

    FluentQuery query = FluentJPA.SQL(() -> {

        DailyCount daily = subQuery((Transaction t) -> {

            Date date = alias(DATE(t.getStartDate()), DailyCount::getDate);
            int count = alias(COUNT(t.getId()), DailyCount::getCount);

            SELECT(date, count);
            FROM(t);
            GROUP(BY(date));
        });

        SELECT(AVG(daily.getCount())); // or MAX
        FROM(daily);
    });

    return query.createQuery(em, Integer.class).getSingleResult();
}

Type declaration:

@Tuple
@Getter // lombok
public class DailyCount {
    private Integer count;
    private Date date;
}

Resulting SQL:

SELECT AVG(q0.count)
FROM (SELECT DATE(t0.start_date) AS date, COUNT(t0.id) AS count
FROM transaction t0
GROUP BY  DATE(t0.start_date)) q0

JPA aggregation on aggregated subquery result

1 Answers1