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

First thing is first

You need to specify one column as updatable = false & insertable = false, as you are specifying two properties in Candidate entity (category & categoryId). So your Candidate entity will look like following

@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", updatable = false, insertable = false)
    private Long categoryId;

    …
}

Then your specification will look like following:

public class CandidateSpecification {

    // Specification for upload id clause
    public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
        if(uploadId == null) return null;
        return ((root, query, builder) -> {
            return builder.equal(root.get(Candidate_.uploadId), uploadId);
        });
    }
    
    // Specification for left join category.
    public static Specification<Candidate> joinCategory() {
    
        return (root, query, builder) -> {
            root.join(Candidate_.category, JoinType.LEFT);
            return builder.conjuction();
        }
    }
    ...

}

Now the query part

public List<Candidate> getCandidatesByUploadIdSortedByCategoryName(Long uploadId) {
    Specification<Candidate> specification = Specification.where(CandidateSpecification.candidatesByUploadId(uploadId))
    .and(CandidateSpecification.joinCategory);

    return candidateRepository.findAll(specification, Sort.by("category.name"));
}