Mysql many-to-many relationship problem between two tables

Question

first of all, I apologize if I was wrong with the title. I could not find how to state the problem I was experiencing. I'm working on a Laravel project. I have two tables called orders and order_items.

Mysql Tables:

orders

• order_id
• order_date
• order_customer_id

order_items

• order_item_id
• order_id
• order_item_product_id
• order_piece

The image I want to reach:

I want to show the user the orders in his account and the products belonging to that order in the form of an accordion menu. There may be more than one order on the page. Naturally the same order is repeated multiple times when I try it with leftJoin. I could not understand if this is a problem I need to solve on the mysql side or the php side. Can you give me a guide on this matter?

My Current Code:

$query = Order::query();
$query->where('order_customer_id', $customerID);
$query->leftJoin('order_items', 'orders.order_id','=','order_items.order_id');
$data['orderList'] = $query->get();
return view('pages.orders.index', $data);

Answer 1

From your database, i see its one to many relationship (Not many to many as your title said).

You can just create relation at your model

OrderItem.php

public function order(){
    $this->belongsTo(Order::class);
}

Order.php

public function orderitem(){
    $this->hasMany(orderitem::class);
}

At controller, get the Order

$orders = Order::with('orderitems')->get();
return view('yourview',compact('orders'))

At view

@foreach($orders as $order)
   {{$oder->name}}
   @foreach($order->orderitem as $orderItem)
       {{$orderItem->name}}
   @endforeach
@endforeach