Order by subquery with Spring JPA Specification

Question

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

Answer 1

I have come up with somewhat hacky solution.

There was another problem which was not apparent in the H2 unit test DB, but showed up on the actual Postgres installation: You have to group by a column if it is directly used inside an Order by clause: must appear in the GROUP BY clause or be used in an aggregate function

The solution involves around two different approaches:

• Construct the joins so that there won't be duplicate rows and just do an identity aggregate operation (like sum) on these single rows

Or

• Construct the joins with interested duplicate rows and count the number of child id's. (And don't forget to group by root id).

Counting the number of child id's was the way to go for this problem. But even that (creating a precondition on the interested rows) proved difficult (I am pretty sure it is possible) so I took a somewhat hacky approach: cross join all the children, and do a sum on case:

public static Specification<DesignElement> orderByGoodVotes() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

            ListAttribute<? super DesignElement, Alert> designElementChanges = root.getModel().getList("changes", Change.class);
            ListJoin<Change, Vote> changeVotes = root.join(designElementChanges).joinList("votes", JoinType.LEFT);
            Path<VoteType> voteType = changeVotes.get("type");

            // This could have been avoided with a precondition on Change -> Vote join
            Expression<Integer> goodVotes1_others0 = cb.<Integer>selectCase().when(cb.in(voteType).value(GOOD).value(GREAT, 1).otherwise(0);

            Order order = cb.desc(cb.sum(goodVotes1_others0));
            appendOrder(query, order);

            // This is required
            query.groupBy(root.get("id"));
            return null;
        }
    };
}

I generalized this approach and fixed the previous specifications:

public static Specification<DesignElement> orderByOpen() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Expression<Integer> opens1_others0 = cb.<Integer>selectCase().when(cb.equal(root.get("status").get("open"), Boolean.TRUE), 1).otherwise(0);
            appendOrder(query, cb.desc(cb.sum(opens1_others0)));
            query.groupBy(root.get("id"));
            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.asc(cb.sum(root.get("severity").get("rank"))));
            query.groupBy(root.get("id"));
            return null;
        }
    };
}

public static Specification<DesignElement> orderByCreationTime() {
    return new Specification<DesignElement>() {
        @Override
        public Predicate toPredicate(Root<DesignElement> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            // This isn't changed, we are already selecting all the root attributes
            appendOrder(query, cb.desc(root.get("creationTime")));
            return null;
        }
    };
}

Since there are ~15 other Specifications like these, I guess there will be a performance penalty for being so lazy but I did not analyze it.

Answer 2

I was facing same problem. For sorting by count(child-collection) I found a workaround - it's not ideal (polluting entity objects, not dynamic etc.), but for some cases, it might be enough.

1. define read-only field 'upvotesCount' holding the information to be used for sort

   @Entity
   class Change {
   
       List<DesignElement> designElements;
   
       // let's define read-only field, mapped to db view 
       @Column(table = "change_votes_view", name = "upvotes_count", updatable = false, insertable = false)
       Integer upvotesCount;
   
   }
   

2. implement db view which where the field is mapped to

   CREATE VIEW change_votes_view AS SELECT c.id, count(v.*) as upvotes_count
   FROM change c
   LEFT JOIN votes v 
     ON <ids-condition> AND <votes-are-up=condition>
   

Another way would be to go with DTO objects - you construct query which fits your needs and you can do it also dynamically.