JPA Criteria for MAX id GROUP BY FOREIGN KEY in a ManyToMany relationship

Question

I'm developing an application using Spring Boot and Spring Data JPA.

The following are the relevant entities:

@Entity
public class Certification {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "certification_type_id")
    private CertificationType certificationType;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "certification", cascade = CascadeType.ALL)
    private Set<CertificationStatus> 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 = "certification_id")
    private Certification certification;
    @ManyToOne
    @JoinColumn(name = "certification_status_type_id")
    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 = "certificationStatusType", cascade = CascadeType.ALL)
    private Set<CertificationStatus> certificationStatuses;
    
    public enum Code {
        ...
    }
}

In my service, I have a method, CertificationService.findAll(). The idea is to retrieve certification records whose latest certification_status is based on MAX(id), GROUP BY certification_id and WHERE certification_status_type_id = ?. This method has five (5) parameters:

Integer certificationTypeId,
Integer certificationStatusTypeId,
LocalDate dateFrom,
LocalDate dateTo,
String client

The combination could be zero (0) to five (5). Thus, I chose to apply JPA Criteria. So far, I have coded the following:

@Override
public List<CertificationDto> findAll(
        Integer certificationTypeId,
        Integer certificationStatusTypeId,
        LocalDate dateFrom,
        LocalDate dateTo,
        String client) {
            
    var certifications = this.certificationRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
        var predicates = new ArrayList<Predicate>();
        // This is working
        if (certificationTypeId != null && certificationTypeId > 0) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.join(Certification_.certificationType, JoinType.INNER), certificationTypeId)));
        }

        // To do
        if (certificationStatusTypeId != null && certificationStatusTypeId > 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<N>)'
            // 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 && !client.isBlank()) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.join(Certification_.client).get(Client_.name)), "%" + client.toLowerCase() + "%")));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    });
    return certifications.stream()
            .map(this.certificationMapper::fromEntityToDto)
            .collect(Collectors.toUnmodifiableList());
}

My criteria for certificationTypeId, dateFrom, dateTo, and client are working. For the certificationStatusTypeId, I want to create criteria based on the following SQL statement:

SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;

This statement is working in the database. Not sure though if this is the best composition of query to select the record based on MAX(id), GROUP BY certification_id and WHERE certification_status_type_id = ?.

Appreciate any help. Thank you.

**Update

This is now my code:

    if (certificationStatusTypeId != null && certificationStatusTypeId > 0) {
        Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
        Root<CertificationStatus> 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)));
    }

But it throws the following error:

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 )]

Looking at the generated JPQL, it seems it looks similar to my SQL statement. I just don't understand why it's Invalid path.

**Update My apologies! My SQL statement is actually similar to the following:

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 = ?;

Update

This is solved. Thanks to Thorben Janssen for being patient looking into my issue.

This is the final portion of my codes:

    if (certificationStatusTypeId != null && certificationStatusTypeId > 0) {
        var certificationStatuses = root.join(Certification_.certificationStatuses);
        Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
        Root<CertificationStatus> 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)));
    }

Answer 1

The following code snippet creates a CriteriaQuery that's identical to your SQL statement. Because you're using Spring Data JPA, you can ignore the first block and the last line. Spring Data JPA provides them for you.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CertificationStatus> criteriaQuery = cb.createQuery(CertificationStatus.class);
Root<CertificationStatus> root = criteriaQuery.from(CertificationStatus.class);
criteriaQuery.select(root);

Subquery<Long> sub = criteriaQuery.subquery(Long.class);
Root<CertificationStatus> subRoot = sub.from(CertificationStatus.class);
sub.select(cb.max(subRoot.get("id")));
sub.groupBy(subRoot.get("certification").get("id"));

criteriaQuery.where(root.get("id").in(sub));

em.createQuery(criteriaQuery).getResultList();

You can create a subquery by calling the subquery method on your CriteriaQuery. This returns a Subquery interface which you can use in the same way as the CriteriaQuery interface.

After you defined your Subquery, you can define the IN clause and include it in the WHERE clause of your query. This is the part that always looks a little strange. Instead of using the CriteriaBuilder to define the Expression, you get the entity attribute and call the in method on it with a reference to your Subquery.

In the final step, you use the CriteriaQuery to instantiate a TypedQuery and execute it. After you activated the logging of SQL statements, you can see that Hibernate executes the following SQL statement:

select
    certificat0_.id as id1_4_,
    certificat0_.certification_id as certific3_4_,
    certificat0_.certification_status_type_id as certific4_4_,
    certificat0_.remarks as remarks2_4_ 
from
    CertificationStatus certificat0_ 
where
    certificat0_.id in (
        select
            max(certificat1_.id) 
        from
            CertificationStatus certificat1_ 
        group by
            certificat1_.certification_id
    )

**Update

It looks like you're referencing the wrong table when defining the IN clause. Please try to replace

predicates.add(criteriaBuilder.and(criteriaBuilder.in(subRoot.get(CertificationStatus_.id)).value(subQuery)));

with

    predicates.add(criteriaBuilder.and(certificationStatuses.get(CertificationStatus_.id).in(subQuery)));