Add orderBy sorting into search Specification

Question

I want to extend this Specification by adding order by params.

Search options DTO:

public class ProductSearchParams {
    private String title;
    private String type;
    private LocalDateTime publishedAt;
    private String sort;
    private List<String> sortBy;
}

public Page<ProductFullDTO> findProducts(ProductSearchParams params, Pageable pageable) {
        Specification<Product> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (params.getTitle() != null) {
                predicates.add(cb.equal(root.get("title"), params.getTitle()));
            }
            if (params.getType() != null) {
                predicates.add(cb.equal(root.get("type"), params.getType()));
            }
            if (params.getPublishedAt() != null) {
                predicates.add(cb.greaterThan(root.get("publishedAt"), params.getPublishedAt()));
            }
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return productService.findAll(spec, pageable).map(productMapper::toFullDTO);
    }

I tried this:

public Page<ProductFullDTO> findProducts(ProductSearchParams params, Pageable pageable) {
        Specification<Product> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (params.getTitle() != null) {
                predicates.add(cb.equal(root.get("title"), params.getTitle()));
            }
            if (params.getType() != null) {
                predicates.add(cb.equal(root.get("type"), params.getType()));
            }
            if (params.getPublishedAt() != null) {
                predicates.add(cb.greaterThan(root.get("publishedAt"), params.getPublishedAt()));
            }
            if (params.getPublishedAt() != null) {
                predicates.add(cb.greaterThan(root.get("publishedAt"), params.getPublishedAt()));
            }
            query.orderBy(builder.desc((root.join(Prodotto_.listaQuoteIng))
                    .get(QuotaIngrediente_.perc_ing)));

            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return productService.findAll(spec, pageable).map(productMapper::toFullDTO);
    }

I can't find a solution how to implement this. Can you give me advice where I'm wrong?

why do you need that? You already have pageable which contains sort. So maybe you are missing something? — bilak, Mar 16 '21 at 20:42

score 1 · Answer 1 · answered Mar 13 '21 at 16:35

1

Are you asking how to use the sortBy list to specify the sort order? The following should work:

query.orderBy(sortyBy.stream()
    .map(root::get)
    .map(cb::desc)
    .toList());

You can also pass the sort order to Pageable via the sort query param (e.g. sort=name,asc&sort=type,desc&sort=listaQuoteIng.perc_ing,desc) and easily adapt it to Criteria API, see this answer.

answered Mar 13 '21 at 16:35

crizzis

9,978
2
28
47

yes, but can you show me how to add you code into the code that I want to extend? – Peter Penzov Mar 13 '21 at 16:39
Not sure I follow, replacing the `query.orderBy(...)` line with the above code should do the trick – crizzis Mar 13 '21 at 16:48

score 1 · Accepted Answer · answered Mar 19 '21 at 17:55

Recreate pageable object before call findAll method:

// sort = "desc" sortBy = ["listaQuoteIng.perc_ing"]
List<Sort.Order> orders = params.getSortBy().stream().map(s -> new Sort.Order("asc".equals(params.getSort()) ? ASC : DESC, s)).collect(Collectors.toList());
pageable = new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), new Sort(orders));

Add orderBy sorting into search Specification

2 Answers2

Linked