spring-data-jpacriteriaqueryspecification-pattern

Spring Data Specification orderBy subquery


On my MySql project I got this particular model with 3 entities: Prodotto with many childs QuotaIngrediente, that in turn is Many-to-One child of Ingrediente too. All my relationships are bi-directional. All of them got an autogenerated integer Id and other fields removed to focus on the interesting ones.

@Entity
public class Prodotto {
    private List<QuotaIngrediente> listaQuoteIng = new ArrayList<QuotaIngrediente>();

    @OneToMany(mappedBy = "prodotto", cascade = CascadeType.ALL, orphanRemoval = true)
    public List<QuotaIngrediente> getListaQuoteIng() {
        return listaQuoteIng;
    }
@Entity
public class QuotaIngrediente{

    private Prodotto prodotto;

    private Ingrediente ing;

    private Double perc_ing;

    @ManyToOne
    @JoinColumn(name = "prodotto")
    public Prodotto getProdotto() {
        return prodotto;
    }

    @ManyToOne
    @JoinColumn(name = "ing")
    public Ingrediente getIng() {
        return ing;
    }
@Entity
public class Ingrediente {
    private Set<QuotaIngrediente> quoteIng = new HashSet<QuotaIngrediente>();

    @OneToMany(mappedBy = "ing", cascade = CascadeType.ALL, orphanRemoval = true)
    public Set<QuotaIngrediente> getQuoteIng() {
        return quoteIng;
    }

I'm using SpringData Specification and I can build a query to get Prodotto based on Ingrediente criteria, this way:

public static Specification<Prodotto> getProdottoByIngSpec (String ing) {

    if (ing != null) {
        return (root, query, criteriaBuilder) -> {
            query.groupBy(root.get(Prodotto_.id));

            return criteriaBuilder.like(((root.join(Prodotto_.listaQuoteIng))
                                                .join(QuotaIngrediente_.ing))
                                                .get(Ingrediente_.nome), "%"+ing+"%");
        };

It works as expected, but now I want to sort it by the QuotaIngrediente perc_ing field OF THAT SPECIFIC INGREDIENTE. Obviously I'm asking how to do it on DB, not in business logic.


Solution

  • I was struggling with a false problem due to a wrong assumption of mine. Solution was the simplest. Just sort by orderBy CriteriaQuery method. The query I used to search already filtered the QuotaIngrediente returning just the lines that match my search criteria. Then this is the only line I had to add to my Specification:

    query.orderBy(builder.desc((root.join(Prodotto_.listaQuoteIng))
                                       .get(QuotaIngrediente_.perc_ing)));