title: JPA Criteria for MAX id GROUP BY FOREIGN KEY in a ManyToMany relationship
body: <p>I'm developing an application using <code>Spring Boot</code> and <code>Spring Data JPA</code>.</p>
  <p>The following are the relevant entities:</p>
  <pre><code>@Entity
  public class Certification {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = &quot;certification_type_id&quot;)
      private CertificationType certificationType;
      @OneToMany(fetch = FetchType.EAGER, mappedBy = &quot;certification&quot;, cascade = CascadeType.ALL)
      private Set&lt;CertificationStatus&gt; certificationStatuses;
  }
  @Entity
  public class CertificationType {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Integer id;
      @Enumerated(EnumType.STRING)
      private Code code;
      private String name;
      private String description;
      ...
      public enum Code {
          ...
      }
  }
  
  @Entity
  public class CertificationStatus {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
      @ManyToOne
      @JoinColumn(name = &quot;certification_id&quot;)
      private Certification certification;
      @ManyToOne
      @JoinColumn(name = &quot;certification_status_type_id&quot;)
      private CertificationStatusType certificationStatusType;
      private String remarks;
  }
  
  @Entity
  public class CertificationStatusType {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Integer id;
      @Enumerated(EnumType.STRING)
      private Code code;
      private String name;
      private String description;
      @OneToMany(fetch = FetchType.EAGER, mappedBy = &quot;certificationStatusType&quot;, cascade = CascadeType.ALL)
      private Set&lt;CertificationStatus&gt; certificationStatuses;
      
      public enum Code {
          ...
      }
  }
  </code></pre>
  <p>In my service, I have a method, <code>CertificationService.findAll()</code>. The idea is to retrieve <code>certification</code> records whose latest <code>certification_status</code> is based on <code>MAX(id)</code>, <code>GROUP BY certification_id</code> and <code>WHERE certification_status_type_id = ?</code>. This method has five (5) parameters:</p>
  <pre><code>Integer certificationTypeId,
  Integer certificationStatusTypeId,
  LocalDate dateFrom,
  LocalDate dateTo,
  String client
  </code></pre>
  <p>The combination could be zero (0) to five (5). Thus, I chose to apply JPA Criteria. So far, I have coded the following:</p>
  <pre><code>@Override
  public List&lt;CertificationDto&gt; findAll(
          Integer certificationTypeId,
          Integer certificationStatusTypeId,
          LocalDate dateFrom,
          LocalDate dateTo,
          String client) {
              
      var certifications = this.certificationRepository.findAll((root, criteriaQuery, criteriaBuilder) -&gt; {
          var predicates = new ArrayList&lt;Predicate&gt;();
          // This is working
          if (certificationTypeId != null &amp;&amp; certificationTypeId &gt; 0) {
              predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.join(Certification_.certificationType, JoinType.INNER), certificationTypeId)));
          }
  
          // To do
          if (certificationStatusTypeId != null &amp;&amp; certificationStatusTypeId &gt; 0) {
              /*
               * I have no idea how to apply my SQL statement:
               * SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
               */
               
               // Not sure what is the direction of the ff codes
              var certificationStatuses = root.join(Certification_.certificationStatuses);
              predicates.add(criteriaBuilder.and(criteriaBuilder.max(certificationStatuses.get(CertificationStatus_.id)))); 
              // It says Cannot resolve method 'and(javax.persistence.criteria.Expression&lt;N&gt;)'
              // Not also sure how to pass in parameter certificationStatusTypeId
          }
  
          // This is working
          if (dateFrom != null) {
              var offsetDateTimeFrom = OffsetDateTime.of(dateFrom, LocalTime.MIDNIGHT, ZoneOffset.ofHours(8));
              var dateTimeFrom = Date.from(offsetDateTimeFrom.toInstant());
              var offsetDateTimeTo = OffsetDateTime.of(Objects.requireNonNullElse(dateTo, dateFrom), LocalTime.MAX, ZoneOffset.ofHours(8));
              var dateTimeTo = Date.from(offsetDateTimeTo.toInstant());
              predicates.add(criteriaBuilder.and(criteriaBuilder.between(
                      root.get(Certification_.createdAt),
                      criteriaBuilder.literal(dateTimeFrom),
                      criteriaBuilder.literal(dateTimeTo)))
              );
          }
  
          // This is working
          if (client != null &amp;&amp; !client.isBlank()) {
              predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.join(Certification_.client).get(Client_.name)), &quot;%&quot; + client.toLowerCase() + &quot;%&quot;)));
          }
  
          return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
      });
      return certifications.stream()
              .map(this.certificationMapper::fromEntityToDto)
              .collect(Collectors.toUnmodifiableList());
  }
  </code></pre>
  <p>My criteria for <code>certificationTypeId</code>, <code>dateFrom</code>, <code>dateTo</code>, and <code>client</code> are working. For the <code>certificationStatusTypeId</code>, I want to create criteria based on the following SQL statement:</p>
  <pre><code>SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
  </code></pre>
  <p>This statement is working in the database. Not sure though if this is the best composition of query to select the record based on <code>MAX(id)</code>, <code>GROUP BY certification_id</code> and <code>WHERE certification_status_type_id = ?</code>.</p>
  <p>Appreciate any help. Thank you.</p>
  <p>**Update</p>
  <p>This is now my code:</p>
  <pre><code>    if (certificationStatusTypeId != null &amp;&amp; certificationStatusTypeId &gt; 0) {
          Subquery&lt;Long&gt; subQuery = criteriaQuery.subquery(Long.class);
          Root&lt;CertificationStatus&gt; subRoot = subQuery.from(CertificationStatus.class);
          subQuery.select(criteriaBuilder.max(subRoot.get(CertificationStatus_.id)))
                  .groupBy(subRoot.get(CertificationStatus_.certification).get(Certification_.id));
          predicates.add(criteriaBuilder.and(criteriaBuilder.in(subRoot.get(CertificationStatus_.id)).value(subQuery)));
          predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subRoot.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
      }
  </code></pre>
  <p>But it throws the following error:</p>
  <pre><code>org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]
  </code></pre>
  <p>Looking at the generated JPQL, it seems it looks similar to my SQL statement. I just don't understand why it's <code>Invalid path</code>.</p>
  <p>**Update
  My apologies! My SQL statement is actually similar to the following:</p>
  <pre><code>select
      // props of Certification
  from certification c
  inner join certification_type ct on ct.id = c.certification_type_id
  inner join client cl on cl.certification_id = c.id
  inner join certification_status cs on cs.certification_id = c.id
  inner join certification_status_type cst on cst.id = cs.certification_status_type_id
  where cs.id in (select max(cs2.id) form certification_status cs2 group by cs2.certification_id) and cs.certification_status_type_id = ?;
  </code></pre>
  <p><strong>Update</strong></p>
  <p>This is solved. Thanks to <code>Thorben Janssen</code> for being patient looking into my issue.</p>
  <p>This is the final portion of my codes:</p>
  <pre><code>    if (certificationStatusTypeId != null &amp;&amp; certificationStatusTypeId &gt; 0) {
          var certificationStatuses = root.join(Certification_.certificationStatuses);
          Subquery&lt;Long&gt; subQuery = criteriaQuery.subquery(Long.class);
          Root&lt;CertificationStatus&gt; subRoot = subQuery.from(CertificationStatus.class);
          subQuery.select(criteriaBuilder.max(subRoot.get(CertificationStatus_.id)))
                  .groupBy(subRoot.get(CertificationStatus_.certification).get(Certification_.id));
          predicates.add(criteriaBuilder.and(certificationStatuses.get(CertificationStatus_.id).in(subQuery)));
          predicates.add(criteriaBuilder.and(criteriaBuilder.equal(certificationStatuses.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
      }
  </code></pre>