| My final goal is to group entities by a date field with any date interval (such as year, month or hours). This has to be done using Criteria API due to a large amount of surrounding code in the project already utilizing this way of querying for entities. I've already described my case in more detail in the following StackOverflow question: https://stackoverflow.com/questions/61984604/group-by-date-intervals-using-jpas-criteria-api Because as in example of grouping by month, I need groups like "2019-05" and "2020-05" (same month) to be separate, a simple "MONTH" function is not sufficient. Therefore I'm trying to achieve this using "to_char", hoping to get a query that would look like this:
SELECT TO_CHAR(sell_date, 'YYYY-MM') AS alias1 FROM receipts GROUP BY alias1;
My code looks like this:
public void test() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<?> root = query.from(DbReceipt.class);
Expression<?> expr = cb.function("to_char", String.class, root.get("sellDate"), cb.literal("YYYY-MM"));
query.groupBy(expr);
query.multiselect(expr);
TypedQuery<Object[]> typedQuery = em.createQuery(query);
List<Object[]> resultList = typedQuery.getResultList();
}
This code results in the following query and exception:
Hibernate: select to_char(dbreceipt0_.sell_date, ?) as col_0_0_ from receipts dbreceipt0_ group by to_char(dbreceipt0_.sell_date, ?)
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] WARN o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 0, SQLState: 42803
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - ERROR: column "dbreceipt0_.sell_date" must appear in the GROUP BY clause or be used in an aggregate function
which to me seems to happen due to the fact that the whole expression has been put into both "select" and "group by" sections of the query. Is there a way to force alias to be used instead using the "groupBy" method? Is there a way around the issue? |
