Home Order by subquery with Spring JPA Specification
Reply: 0

Order by subquery with Spring JPA Specification

user3219
1#
user3219 Published in June 23, 2018, 4:31 am

I have a problem with sorting, which would have been easily solved by native SQL and probably JPQL, but not Spring JPA Specifications.

Here is the trimmed domain model:

@Entity
class DesignElement {
   List<Change> changes;
}
@Entity
class Change {
   List<DesignElement> designElements;
}
@Entity
class Vote {
   Change change;
   VoteType type;
}

Users publish their Change proposals on several elements, and other users vote these changes with VoteType of GREAT, GOOD, NEUTRAL or BAD.

What I try to accomplish is to order Entitys (not Changes) by the count of GOOD and GREAT votes.

It might have been easier with SQL or JPQL but we try to utilize Specification API since we need several other predicates and orders.

After two days of agony I have come up with several assumptions about what I cannot do with Specification api. Some of them are probably wrong, if so I will edit the question with answers.

  • We cannot just add count(Vote_.id) in the root query if we have several Specification<DesignElement>s to be combined.
  • It is not possible to order by subquery in JPA: https://hibernate.atlassian.net/browse/HHH-256
  • It is not possible to do a multiselect in subquery.

Here are several other working Orders defined inside other Specification<DesignElement>s:

private static void appendOrder(CriteriaQuery<?> query, Order order) {
    List<Order> orders = new ArrayList<>(query.getOrderList());
    orders.add(0, order);
    query.orderBy(orders);
}

public static Specification<DesignElement> orderByOpen() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            appendOrder(query, cb.desc(root.get("status").get("open")));
            return null;
        }
    };
}

public static Specification<DesignElement> orderByReverseSeverityRank() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            appendOrder(query, cb.desc(root.get("severity").get("rank")));
            return null;
        }
    };
}

public static Specification<DesignElement> orderByCreationTime() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            appendOrder(query, cb.asc(root.get("creationTime"));
            return null;
        }
    };
}

Which would allow a usage like that:

List<DesignElement> elements = designElementDao.findAll(Specifications.where(orderByReverseSeverityRank()).and(orderByCreationTime());
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.316191 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO