Hibernate Formula with join

Question

i have an entity order and an entity order_items ( 1->n relation). I want a field in order that table that show the sum(quantity) of related order items. These are my entities:

    @Entity(name="ORDERS")
public class Order {

    @Id
    @GeneratedValue
    @Column (name="order_id")
    private long id;

    @OneToOne
    @JoinColumn(name="customer_id")
    private Customer customer;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "order", cascade =CascadeType.ALL)
    @JsonManagedReference
    private List<OrderItem> orderItems=new ArrayList();



@Entity(name="ORDER_ITEMS")
public class OrderItem {
    @Id
    @GeneratedValue
    @Column (name="order_item_id")
    private long id;

    @Column (name="quantity")   
    private int quantity;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    @JsonBackReference
    private Order order;

I want a new field in Order entity that show the total quantity ( sum of quantity of the childs). I've tried to add the field in order but it not works

@Formula("select sum(oi.quantity) from ORDER_ITEMS oi where oi.order_id= order_id)")
private int totalQuantity;

Can you help me to fix it ?

Answer 1

The solution

    @Formula("(select sum(oi.quantity) from ORDER_ITEMS  oi where oi.order_id= order_id)")
private int totalQuantity;