title: Transforming complex postgres query into JPQL for Spring Hibernate repository
body: <p>I have the following Postgres SQL query, in order to get <code>alerts</code> with the latest <code>date</code>:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>This runs well on the postgres database generated by Hibernate. Note the odd id_id construction is because of an embedded key.</p>
  
  <p>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:</p>
  
  <pre><code>@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&lt;Alert&gt; findLatest(@Param("subjectIds") List&lt;UUID&gt; subjectIds,
                         @Param("alertConfigurationIds") List&lt;UUID&gt; alertConfigurationIds,
                         @Param("alertLevels") List&lt;AlertLevel&gt; alertLevels,
                         Pageable pageable);
  </code></pre>
  
  <p>But Hibernate doesn't understand what to do with this to the point where it actually throws a nullpointer while parsing this query.</p>
  
  <pre><code>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.&lt;init&gt;(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)
  </code></pre>
  
  <p>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. </p>