What is the right approach to deal with JPA period queries?

Question

I need to generate the expected result below. Basically, it's a query to aggregate values according to a specific period (WEEKLY, MONTHLY and etc). There's a date filter, with start and end, and we need to return values for all the range. If they don't exists, should return 0.

In the example below the start date is '2015-08-02' and the and date '2015-08-23' and the period is WEEKLY. Notice that for week 2, we don't have values, but it should be returned with a zero value.

So, in this case, what is the best approach to do this using JPA? We think of using temporary tables and join the results with this table to get results for the entire range, but I don't know if this is possible using JPA, because we need create the table, join and then destroy the temp table.

Another option is to create a database view and map it to an entity.

In the cases above, the JPQL query should be something like that:

@Query("select status, sum(totalInvoice), week from Invoice where " +
        "left join TempTable as tt..." + <-- TEMP TABLE OR VIEW TO GET THE PERIODS
        "issuer.id = :issuerId and type = :type and (:recipientId is null or recipient.id = :recipientId) and " +
        "status in ('ISSUED', 'PAID') " +
        "group by status")

Another option is to use a Stored Procedure, but they seem to be difficult to implement with JPA and I don't think that they are necessary.

Expected result:

{  
   "code":"xxx",
   "title":"This is the title of the first series"
   "type":"PERIODIC",
   "period":"WEEKLY", <-- PERIOD
   "from":"2015-08-02",
   "to":"2015-08-29",
   "labels": ["2015-08-02", "2015-08-09", "2015-08-16", "2015-08-23"],
   "tabelType": "TEXT",
   "series":[  
      {  
         "code":"xxx",
         "title":"This is the title of the first series"
         "values":[10, 0, 13, 18] <- in this example, we don't have values for label "2015-08-09"
      },
      {  
         "code":"xxx",
         "title":"This is the title of the second series"
         "values":[10, 0, 13, 18] <- in this example, we don't have values for label "2015-08-09"
      }
   ]
}

Answer 1

@pozs provided the answer here. This can only be done with a native query (PostgreSQL). Here is the result:

/**
 * Returns the counts, totals and averages of the states by their currency, period and status.
 */
@Query(value = "select i.currency, date(p), i.status, count(id), sum(coalesce(i.total, 0)), avg(coalesce(i.total, 0)) " +
    "from generate_series(date_trunc(:period, cast(:from as timestamp)), date_trunc(:period, cast(:to as timestamp)) + cast('1 ' || :period as interval), cast('1 ' || :period as interval)) p " +
    "inner join invoice i on i.due_date >= p and i.due_date < p + cast('1 ' || :period as interval) " +
    "where issuer_id = :issuerId and type = :type and (:recipientId = 0 or recipient_id = :recipientId) and type = :type " +
    "group by i.currency,  date(p), i.status " +
    "order by i.currency, date(p), i.status", nativeQuery = true)
List<Object[]> getIssuerStatementTotalsByCurrencyPeriodAndStatus(
    @Param("issuerId") long issuerId,
    @Param("recipientId") long recipientId,
    @Param("type") String statementType,
    @Param("from") String from,
    @Param("to") String to,
    @Param("period") String period);

Note that this returns a list of Object arrays. Also note that I was unable to pass in enums and complex parameters into the method. I've had to dumb these values down to Strings and primitives.

I've turned this result into something meaningful with the following class:

  /**
   * Contains the result of a single result in an aggregate query.
   */
  public class AggregateResult {

      private List<String> keys;
      private List<BigDecimal> values;

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2) {
          this(new Object[] { value1, value2 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3) {
          this(new Object[] { value1, value2, value3 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3, Object value4) {
          this(new Object[] { value1, value2, value3, value4 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3, Object value4, Object value5) {
          this(new Object[] { value1, value2, value3, value4, value5 });
      }

      public AggregateResult(Object... vals) {
          values = new ArrayList<>();
          while (values.size() < vals.length && vals[vals.length - values.size() - 1] instanceof Number) {
              Number number = (Number) vals[vals.length - values.size() - 1];
              values.add(number instanceof BigDecimal ? (BigDecimal) number : new BigDecimal(number.toString()));
          }

          this.keys = Stream.of(ArrayUtils.subarray(vals, 0, vals.length - values.size())).map(Object::toString).collect(toList());
      }

      public List<String> getKeys() {
          return keys;
      }

      public List<BigDecimal> getValues() {
          return values;
      }

      /**
       * Returns the list of {@link AggregateResult}s for the raw result. The raw result is expected to
       * have been returned from a native JPA query.
       */
      public static List<AggregateResult> fromNativeResult(List<Object[]> raw) {
          return raw.stream().map(AggregateResult::new).collect(toList());
      }
  }

Answer 2

This might not be the direct answer to your question, but: why do you need to do the grouping within the JPA query directly instead of the Java code? This type of complex semantic grouping is something that is perfectly well done using Java, but SQL databases generally are not very good at producing this type of structured data. If there are no other reasons for doing this on the database level (say, you need a view populated with this data that's searchable based on period), then just load the raw data and populate the structure using Java code - it will have much less coding overhead and will probably be more efficient as well.