CODEWITHSUNDEEP
hibernategroup-byspring-data-jpahibernate-criteria
Jebil
Jebil

Reputation: 1224

Spring JPA Criteria Query group by with to_char function

I am trying to group by day / month / year

Expression<String> expression = criteriaBuilder.function(
                            "to_char", String.class, root.get("reqDate"),
                            criteriaBuilder.parameter(String.class, "dateSegment"));

query.multiselect(
                criteriaBuilder.count(root).alias(FIELD_COUNT), expression.alias("reqDate"))
               .groupBy(expression);
entityManager.createQuery(query)
        .setParameter("dateSegment","MM-YYYY")
        .getResultList();

but this throws an error that the select field must appear in a group by, which already is, I checked the generated sql, it works in db console. but not with JPA, what am i doing it wrong ?

generated sql 

select 
  count(shipment0_.shipment_pk_id) as col_0_0_, 
  to_char(
    shipment0_.requested_delivery_date_time, 
    ?
  ) as col_2_0_ 
from 
  shipment shipment0_ 
group by 
  to_char(
    shipment0_.requested_delivery_date_time, 
    ?
  )

with binding 

2019-10-28 09:20:05,891 TRACE [0.0-8080-exec-6 Wcl5M33fEZTS5iF Wcl5M33fEZTS5iF] - o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [VARCHAR] - [MM-YYYY]
2019-10-28 09:20:05,891 TRACE [0.0-8080-exec-6 Wcl5M33fEZTS5iF Wcl5M33fEZTS5iF] - o.h.type.descriptor.sql.BasicBinder      : binding parameter [10] as [VARCHAR] - [MM-YYYY]

why is it binding as two different params?

log

org.postgresql.util.PSQLException: ERROR: column "shipment0_.requested_delivery_date_time" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 383
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]

Upvotes: 3

Views: 4783

Answers (2)

mhenro
mhenro

Reputation: 61

Your mistake is that you use criteriaBuilder.parameter(String.class, "dateSegment") as DateTime formatter. Instead of it you should use cb.literal("MM-YYYY") and then all should work as expected. So the correct code is the following:

Expression<String> expression = criteriaBuilder.function(
                            "to_char", String.class, root.get("reqDate"),
                            criteriaBuilder.literal("MM-YYYY"));

query.multiselect(
                criteriaBuilder.count(root).alias(FIELD_COUNT), expression.alias("reqDate"))
               .groupBy(expression);
entityManager.createQuery(query)
        .getResultList();

Upvotes: 3

Jebil
Jebil

Reputation: 1224

Managed to achieve this by making query look like

select 
  count(shipment0_.shipment_pk_id) as col_0_0_, 
  to_char(
    shipment0_.requested_delivery_date_time, 
    ?
  ) as col_2_0_ 
from 
  shipment shipment0_ 
group by 
  2

Upvotes: 1

Related Questions