Use orderBy when chaining multiple Specifications

Question

An existing example does not suit to my scenario, it checks the presence of a parameter to sort by. In my case I would just like to order by a column (created_on) in DESC order.

So, here is the definition of the specification I'd like to call in the very end, after chaining "-5 other ones:


public static Specification<Event> orderByCreationDate() {
        return new Specification<Event>() {
            @Override
            public Predicate toPredicate(Root<Event> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                query.orderBy(criteriaBuilder.desc(root.get("created_on")));
                return null;
            }
        };
    }

The shortened version using lambda looks like that:

    public static Specification<Event> orderByCreationDate() {
        return (root, query, criteriaBuilder) -> {
            query.orderBy(criteriaBuilder.desc(root.get("created_on")));
            return null;
        };
    }

Here is the full chained call of the specifications:

Page<Event> eventsPage = historyLogRepository.findAllPaginated(
                                    allEventsWithEventTypesByVaultId(vaultId)
                                    .and(hasNoEventTypeVisibility()).or(hasEventTypeVisibility(visibilities))
                                    .and(hasNoEventTypes()).or(hasEventTypes(types))
                                    .and(creationDateBefore(before))
                                    .and(creationDateAfter(after))
                                    .and(hasNoDocumentId()).or(hasDocumentId(documentId))
                                    .and(orderByCreationDate()),
                                    pageable);

The idea is to replace this native query:

@Query(value = "SELECT * FROM event e " +
            "INNER JOIN event_type et ON et.id = e.event_type_id " +
            "WHERE e.vault_id = :vaultId AND " +
            "(COALESCE(:visibilities) IS NULL OR et.visibility IN (:visibilities)) AND " +
            "(COALESCE(:types) IS NULL OR et.name in (:types)) AND " +
            "(:before IS NULL OR e.created_on < :before) AND " +
            "(:after IS NULL OR e.created_on > :after) AND " +
            "(:documentId IS NULL OR e.document_id = :documentId) " +
            "ORDER BY e.created_on DESC LIMIT :limit", nativeQuery = true)
    List<Event> findAll(@Param("vaultId") String vaultId,
                        @Param("before") LocalDateTime before,
                        @Param("after") LocalDateTime after,
                        @Param("limit") Integer maxNumberOfResults,
                        @Param("types") List<String> types,
                        @Param("visibilities") List<String> visibilities,
                        @Param("documentId") String documented);

with the new one using Specifications:

 Page<Event> findAllPaginated(Specification<Event> specification, Pageable pageable);
...

Any idea? What should I return from the toPredicatemethod in this case?

You shouldn't sort using `Specification`. You should call `orderBy` from code that create them. Can you show code that use `orderByCreationDate` — talex, Oct 04 '22 at 12:45
Thats not really a predicate now is it? Assuming you are using Spring Data JPA (judging from the tag and specification) you are also using the `JpaSpecificationExecutor`. What you should do is use the `findAll(Specification, Pageable)` method and pass the sort/ordering through the `Pageable` instead of a specification. — M. Deinum, Oct 04 '22 at 12:48

score 1 · Answer 1 · answered Oct 04 '22 at 12:45

null should be fine and usually works. If you want to be explicit, return criteriaBuilder.conjunction() for something that's always true – similarly disjunction() for something that's always false.

Predicate conjunction()

Create a conjunction (with zero conjuncts). A conjunction with zero conjuncts is true.

Predicate disjunction()

Create a disjunction (with zero disjuncts). A disjunction with zero disjuncts is false.

score 0 · Answer 2 · answered Oct 12 '22 at 06:02

As far as I understand, you have made methods for each parameters, now you just need to combine them, I have some similar part of code like below

Explanation: code checks if null then return true else required specification.

public static Specification<Post> byAuthorNames(String[] authorNames) {
        return (root, query, criteriaBuilder) -> {
            if (authorNames == null || authorNames.length == 0) {
                return criteriaBuilder.conjunction();
            }
            return root.<String>get("author").in(authorNames);
        };
    }

Combinining multiple specification:

public Specification<Post> findByFilters(String search, String[] authorNames, String[] tagNames) {
        return byPublished()
                .and(bySearch(search))
                .and(byAuthorNames(authorNames))
                .and(byTagNames(tagNames));
    }

Use orderBy when chaining multiple Specifications

2 Answers2

`Predicate conjunction()`

`Predicate disjunction()`

Linked