[FIXED] Specification for filter joining multiple tables, including embedded primary key

Issue

I want to build a Specification to filter a property that is not directly included in the entity but in the "second join".

The entities look (much simplified) like this:

@Entity
public class A {
    @Id
    private UUID id;

    @OneToMany(mappedBy=pk.a)
    private List<B> b;
}

@Entity
public class B {
    @EmbeddedId
    private PK pk;
}

@Embeddable
public class PK {
    @ManyToOne
    private A a;
    
    @ManyToOne
    private C c;
}

@Entity
public class C {
    @Id
    private UUID id;

    @OneToMany(mappedBy=pk.b)
    private List<B> b;
}

I want to get all A that have a specific C.id and I already have a repository method by name that is working:

List<A> findAllByBPKCId(String id);

Since I want some more filters I need to move to JpaSpecificationExecutor but I'm failing to build the Specification to do the same than the previous repository method.

What I tried so far is: Calling

List<A> findAll(Specification specification);

with

new Specification() {
    @Override
    public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteraBuilder) {
        return criteriaBuilder.equal(root.join("b").join(pk.c).get("id"), ID_TO_COMPARE);
    }
}

However it won't find "pk.c", I need to somehow "get" pk first before joining c. It throws an IllegalArgumentException because it is 'Unable to locate Attribute with the given name [pk.b]'.

How can I join an entity that is nested in an embedded "composite primary key"?

Solution

You need to first join B and PK, and then join with C, i.e., PK should be consider the same way as the rest of entities when joining.

Please, consider the following code:

new Specification() {
    @Override
    public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteraBuilder) {
        Join<A, B> joinAB = root.join("b");
        Join<B, PK> joinBPK = joinAB.join("pk");
        Join<PK, C> joinPKC = joinBPK.join("c");

        return criteraBuilder.equal(joinPKC.get("id"), cb.literal(ID_TO_COMPARE));
    }
}

This code can be simplified using lambdas:

public static Specification<A> getSpecification(String ID_TO_COMPARE) {
  return (root, query, cb) -> {
    Join<A, B> joinAB = root.join("b");
    Join<B, PK> joinBPK = joinAB.join("pk");
    Join<PK, C> joinPKC = joinBPK.join("c");

    return cb.equal(joinPKC.get("id"), cb.literal(ID_TO_COMPARE));
  };
}

In addition, if you have the opportunity, use criteria metamodel, it provides compilation type field checking when constructing your queries and makes the things clear:

public static Specification<A> getSpecification(String ID_TO_COMPARE) {
  return (root, query, cb) -> {
    Join<A, B> joinAB = root.join(A_.b);
    Join<B, PK> joinBPK = joinAB.join(B_.pk);
    Join<PK, C> joinPKC = joinBPK.join(PK_.c);

    return cb.equal(joinPKC.get(C_.id), cb.literal(ID_TO_COMPARE));
  };
}

Answered By - jccampanero