Sort child rows of each group in the desired order and take the desired number of top rows in each group in JPA

Question

Is it possible to sort child rows of each group (child rows of each parent row) in the desired order and take the desired number of top rows in each group in JPA?

For example, I have three tables in MySQL database.

• category
• sub_category
• product

The relationship between these tables is intuitive - one-to-many in the order in which they appear.

I'm executing the following criteria query on the sub_category table.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<SubCategory>criteriaQuery=criteriaBuilder.createQuery(SubCategory.class);
EntityType<SubCategory> entityType = entityManager.getMetamodel().entity(SubCategory.class);
Root<SubCategory> root = criteriaQuery.from(entityType);
criteriaQuery.distinct(true);

Join<SubCategory, Category> catJoin = root.join(SubCategory_.catId, JoinType.INNER);
SetJoin<SubCategory, Product> prodJoin = root.join(SubCategory_.productSet, JoinType.INNER);

List<Predicate>predicates=new ArrayList<Predicate>();
predicates.add(criteriaBuilder.isTrue(root.get(SubCategory_.visible)));
predicates.add(criteriaBuilder.isTrue(catJoin.get(Category_.visible)));
predicates.add(criteriaBuilder.isTrue(prodJoin.get(Product_.visible)));

criteriaQuery.where(predicates.toArray(new Predicate[0]));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(SubCategory_.subCatId)));               
List<SubCategory> list = entityManager.createQuery(criteriaQuery).getResultList();

This query happens to fetch all sub categories where visible (TINYINT in MySQL which is mapped to a Boolean property in the corresponding JPA entity) is true in all of these tables.

After executing this query, a set of products of each sub category will be available as a Set<Product>.

I want to fetch a group of top five products (not all products of each sub category) of each sub category after sorting a group of products of each sub category in descending order.

Is it possible?

_{I'm using JPA 2.0 provided by EclipseLink 2.3.2.}

Answer 1

Unfortunately, AFAIK, the only way to limit the results of a query is to use Query#setMaxResults() and this is only applicable to a Query object, not to a subquery.

I suggest to use a workaround, like selecting only the subcategories with your query. Thanks to lazy loading, when returning the results, JPA shouldn't fetch all products for each result (you can check this with some logging), therefore avoiding an unnecessary db load.

Later, when you need the top 5 products for each subcategory, instead of using

SubCategory sc;
List<Product> list = sc.getProducts();

just run a query for each subcategory:

SubCategory sc;
List<Product> list = subCategoryService.getTopProducts(sc);

This shouldn't have a visible worsening of the performances, if the subcategories shown each time are in a reasonable number (like up to 30, as for the results that can be displayed in a paginated data list).