java - Spring data jpa specification based on the postgres query

Question

I want to create a spring jpa specification that would behave like this query:

SELECT * FROM files f WHERE f.status = :status AND f.file_id IN (SELECT initiations.file_id FROM initiations WHERE iban = :iban)

The first part I managed to create as that:

public Slice<File> findAll(FileRequest fileRequest) {
    return fileRepository.findAll(getSpecification(fileRequest), fileRequest.getPageable());
}


private Specification<File> getSpecification(FileRequest fileRequest) {
    return Specification.where(isOfStatus(fileRequest.getStatus()));
}


private Specification<File> isOfStatus(String status) {
    return (Specification<File>) (root, criteriaQuery, criteriaBuilder) -> {
        javax.persistence.criteria.Predicate statusEqual = criteriaBuilder.equal(root.get("status"), status);
        return criteriaBuilder.and(statusEqual);
    };
}

Now I'm stuck with the second part, I'm not sure how to create the part WHERE IN especially that it is a different SELECT from a different repository/table. So how should I make the Specification so it could create a list of values from initiationsRepository and then search for a match in it, as it is done in the query I've provided.

question from:https://stackoverflow.com/questions/66045666/spring-data-jpa-specification-based-on-the-postgres-query

Answer 1

Waitting for answers