How to merge and sum up certain values of items that have the same ID value

So i have 2 tables. Orders and OrderItems. Relationship is each Order can have many OrderItems (orderitem is just a product).

OrderItem Table

id INT PRIMARY KEY
name TEXT
quantity INT
pack_value INT
order_id INT FOREIGN KEY REFERENCES ORDER_ID ON ORDERS TABLE
product_id INT FOREIGN KEY REFERENCES PRODUCT_ID ON PRODUCTS TABLE

Orders Table

order_id INT PRIMARY KEY
status TEXT
user_id INT FOREIGN KEY REFERENCES USER_ID ON USERS TABLE

An order can have the same OrderItem twice or only once. For example like below, 3 items but 2 products:

OrderItem(id=1, product_id=100, name="Pizza", quantity=2, pack_value=10, order_id=7)
OrderItem(id=2, product_id=100, name="Pizza", quantity=10, pack_value=1, order_id=7)
OrderItem(id=3, product_id=555, name="Olives", quantity=5, pack_value=1, order_id=7)

So above there is 2 entries for Pizza and this is the result i get. However i want it to only show 1 entry for Pizza because its the same item. Below is what i want to see:

OrderItem(product_id=100, name="Pizza", quantity=30, pack_value=1, order_id=7)
OrderItem(product_id=555, name="Olives", quantity=5, pack_value=1, order_id=7)

So essentially, if the item only exists once, then do nothing to it. If it exists twice, then make the pack_value=1 and the quantity is the sum of the individual quantity*pack_value. So in example above quantity becomes (2*10 + 10*1 = 30).

The controller method is like below, and so here is where I want to do this:

public function showOrderDetails(Order $order){
    return view('orders.show', compact('order'));
}

Also the Order and OrderItem models has the method for the relationships. For example, in the Order Model i have:

public function orderItems(){
    return $this->hasMany(OrderItem::class, 'order_id');
}

Thanks, and if you need any extra info i can provide.

Answer

SELECT name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_id
FROM order_items
GROUP BY name,
         order_id;

fiddle

Merge another tables to this code using it as subquery, or add them into FROM clause (adjusting its output list and grouping expression) if needed.

Advertisement

Answer