Implement JPA Projection with count

Question

I want to implement JPA Projection with count. I tried this:

@Query(value = "SELECT new org.service.PaymentTransactionsDeclineReasonsDTO( id, count(id) as count, status, error_class, error_message) " +
        " FROM payment_transactions " +
        " WHERE terminal_id = :id AND (created_at > :created_at) " +
        " AND (status != 'approved') " +
        " GROUP BY error_message " +
        " ORDER BY count DESC", nativeQuery = true)
List<PaymentTransactionsDeclineReasonsDTO> transaction_decline_reasons(@Param("id") Integer transaction_unique_id, @Param("created_at") LocalDateTime created_at);

But I get error: Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.plugin.service.PaymentTransactionsDeclineReasonsDTO( id, count(id) as c' at line 1

How I can implement proper count when I have class based Projection?

Answer 1

Try Interface-based Projection instead of DTO:

public interface TransactionDeclineReason {
   Integer getId();
   Long getCount();
   Status getStatus();
   ErrorClass getErrorClass(); // I suppose it's enum...
   String getErrorMessage();
}

@Query(value = "select " +
                 "id as id, " + 
                 "count(id) as count, " + 
                 "status as status, " + 
                 "error_class as errorClass, " + 
                 "error_message as errorMessage " +
               "from " +
                 "payment_transactions " +
               "where " +
                 "terminal_id = ?1 " + 
                 "and created_at > ?2 " +
                 "and status != 'approved' " +
               "group " + 
                 "by error_message " +
               "order by " +
                 "2 desc", nativeQuery = true)
List<TransactionDeclineReason> getTransactionDeclineReasons(Integer transactionId, LocalDateTime createdAt);

Pay attention on aliases (i.e. id as id) - they are mandatory.

Answer 2

You are mixing JPQL and SQL syntax.

The constructor expression (new ...) is JPQL, but in the annotation you mark it as a nativeQuery i.e. as SQL so you have to make up your mind.

If it is to be SQL I don't think you can use aliases in the ORDER BY clause, so you might have to either repeat the expression or wrap it in a subselect as described here: Using an Alias in a WHERE clause.

If it is to be JPQL it doesn't support aliases in a constructor expression, so I guess you have to repeat the expression in the order by clause.