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?