Spring JPA: Join and OrderBy in Specification

Question

I have some problem with Specification construction. I have two entities:
Candidate:

@Entity
@Table(name = "candidates")
public class Candidate extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id", insertable = false, updatable = false)
    @JsonIgnore
    private Category category;

    @Column(name = "category_id")
    private Long categoryId;

    …
}

and Category:

@Entity
@Table(name = "categories")
public class Category extends BaseEntity {

    @OneToMany(mappedBy = "category")
    @JoinColumn(name = "category_id")
    @JsonBackReference(value = "categories-candidates")
    private List<Candidate> candidates = new ArrayList<>();
    …
}

Some Candidate has null in category field. And I need to sort by Category's name field, not by some Candidate's fields. At the same time, I have where-condition by Candidate's field uploadId. I need to create Specification to equivalense of SQL (I checked this request - this is exactly what I need):

SELECT * FROM candidates 
LEFT JOIN categories ON candidates.category_id = categories.id 
WHERE candidates.upload_id = 1 
ORDER BY categories."name"

I tried to do that:

public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
    return ((root, criteriaQuery, criteriaBuilder) -> {
        Join<Candidate, Category> join = root.join("category", JoinType.LEFT);
        criteriaQuery.orderBy(criteriaBuilder.desc(join.get("name")));
        return criteriaBuilder.equal(join.get("uploadId"), uploadId);
    });
}

But I can't:

• There is no "uploadId" field in join, 'cause this is join left join for Category-to-Candidate, not vica versa
• I need all Candidate records, even with null Category, so I can't use inner join
• I can't use JoinType.RIGHT - it doesn't supported and I got the Exception
• I tryed to change relation owner in entities, but it didn't help me
• I shouldn't use @Query things, I need to do it into Specification<>
• If I write return criteriaBuilder.equal(root.get("uploadId"), uploadId); with root instead join, I haven't join results

How I can get this?

Answer 1

You can use Join.getOn() that transforms Join to Predicate.

public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
    return ((root, criteriaQuery, criteriaBuilder) -> {
        Join<Candidate, Category> join = root.join("category", JoinType.LEFT);
        criteriaQuery.orderBy(criteriaBuilder.desc(join.get("name")));
        return criteriaBuilder.equal(root.get("uploadId"), uploadId)
                              // here it is
                              .and(join.getOn());
    });
}