Spring Data Specification orderBy subquery

Question

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.

What does it mean to sort by `b.a` when b is a set? What does happen if you use a `Sort` with `listaQuoteIng.perc_ing`? Sorry, if I'm missing something essential, but it is hard to follow the code when all the names mean absolutely nothing for me. — Jens Schauder, Mar 05 '20 at 16:31
@JensSchauder Every `Prodotto` (product) has a list of items (`QuotaIngrediente`). Every item is composed by two fields: a `foreign key` that specifies one of the ingredient (`Ingrediente`) belonging to the product, and an Integer measuring that ingredient content in percentage for the specific product (`perc_ing`). I want to sort a list of products basing on their content of a specific ingredient. If I simply constraint my sort by `listaQuoteIng.perc_ing`, it will take in account the percentage of EVERY ingredient in all the products, and not just the one I want sorting on. — 4javier, Mar 05 '20 at 20:35
@JensSchauder I was losing my mind on a non-problem. I thought that my query was returning ALL the ingredients of any product that matched my constraint. Instead it actually returns only the matching ingredients, then as you were correctly pointing out I had just to ORDER BY listaQuoteIng.perc_ing. I accomplished that by adding this clause to my Specification ```query.orderBy(builder.desc((root.join(Prodotto_.listaQuoteIng)).get(QuotaIngrediente_.perc_ing)));``` If you got any improvement, write it in a separate answer and I'll mark it as solution. — 4javier, Mar 06 '20 at 18:35
Good to hear. Please, post the answer as a proper answer, or close/delete the question, so people no it has been dealt with. — Jens Schauder, Mar 07 '20 at 10:32

score 0 · Accepted Answer · answered Mar 07 '20 at 11:16

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)));

Spring Data Specification orderBy subquery

1 Answers1