Подсчитайте количество строк, отфильтрованных с помощью группы и наличия в JPA

У меня есть две таблицы в базе данных MySQL.

  • товар
  • order_item

Клиенты размещают заказы на продукты, которые хранятся в таблице order_item — отношение «один ко многим» от product до order_item.

В настоящее время я выполняю следующий запрос.

SELECT t0.prod_id, 
       sum(t1.quantity_ordered) 
FROM   projectdb.product t0, 
       projectdb.order_item t1 
WHERE  (t0.prod_id = t1.prod_id) 
GROUP  BY t0.prod_id 
HAVING (sum(t1.quantity_ordered) >= ?) 
ORDER  BY sum(t1.quantity_ordered) DESC

Критерий запроса, который создает этот SQL, выглядит следующим образом.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]>criteriaQuery=criteriaBuilder.createQuery(Object[].class);
Metamodel metamodel = entityManager.getMetamodel();
Root<OrderItem> root = criteriaQuery.from(metamodel.entity(OrderItem.class));

Join<OrderItem, Product> orderItemProdJoin = root.join(OrderItem_.prodId, JoinType.INNER);

List<Expression<?>>expressions=new ArrayList<Expression<?>>();
expressions.add(orderItemProdJoin.get(Product_.prodId));
expressions.add(criteriaBuilder.sum(root.get(OrderItem_.quantityOrdered)));
criteriaQuery.multiselect(expressions.toArray(new Expression[0]));

criteriaQuery.groupBy(orderItemProdJoin.get(Product_.prodId));
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.sum(root.get(OrderItem_.quantityOrdered)), criteriaBuilder.literal(5)));

criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(root.get(OrderItem_.quantityOrdered))));
List<Object[]> list = entityManager.createQuery(criteriaQuery).getResultList();

Этот запрос суммирует количество каждой группы продуктов в таблице order_item.

Он отображает список строк, который выглядит следующим образом.

prod_id       qunatity_ordered

 6            11
 8             8
26             8
 7             7
31             7
12             6
27             6
24             5
 9             5

Можно ли просто подсчитать количество строк, созданных этим запросом - в данном случае 9?

Я использую JPA 2.1, предоставленный EclipseLink 2.5.2 и Hibernate 4.3.6 final.


mysql hibernate jpa eclipselink jpa-2.1
person Tiny    schedule 13.08.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

У вас есть два варианта:

SELECT COUNT(*)
  FROM (
   SELECT 1, 
     FROM projectdb.product t0, 
          projectdb.order_item t1 
    WHERE (t0.prod_id = t1.prod_id) /* I prefer not to use Implicit Joins */
 GROUP BY t0.prod_id 
   HAVING (sum(t1.quantity_ordered) >= ?) 
       ) groups

OR:

list.size();
person Arth    schedule 13.08.2014
comment
Подзапросы в предложении FROM не поддерживаются ORM (до сих пор). - person Tiny; 13.08.2014
comment
Достаточно справедливо .. Это какая-то причина, по которой вы не можете использовать list.size()? - person Arth; 13.08.2014
comment
list.size() требует загрузки всего списка в память, что может стать узким местом в производительности и привести к утечке памяти, если список слишком велик. - person Tiny; 13.08.2014
comment
Это должен быть очень большой список, и если вы просто SELECT 1 (или t0.prod_id) и не включаете ORDER BY, это, по сути, просто массив целых чисел. Если вы все еще беспокоитесь, я уверен, что в вашей системе должен быть способ запуска пользовательского SQL. - person Arth; 13.08.2014

arrow_upward
0
arrow_downward

Один из способов подсчета таких строк состоит в том, чтобы обернуть данный запрос в другой запрос, который подсчитывает строки, и сделать данный запрос подзапросом следующим образом.

SELECT count(DISTINCT(t0.prod_id)) 
FROM   projectdb.product t0 
WHERE  EXISTS (SELECT t1.prod_id 
               FROM   projectdb.order_item t2, 
                      projectdb.product t1 
               WHERE  ((t1.prod_id = t0.prod_id ) 
                        AND ( t1.prod_id = t2.prod_id)) 
               GROUP  BY t1.prod_id 
               HAVING (sum(t2.quantity_ordered) >= ?))

Критериальный запрос, который создает приведенный выше SQL.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Long>criteriaQuery=criteriaBuilder.createQuery(Long.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Product> root = criteriaQuery.from(metamodel.entity(Product.class));
criteriaQuery.select(criteriaBuilder.countDistinct(root));

Subquery<Long> orderItemSubquery = criteriaQuery.subquery(Long.class);
Root<OrderItem> orderItemRoot = orderItemSubquery.from(metamodel.entity(OrderItem.class));
Join<OrderItem, Product> orderItemProdJoin = orderItemRoot.join(OrderItem_.prodId, JoinType.INNER);

orderItemSubquery.select(orderItemProdJoin.get(Product_.prodId));
orderItemSubquery.where(criteriaBuilder.equal(root, orderItemRoot.get(OrderItem_.prodId)));
orderItemSubquery.groupBy(orderItemProdJoin.get(Product_.prodId));
orderItemSubquery.having(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.sum(orderItemRoot.get(OrderItem_.quantityOrdered)), criteriaBuilder.literal(5)));
criteriaQuery.where(criteriaBuilder.exists(orderItemSubquery));

Long count = entityManager.createQuery(criteriaQuery).getSingleResult();
System.out.println("count = "+count);

Обычно я избегаю использования IN() подзапросов и использую EXISTS() подзапросов. Тем не менее тот же запрос можно переписать с использованием IN() следующим образом.

SELECT count(DISTINCT(t0.prod_id)) 
FROM   projectdb.product t0 
WHERE  t0.prod_id IN (SELECT t1.prod_id 
                      FROM   projectdb.order_item t2, 
                             projectdb.product t1 
                      WHERE  (t1.prod_id = t2.prod_id) 
                      GROUP  BY t1.prod_id 
                      HAVING (sum(t2.quantity_ordered) >= ?))

Соответствующий запрос критериев.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Long>criteriaQuery=criteriaBuilder.createQuery(Long.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Product> root = criteriaQuery.from(metamodel.entity(Product.class));
criteriaQuery.select(criteriaBuilder.countDistinct(root));

Subquery<Long> orderItemSubquery = criteriaQuery.subquery(Long.class);
Root<OrderItem> orderItemRoot = orderItemSubquery.from(metamodel.entity(OrderItem.class));
Join<OrderItem, Product> orderItemProdJoin = orderItemRoot.join(OrderItem_.prodId, JoinType.INNER);

orderItemSubquery.select(orderItemProdJoin.get(Product_.prodId));
orderItemSubquery.groupBy(orderItemProdJoin.get(Product_.prodId));
orderItemSubquery.having(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.sum(orderItemRoot.get(OrderItem_.quantityOrdered)), criteriaBuilder.literal(5)));
criteriaQuery.where(criteriaBuilder.in(root.get(Product_.prodId)).value(orderItemSubquery));

Long count = entityManager.createQuery(criteriaQuery).getSingleResult();
System.out.println("count = "+count);

Что касается ограничений в ORM, я не могу найти лучшей альтернативы, чем это.

person Tiny    schedule 13.08.2014