JPA Criteria OneToMany of same entity type filter conditions

Question

I have a Project class with a bi-directional OneToMany/ManyToOne defined

public class Project{

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy="subParent", orphanRemoval = true)
    private Set<Project> subProjects = new HashSet<Project>();

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PARENT_PROJECT_ID")
    private Project subParent;

    private String name;

I currently have a bunch of Predicates I use to filter on Project where I ignore any that are subProjects and that works fine (using Specification). However I have a new requirement where I need to get the subParent if the Predicates apply to it or any of the subProjects.

For example if I am looking for a Project who's name contains "test", I need to continue getting Projects where name like "test" as before, but also get the subParent if a subProject contains the name "test".

How can I filter based on the children and main entity of the same type?

Some things I have tried:

    @Override
    public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        //contains main filter to put on Project entity
        Predicate mainPredicate = super.toPredicate(root, cq, cb);

        Subquery<Long> subProjectQuery = cq.subquery(Long.class);

        Root<Project> subProject = subProjectQuery.from(Project.class);
        SetJoin<Project, Project> subProjects = subProject.join(Project_.subProjects);

        //get same filters to put on the subprojects
        Predicate subPredicate = super.toPredicate(subProject, cq, cb);
        Predicate includeSubPred = cb.and(cb.isNotNull(subProject.get(Project_.subParent)));

        subProjectQuery.select(cb.count(subProjects));
        subProjectQuery.where(cb.and(includeSubPred, subPredicate));

        Predicate ignoreSubPred = cb.and(cb.notEqual(root.get(Project_.projectStatus), ProjectStateEnum.SUB_PROJECT));

        return cb.or(cb.and(mainPredicate, ignoreSubPred), cb.greaterThan(subProjectQuery,0L));
    }

    @Override
    public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        //contains main filter to put on Project entity
        Predicate mainPredicate = super.toPredicate(root, cq, cb);
        Predicate ignoreSubPred = cb.and(cb.notEqual(root.get(Project_.projectStatus), ProjectStateEnum.SUB_PROJECT));

        Subquery<Project> sq = cq.subquery(Project.class);
        Root<Project> subRoot = sq.from(Project.class);
        //get same filters to put on the subprojects
        Predicate subPredicate = super.toPredicate(subRoot, cq, cb);
        Predicate includeSubPred = cb.and(cb.isNotNull(subRoot.get(Project_.subParent)));

        sq.select(subRoot.get(Project_.subParent))
        .where(cb.and(cb.equal(root, subRoot.get(Project_.subParent)), includeSubPred, subPredicate));

        return cb.or(cb.and(mainPredicate, ignoreSubPred), cb.in(root).value(sq));
    }

    @Override
    public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        //contains main filter to put on Project entity
        Predicate mainPredicate = super.toPredicate(root, cq, cb);

        Subquery<Project> subProjectQuery = cq.subquery(Project.class);
        Root<Project> subProject = subProjectQuery.from(Project.class);
        //get same filters to put on the subprojects
        Predicate subPredicate = super.toPredicate(subProject, cq, cb);
        Predicate includeSubPred = cb.and(cb.isNotNull(subProject.get(Project_.subParent)));

        subProjectQuery.select(subProject.get(Project_.subParent));
        subProjectQuery.where(cb.and(includeSubPred, subPredicate));

        Join<Project, Project> projectSubprojectJoin = root.join(Project_.subProjects, JoinType.LEFT);

        Predicate ignoreSubPred = cb.and(cb.notEqual(root.get(Project_.projectStatus), ProjectStateEnum.SUB_PROJECT));


        return cb.or(cb.and(mainPredicate, ignoreSubPred), projectSubprojectJoin.in(subProjectQuery));
    }

Any suggestions or help would be greatly appreciated.

********* UPDATE1 *********

So I was able to get the following to work as I needed:

    @Override
    public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        //predicates from filters for main project
        Predicate mainPredicate = super.toPredicate(root, cq, cb);
        //exclude subprojects predicate
        Predicate ignoreSubPred = cb.and(cb.notEqual(root.get(Project_.projectStatus), ProjectStateEnum.SUB_PROJECT));

        // Subquery to get subprojects
        Subquery<Project> sq = cq.subquery(Project.class);
        Root<Project> subRoot = sq.from(Project.class);
        // Get same filters for the sub projects that are applied ot main project (same entity type)
        Predicate subPredicate = super.toPredicate(subRoot, cq, cb);
        // Include Subprojects
        Predicate includeSubPred = cb.and(cb.isNotNull(subRoot.get(Project_.subParent)));
        // Subquery selects parent for subprojects where conditions met
        sq.select(subRoot.get(Project_.subParent))
        .where(cb.and(cb.equal(root, subRoot.get(Project_.subParent)), includeSubPred, subPredicate));

        // return main project meets criteria or main project is in sub query of subprojects where condition met
        return cb.or(cb.and(mainPredicate, ignoreSubPred), cb.in(root).value(sq));
    }

However it seems to be incredibly slow compared to the original functionality. Can anyone see where I may be able to optimize what it is trying to accomplish?

********* UPDATE2 *********

Turns out my solution in update1 was fine. After adding an index to the subParent in the database everything was fast, will leave the first update as the solution in case anyone else runs into similar issue.

Answer 1

The answer ended up being update1 in original post

@Override
    public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        //predicates from filters for main project
        Predicate mainPredicate = super.toPredicate(root, cq, cb);
        //exclude subprojects predicate
        Predicate ignoreSubPred = cb.and(cb.notEqual(root.get(Project_.projectStatus), ProjectStateEnum.SUB_PROJECT));

        // Subquery to get subprojects
        Subquery<Project> sq = cq.subquery(Project.class);
        Root<Project> subRoot = sq.from(Project.class);
        // Get same filters for the sub projects that are applied ot main project (same entity type)
        Predicate subPredicate = super.toPredicate(subRoot, cq, cb);
        // Include Subprojects
        Predicate includeSubPred = cb.and(cb.isNotNull(subRoot.get(Project_.subParent)));
        // Subquery selects parent for subprojects where conditions met
        sq.select(subRoot.get(Project_.subParent))
        .where(cb.and(cb.equal(root, subRoot.get(Project_.subParent)), includeSubPred, subPredicate));

        // return main project meets criteria or main project is in sub query of subprojects where condition met
        return cb.or(cb.and(mainPredicate, ignoreSubPred), cb.in(root).value(sq));
    }