Group by result of custom function HIBERNATE

user_9282959 0
Viewed 186

Hi I have some problem with group by a result of a custom function in Hibernate. The query looks like:

 @Query("SELECT NEW ReportDay(" +
            "FUNCTION('atTimezone', p.startDate, :timezone), "
            "SUM(p.energy)) " +
            "FROM Process p GROUP BY FUNCTION('atTimezone', p.startDate, :timezone)")

The error thrown is that p.startDate must appear in groupBy clause or in an aggregate function. But the custom function is in the group by clause.

Any ideas? The custom function is this:

    registerFunction("atTimezone", new SQLFunctionTemplate(StandardBasicTypes.TIMESTAMP,"date_trunc('day', (cast(?1 as timestamp) AT TIME ZONE cast(?2 as varchar)))"));

and is trying to get the date trunc to day in a specific timezone. many thanks!

spring-boot jpa hibernate spring
1 Answers

The problem here is that in SQL the two timezone parameters will be separate parameters and your database (at least PostgreSQL) won't be able to recognize that these are actually the same value.

In order to work around this issue, you can give your function an alias which can be referenced in the GROUP BY clause. Select aliases are not allowed in the GROUP BY clause according to ANSI SQL and JPQL. Some database dialects such as PostgreSQL, MySQL and H2 however do support it and since Hibernate 5.4.10 aliases in the group by clause are propagated to the SQL query for these supporting dialects (HHH-9301).

So instead of:

SELECT FUNCTION('atTimezone', p.startDate, :timezone), SUM(p.energy))
FROM Process p GROUP BY FUNCTION('atTimezone', p.startDate, :timezone)

You could do:

SELECT FUNCTION('atTimezone', p.startDate, :timezone) AS d, SUM(p.energy))
FROM Process p GROUP BY d

How this works together with object projection (NEW ReportDay(...)) is not known to me, but this should at least get you started.

user_21042800
Related