Tukukkk

I have the following Postgres SQL query, in order to get alerts with the latest date:

SELECT latest_alerts.subject_id,

       latest_alerts.alertconfiguration_id_id,

       latest_alerts.alert_level,

       latest_alerts.maxdate

FROM   (SELECT subject_id,

               alertconfiguration_id_id,

               alert_level,

               Max(date) AS maxdate

        FROM   alert

        WHERE  subject_id IN ( 'da157532-8de5-4c0c-8608-d924e670d5db', '63b99886-77c8-4784-b8f0-7ff5310f1272' )

               AND alertconfiguration_id_id IN (

                   '6feb6b8b-6b96-4d5d-ac58-713b3cd637a0'

                                               )

        GROUP  BY subject_id,

                  alertconfiguration_id_id,

                  alert_level) AS latest_alerts

       INNER JOIN alert

               ON alert.date = latest_alerts.maxdate

                  AND alert.subject_id = latest_alerts.subject_id

                  AND alert.alertconfiguration_id_id =

                      latest_alerts.alertconfiguration_id_id

                  AND alert.alert_level IN ( 'WARNING' )

ORDER  BY latest_alerts.maxdate DESC;

This runs well on the postgres database generated by Hibernate. Note the odd id_id construction is because of an embedded key.

But I'm struggling to transform this into a JPA/JPQL query that I can use in a Spring Boot application. So far I have this:

@Query("SELECT" +

        " latest_alerts.subject," +

        " latest_alerts.alertConfiguration," +

        " latest_alerts.alertLevel," +

        " latest_alerts.max_date" +

        "FROM" +

        " (SELECT" +

        " subject," +

        " alertConfiguration," +

        " alertLevel," +

        " MAX(date) AS max_date" +

        " FROM" +

        " alert" +

        " WHERE" +

        " subject.id IN (:subjectIds) AND alertConfiguration.id.id IN (:alertConfigurationIds)" +

        " GROUP BY" +

        " subject, alertConfiguration, alertLevel) AS latest_alerts" +

        " INNER JOIN" +

        " alert" +

        " ON" +

        " alert.date = latest_alerts.max_date" +

        " AND alert.subject = latest_alerts.subject" +

        " AND alert.alertConfiguration = latest_alerts.alertConfiguration" +

        " AND alert.alertlevel IN (:alertLevels)" +

        " ORDER BY latest_alerts.date DESC")

Page<Alert> findLatest(@Param("subjectIds") List<UUID> subjectIds,

                       @Param("alertConfigurationIds") List<UUID> alertConfigurationIds,

                       @Param("alertLevels") List<AlertLevel> alertLevels,

                       Pageable pageable);

But Hibernate doesn't understand what to do with this to the point where it actually throws a nullpointer while parsing this query.

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page ournamespace.sense.repository.AlertRepository.findLatest(java.util.List,java.util.List,java.util.List,org.springframework.data.domain.Pageable)!

    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93)

    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63)

    ... many more

Caused by: java.lang.NullPointerException

    at org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4355)

    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:993)

    at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3549)

Any idea if this kind of query is even possible? With a nullpointer thrown by Hibernate it's a bit hard to see what part of the query is the problem.

Cannot do it with Hibernate. From the documentation:

Note that HQL subqueries can occur only in the select or where
clauses.

But you can use native query:

@Query(

  value = "SELECT latest_alerts.subject_id,

       latest_alerts.alertconfiguration_id_id,

       latest_alerts.alert_level,

       latest_alerts.maxdate ...

FROM ...", 

  nativeQuery = true)

Page<Alert> findLatest(@Param("subjectIds") List<UUID> subjectIds,

                       @Param("alertConfigurationIds") List<UUID> alertConfigurationIds,

                       @Param("alertLevels") List<AlertLevel> alertLevels,

                       Pageable pageable);