Nested Eager Loading with Joins in Laravel

I'm fairly new to Larevel. I have the following Tables and their relationships

products Table:

id    product_name    product_price
1     Product A       2 USD
2     Product B       3 USD

components Table

id      component_name      component_price
1       Component A             5 USD
2       Component B             3 USD

product_component Pivot Table

id  component_id       product_id
1    1                      1
2    1                      2
3   2                       2

orders Table

id    order_date
1     "2015-05-06"

order_items TABLE

id  order_id    component_id    quantity
1   1           1                       1
2   1           2                       2

The Order Model

class Order extends Model {

        public function items()
        {
            return $this->hasMany('OrderItem');
        }

}

The OrderItem Model:

class OrderItem extends Model {

        public function orders()
        {
            return $this->belongsTo('Order');
        }

}

Product Model

class Product extends Model {

        public function components()
        {
            return $this->belongToMany('Component');
        }

}

Component Model

class Component extends Model {

        public function products()
        {
            return $this->hasOne('Product');
        }

}

Product Component Model

class ProductComponent extends Model {

        public function products()
        {
            return $this->belongsTo('Product')->withPivot();
        }

    public function components()
        {
            return $this->belongsTo('Component')->withPivot();
        }
}

View

<h3>Order Id : {{ $order->id }} </h3>
    <h3>Order Date : {{ $order->order_date }} </h3>
@foreach($order->items as $item)
    <tr>
        <td>{{ $item->component_name }}</td>
        <td>{{ $item->component_price }}</td>

    </tr>
@endforeach

My Controller:

public function show($id)
    {

        $order = Order::with(array('items' => function($query) 
            {
                    $query->join('components AS c', 'c.id', '=', 'order_items.component_id');
                }))
                ->find($id);

        return view('orders', compact('order'));
    }

I am only able to produce the following report with the above code

Order No : 1
Order Date : 2015-05-06

Component A    5 USD

Component B    3 USD

However, I need the Order Report in the following format with Product details for every Product Component.

Order No : 1
Order Date : 2015-05-06

Component A   

    - Product A   2 USD  
    - Product B   3 USD
Total       5 X 1 = 5 USD

Component B    3 USD

    - Product B   3 USD
Total       3 X 2 = 6 USD

I think I am in the correct direction, but need guidance to generate the desired report. Many thanks in advance.

saaz

11 years ago

Level 5

Nevermind, I was able to solve this using the following:

class OrderItem extends Model 
{

        public function orders()
        {
            return $this->belongsTo('Order');
        }

        public function products()
        {
            return $this->component->belongsToMany('App\Models\Product\Product');
        }

        public function component()
        {
            return $this->hasOne('App\Models\Product\Component');
        }
}

Controller:

        $order = Order::with(array('items' => function($query) 
                    {
                        $query->with('products')
                        ->join('product_components AS c', 'c.id', '=', 'product_order_items.component_id') ;
                            }))
                        ->find($id);

View:

<h3>Order Id : {{ $order->id }} </h3>

<h3>Order Date : {{ $order->order_date }} </h3>

@foreach($order->items as $item)
    <tr>
        <td>{{ $item->component_name }}</td>
        <td>{{ $item->component_price }}

        @foreach($item->products AS $product)
                <li>{{ $product->name }}</li>
        @endforeach
        </td>
    </tr>
@endforeach

1 like

sukonovs

11 years ago

Level 6

You can nest eager loading like this:

\SomeModel::with('relation.anotherRelation')->get();

Please or to participate in this conversation.