Query on distant/not directly related entity

I’m trying to create a query with a where clause on a not directly related table.

My table/entity structure looks like this:

I have got an entity Entity with a ManyToOne relation to Relation. Relation has a ManyToMany relation to DistantRelation.

I have a JpaSpecificationExecutor<Entity> on which I call findAll() with a Specification<Entity>.

How do I setup my entity and/or my specification so I can filter on one of the fields of DistantRelation?

Answer

Entities definition:

@javax.persistence.Entity
@Data
public class Entity {
    @Id
    private Long id;

    @ManyToOne
    private Relation relation;
}

@javax.persistence.Entity
public class Relation {
    @Id
    private Long id;

    @ManyToMany
    private Set<DistantRelation> distantRelation;
}

@Entity
public class DistantRelation {
    @Id
    private Long id;

    private String name;

    @ManyToMany
    private Set<Relation> relation;
}

Solution 1. Subquery with optimal joins

public class EntityDistantRelationSpecification implements Specification<Entity> {

    private String value;

    public EntityDistantRelationSpecification(String value) {
        this.value = value;
    }

    @Override
    public Predicate toPredicate(Root<Entity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        Subquery<DistantRelation> subQuery = query.subquery(DistantRelation.class);
        Root<DistantRelation> subRoot = subQuery.from(DistantRelation.class);
        Expression<Collection<Relation>> relations = subRoot.get("relation");

        Predicate relationPredicate = builder.isMember(root.get("relation"), relations);
        Predicate distantFiledPredicate = builder.equal(subRoot.get("name"), value);

        subQuery.select(subRoot).where(relationPredicate, distantFiledPredicate);
        return builder.exists(subQuery);
    }
}

Generated query:

    select
        entity0_.id as id1_9_,
        entity0_.relation_id as relation2_9_ 
    from
        entity entity0_ 
    where
        exists (
            select
                distantrel1_.id 
            from
                distant_relation distantrel1_ 
            where
                (
                    entity0_.relation_id in (
                        select
                            relation2_.relation_id 
                        from
                            distant_relation_relation relation2_ 
                        where
                            distantrel1_.id=relation2_.distant_relation_id
                    )
                ) 
                and distantrel1_.name=?
            )

Solution 2. Subquery with all relation joins

public class EntityDistantRelationSpecification implements Specification<Entity> {

    private String value;

    public EntityDistantRelationSpecification(String value) {
        this.value = value;
    }

    @Override
    public Predicate toPredicate(Root<Entity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        Subquery<Relation> subQuery = query.subquery(Relation.class);
        Root<Relation> subRoot = subQuery.from(Relation.class);
        Join<Relation, DistantRelation> distantRelationJoin = subRoot.join("distantRelation", JoinType.INNER);

        Predicate relationPredicate = builder.equal(root.get("relation"), subRoot.get("id"));
        Predicate distantFiledPredicate = builder.equal(distantRelationJoin.get("name"), value);

        subQuery.select(subRoot).where(relationPredicate, distantFiledPredicate);
        return builder.exists(subQuery);
    }
}

Generated query:

    select
        entity0_.id as id1_9_,
        entity0_.relation_id as relation2_9_ 
    from
        entity entity0_ 
    where
        exists (
            select
                relation1_.id 
            from
                relation relation1_ 
            inner join
                relation_distant_relation distantrel2_ 
                    on relation1_.id=distantrel2_.relation_id 
            inner join
                distant_relation distantrel3_ 
                    on distantrel2_.distant_relation_id=distantrel3_.id 
            where
                entity0_.relation_id=relation1_.id 
                and distantrel3_.name=?
        )