2

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.

Tiny
  • 27,221
  • 105
  • 339
  • 599

2 Answers2

2

I'm not familiar with a way to limit a join or a subquery in JPA. If you find some elegant way to do it, please don't forget to post it here.

From what I can think of, you could do something that's less effective :

  1. Remove the Products "Join" - Retrieve only the Categories and Sub categories.
  2. Retrieve the Products of each sub category using a separate query and specify a setMaxResults(5) to every one of your "Products" queries.

Another possibility is using a native query. That would be much more effective but populating your entity beans would be tedious. Here is an example of something you can do to get the top 5 products of each sub category :

select * from category c
inner join sub_category cn on cn.category_id = c.id
inner join product p1 on p1.sub_category_id = cn.id and p1.id in
(select tmp.id from (select p.id, row_number() over (partition by p.sub_category order by p.id) as rn from product p where p.visible = 1) tmp
where tmp.rn < 6);

(Obviously the query needs some tweeking to make it work on)

Ronny Shapiro
  • 411
  • 3
  • 10
  • I could only think of 2 but it incurs a high cost because we need to execute a query to fetch a list of products on each sub category, the query to fetch a list of products goes to an inner loop of sub category. – Tiny Mar 26 '14 at 11:22
  • Right, you would have to query each product list separately. If you want good control of your queries, go with native, but you'd be working with Object arrays and not with your JPA objects. You can always just fetch the entire product list and filter out products except from the top five, but I guess you already taken that possibility into account (if you do this make sure your Objects are detached). – Ronny Shapiro Mar 26 '14 at 12:59
  • Product join is only used to enforce a conditional check in SQL, `WHERE visible=1` in the `product` table. – Tiny Mar 27 '14 at 11:09
  • Whats' your desired result ? I understood you wanteded : A list of Categrories -> holding a list of sub categories -> holding a list of top 5 visible products ? Did I miss anything ? – Ronny Shapiro Mar 27 '14 at 15:03
  • You guessed correctly. Apart from that all categories are already displayed in a menu. Therefore, they are not required to be listed (they should be visible though). Actually, I need to display a sub category-wise list of products in [``](http://www.primefaces.org/showcase/ui/datagrid.jsf), top 5 products of each sub category horizontally in each row of a `` after sorting each group of products in descending order. – Tiny Mar 27 '14 at 15:10
  • I'm not sure I fully understand your workflow, but in case the data just needs to be displayed in a menu, I would consider using a native query as you most likely only want an id and title and populating your object might not be that annoying. I updated the answer above with an example query. – Ronny Shapiro Mar 27 '14 at 15:50
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).

perissf
  • 15,979
  • 14
  • 80
  • 117
  • I'm attempting to run the second snippet (`List list = subCategoryService.getTopProducts(sc);`) [here](http://stackoverflow.com/q/22798513/1391249) that incurs another problem as mentioned there :) – Tiny Apr 02 '14 at 11:54