JPA Specifications find children of children

Question

Let's say I have the following classes:

@Entity
@Table(name = "QUESTIONNAIRE")
public final class Questionnaire implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "Questionnaire", sequenceName = "QUESTIONNAIRE_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "Questionnaire")
    private Long id;
    
    @Enumerated(EnumType.STRING)
    private QuestionnaireType type;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "QUESTIONNAIRE_QUESTION", joinColumns = @JoinColumn(name = "QUESTIONNAIRE_ID"), inverseJoinColumns = @JoinColumn(name = "QUESTION_ID"))
    private List<Question> questions = new ArrayList<>();

}

@Entity
@Table(name = "QUESTION")
public final class Question implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "Question", sequenceName = "QUESTION_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "Question")
    private Long id;

    @Column(nullable = false)
    private String value;

    @Column(nullable = false)
    @Enumerated(EnumType.STRING)
    private QuestionType type;

    @OneToMany(mappedBy = "question", cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.LAZY)
    private List<Answer> answers = new ArrayList<>();

}

@Entity
@Table(name = "ANSWER")
public final class Answer implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "Answer", sequenceName = "ANSWER_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "Answer")
    private Long id;

    @Column(nullable = false)
    private String value;

    @NotNull(message = Constantes.ATTR_FIELD_REQUIRED)
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "QUESTION_ID")
    private Question question;
}

public class AnswerFilter implements Serializable {

    private static final long serialVersionUID = 1L;
    
    private QuestionType questionType;
    private QuestionnaireType questionnaireType;
    private String value;
    
}

I've ommited getters and setters and other methods to simplify.

I need to write a query using JPA specifications to find answers with a certain value, that are included in questions of a certain type that are part of a questionnaire of a certain type.

I've written the following:

public static Specification<Answer> filter (final AnswerFilter filter) {
    return (root, query, criteriaBuilder) -> {

        List<Predicate> predicates = new ArrayList<>();
        Join<Answer, Question> questionJoin = root.join(Answer_.question, JoinType.INNER);

        if (filter.getValue() != null) {
            predicates.add(criteriaBuilder.equal(root.get(Answer_.value), filter.getValue()));
        }

        if (filter.getQuestionType() != null) {
            predicates.add(criteriaBuilder.equal(questionJoin.get(Question_.type), filter.getQuestionType()));
        }

        if (filter.getQuestionnaireType() != null) {
            Subquery<Question> questionSubquery = query.subquery(Question.class);
            Root<Question> questionRoot = questionSubquery.from(Question.class);
            Subquery<Questionnaire> questionnaireSubquery = questionSubquery.subquery(Questionnaire.class);
            Root<Questionnaire> rootInd = questionnaireSubquery.from(Questionnaire.class);
            questionSubquery.select(questionRoot);
            questionnaireSubquery.where(criteriaBuilder.equal(rootInd.get(Questionnaire_.type), filter.getQuestionnaireType()), questionJoin.in(Questionnaire_.questions));
            questionnaireSubquery.select(rootInd);
            predicates.add(criteriaBuilder.exists(questionnaireSubquery));
        }

        query.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]))).distinct(true);
        return query.getRestriction();

    };
}

The problematic part is the last "if", I've written several variations of it and I can't get it to work...

Answer 1

Solution: subquery for Questionnaire via criteriaBuilder.isMember

    public static Specification<Answer> filter (final AnswerFilter filter) {
        return (root, query, builder) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (filter.getValue() != null) {
                predicates.add(builder.equal(root.get("value"), filter.getValue()));
            }

            if (filter.getQuestionType() != null) {
                Join<Answer, Question> questionJoin = root.join("question", JoinType.INNER);
                predicates.add(builder.equal(questionJoin.get("type"), filter.getQuestionType()));
            }

            if (filter.getQuestionnaireType() != null) {
                Subquery<Questionnaire> subQuery = query.subquery(Questionnaire.class);
                Root<Questionnaire> subRoot = subQuery.from(Questionnaire.class);
                Expression<Collection<Question>> questions = subRoot.get("questions");

                Predicate questionsRelation = builder.isMember(root.get("question"), questions);
                Predicate questionerFieldPredicate = builder.equal(subRoot.get("type"), filter.getQuestionnaireType());

                subQuery.select(subRoot).where(questionsRelation, questionerFieldPredicate);
                predicates.add(builder.exists(subQuery));
            }

            query.where(builder.and(predicates.toArray(new Predicate[0]))).distinct(true);
            return query.getRestriction();
        };
    }

Generated query:

    select
        distinct answer0_.id as id1_3_,
        answer0_.question_id as question3_3_,
        answer0_.value as value2_3_ 
    from
        answer answer0_ 
    inner join
        question question1_ 
            on answer0_.question_id=question1_.id 
    where
        answer0_.value=? 
        and question1_.type=? 
        and (
            exists (
                select
                    questionna2_.id 
                from
                    questionnaire questionna2_ 
                where
                    (
                        answer0_.question_id in (
                            select
                                questions3_.question_id 
                            from
                                questionnaire_question questions3_ 
                            where
                                questionna2_.id=questions3_.questionnaire_id
                        )
                    ) 
                    and questionna2_.type=?
                )
        )

Also, the similar question you can review at Query on distant/not directly related entity