CODEWITHSUNDEEP
phplaravel
Martin AJ
Martin AJ

Reputation: 6697

How can I calculate total price of an invoice?

I have two three tables:

// invoices
+----+----------------+
| id |  invoice_code  |
+----+----------------+
| 1  | d09823r230r4   |
| 2  | 34tf354f45tf   |
+----+----------------+

// commodities
+----+-------------+------------+--------+
| id | invoice_id  | product_id | number |
+----+-------------+------------+--------+
| 1  | 1           | 1          | 2      |
| 2  | 1           | 3          | 4      |
+----+-------------+------------+--------+
-- number columns is the number of each ordered product in the invoice

// products
// invoices
+----+-----------+---------+
| id |   name    |  price  |
+----+-----------+---------+
| 1  | SFX-300   | 15000   |
| 2  | GB32-b10  | 2000    |
| 3  | SVX-m30   | 1200    |
+----+-----------+---------+

All I need to do is calculating the total price of an invoice. Here is the formula to calculate the total price for invoice x:

$total_invoice_price = 0;
foreach( $invoice_x->commodities as $commodity){

    $total_invoice_price += ( $commodity.number * <products.price> )
}

echo $total_invoice_price;

The problem is about getting <products.price>. It needs one more join to products table. Any idea how can I do that using Laravel relationships ?

Upvotes: 0

Views: 1715

Answers (3)

Praveen Kumar
Praveen Kumar

Reputation: 927

Please check below Query 

$invoices_total = DB::table('invoices')
        ->join('products', 'invoices.id', '=', 'commodities.invoice_id')
        ->join('commodities', 'products.id', '=', 'commodities.product_id')
        ->sum('products.price * commodities.number')
        ->groupBy('invoices.id')
        ->get();

Upvotes: 1

spicydog
spicydog

Reputation: 1714

To save the query, you should do eager loading using "with()" the model you wish to join.

I'm not sure how you named your model and how well it linked to each other.

Let's assume that it've been done in the conventional way.

Here is the script.

$total_invoice_price = $invoice_x->commodities
                                 ->with('products')
                                 ->get()
                                 ->map(function ($commodity) {
                                     return $commodity->number * $commodity->product->price;
                                 })
                                 ->sum();

What I've done is after getting the products joined with each commodity. I do the get() to have the commodities collection. The from the collection, we do map on each commodity and return the number and price of product. Then we multiply and return as the sum of each commodity record. After that we sum all the totals to the grand total and get your result.

I wrote the code without testing, so try to adjust it to your code.

Upvotes: 2

Philipp
Philipp

Reputation: 15629

If you just need the total price, this could be done in pure sql with aggregate statements and joins over the three tables.

SELECT invoice.invoice_code, SUM(product.price * commodities.number)
FROM invoice
JOIN commodities ON invoice.id = commodities.invoice_id
JOIN product ON product.id = commodities.product_id
GROUP BY invoice.id

Upvotes: 3

Related Questions