Howto make this relation work in MYSQL or Laravel

Question

I'm no amazing with MYSQL at all, however laravel framework has made it very easy for me to get what i need as long as i know the basics.

So this question will first aim towards the MYSQL people, because i would rather figure out how to achieve this in plain MYSQL, and then convert it into laravel queries after.

I have 4 tables that need to be used in this query, as seen below:

1. products

    id | name | description | price

2. customers

    id | fname | lname | email | telephone

3. orders

    id | customer_id | total_price | status

4. order_items

    id | order_id | product_id | quantity | price

So i am creating an orders page on my web application. On this page it will show a list of all orders, including the order_items to that particular order. It will also include which customer this order belongs to.

I have managed to achieve the above using laravel, and i get the array seen below:

Array
(
[0] => Array
    (
        [id] => 9
        [customer_id] => 16
        [total_price] => 103.96
        [status] => new
        [created_at] => 2016-02-24 03:06:41
        [customer] => Array
            (
                [id] => 16
                [fname] => firstname
                [lname] => lastname
                [email] => firstname.lastname@gmail.com
                [telephone] => 07707707707
                [address_line_1] => Warstone Rd, walsall
                [address_line_2] => Wolverhampton 
                [postcode] => WV10 7LX
            )

        [order_item] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [order_id] => 9
                        [product_id] => 44
                        [quantity] => 1
                        [price] => 50.00
                    )

                [1] => Array
                    (
                        [id] => 2
                        [order_id] => 9
                        [product_id] => 46
                        [quantity] => 2
                        [price] => 31.98
                    )

                [2] => Array
                    (
                        [id] => 3
                        [order_id] => 9
                        [product_id] => 48
                        [quantity] => 1
                        [price] => 7.99
                    )

                [3] => Array
                    (
                        [id] => 4
                        [order_id] => 9
                        [product_id] => 51
                        [quantity] => 1
                        [price] => 13.99
                    )

            )

    )

)

Now the part i am having trouble with is getting the products that relate to the order_items.

So far it has worked for me because i have been doing thinking like this

$order = Order::find($id)->with('customer','orderItem')->get()->toArray();

This works easy because an order has a customer_id field and an order_items has an order_id. But for me to get the products i need to join products to order_items.

If any one is good at MYSQL and can provide me a query to study and then convert into laravel that would be great.

If anyone knows laravel 5, well this is all the laravel stuff below:

Order.php


public function customer(){
    return $this->belongsTo('App\Customer');
}

public function orderItem(){
    return $this->hasMany('App\OrderItem');
}


OrderItem.php

public function order(){
    $this->belongsTo('App\Order');
}

public function product(){
    $this->hasOne('App\Product');
}


Product.php

public function orderitem(){
    $this->hasOne('App\OrderItem');
}


Customer.php

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

As you can see above are my modal relationships i have set up.

This is my controller where i try to get the full array.

public function show($id)
{
    $order = Order::find($id)->with('customer','orderItem','product')->get()->toArray();

    echo "<pre>";
    print_r($order);
    echo "</pre>";
}

The error i receive is:

 call to undefined method Illuminate\Database\Query\Builder::product()

If i remove the ->with('customer','orderItem','product') and change it to ->with('customer','orderItem') i get the array posted above.

Does anyone know how i can achieve this please?

Answer 1

You are on the right part the only mistake you are doing is you are calling product on the order model which has no direct relation with it. You have to call the product model through the orderitem model like this

   Order::find($id)->with('customer','orderItem.product')->get()->toArray()