JPA counting its subclass for one to many relationship

Question

I am using JPA for my project and I would like to know that there is any way to count its subclass for one to many relationship. For example, let's say there is an order class that has multiple items and I need to display a list of orders.

@Entity
@Table
public class Order {
    ...
    @Id
    private Long orderId;
    @OneToMany
    private List<OrderItem> orderItems;
}

For the list, I need to display how many items are ordered and how many items are canceled. So, I added functions such that

    @OneToMany
    private List<OrderItem> orderItems;
    ...
    public Long countOrderedItems() {
        Long count = 0;
        orderItems.forEach(orderItem -> {
            if(orderItem.getStatus().equals(Status.ORDERED)){
                count++;
            }
        });
        return count;
    }
    public Long countCancelItems() {
        Long count = 0;
        orderItems.forEach(orderItem -> {
            if(orderItem.getStatus().equals(Status.CANCEL)){
                count++;
            }
        });
        return count;
    }

However, this looks inefficient and I want to get these two values directly when I get data from repository like:

@Query("SELECT o, (SELECT COUNT(oi) FROM OrderItem oi WHERE oi.Order.orderId = o.orderId AND oi.status = 'ORDERED') FROM Order o"); 

But, I know this is not correct JPQL and I would like to know how to get these values efficiently in JPA.

Answer 1

Use JPA 2.1 feature JOIN ON

select o, count(io), count(io2) from Order o 
left join o.irderItem oi on oi.status = 'ORDERED'
left join o.irderItem ui on ui.status = 'CANCELED'
group by s

The join condition used for a join comes from the mapping's join columns. This means that the JPQL user is normally free from having to know how every relationship is joined. In some cases it is desirable to append additional conditions to the join condition, normally in the case of outer joins. This can be done through the ON clause.

Answer 2

It could be done with single join also:

HSQL:

select o, 
sum(case when oi.itemStatus = 'ORDERED' then 1 else 0 end), 
sum(case when oi.itemStatus = 'CANCELED' then 1 else 0 end) 
from OrderItem oi 
right outer join Order o on oi.orderId = o.id
group by o.id

JPA Repository:

@Query("select new example.springboot.jpa.custom.OrderSummary(o, " +
        "sum(case when oi.itemStatus = 1 then 1 else 0 end) as orderedItems, " +
        "sum(case when oi.itemStatus = 1 then 1 else 0 end) as canceledItems)  " +
        "from OrderItem oi right join oi.customOrder o group by o")
List<OrderSummary> findAllOrdersSummary();

You can find full example here: https://github.com/abhilekhsingh041992/spring-boot-samples/blob/master/jpa/src/main/java/example/springboot/jpa/repository/OrderRepository.java