Calculating Totals In Model

Is the vat the same on all products?

$vat = $this->products->first()->vat;

Hey @sinnbeck thanks for your response. That suggestion does work if i was only using one vat rate. Unfortunately though the vat rate can change depending on the product or service.

In my products table i have

-id
-name
-vat_id
-cost
-sale

I then have a jobs table and a pivot between job & products via job_product

In my VAT table i have

-id
-calc (0, 1.05 or 1.2)
-rate (0%, 5% or 20%)
-description

Thank you in advance.

So something like

$total = $this->products->sum(function($product) {
            return $product->sale * $product->tax->calc;
        });

@sinnbeck you are an absolute legend thank you. Much appreciated. :)

@ollie_123 Just a note: if you’re dealing with anything financial (invoices, orders, etc) be sure to calculate any taxes, totals, etc at the point of creation and save them as values in the row itself.

The reason: if you update any calculation logic, amounts on historical invoices and orders shouldn’t change.

Hey @martinbean, thank you for the suggestion. Thats a really good point. Currently, i have an order table and products table with a many to many relationship and the order_id & product_id's being stored via a pivot table.

Lets say that upon creating an invoice a new row is inserted into an Invoice table, how would i account for multiple products as it could range from 1 - 15+ products. Would it be a case of an invoices table & invoice_products table with a belongsTo on the invoice_products & hasMany on the invoice table?

//invoices return $this->hasMany('App\InvoiceProduct');
-id
-order_id
-payment_ref
-etc....

//invoice_product return $this->belongsTo('App\Invoices');
-id
-invoice_id
-product_name
-product_description
-sale_net
-vat_rate
-sale_inc_vat
-etc

@ollie_123 Your Invoice model would have a has-many relation with an InvoiceLineItem model. The line item model would then store a description of the line item, quantity, unit price, and total for that line. You can have a foreign key pointing towards a product or whatever.

But again, don’t rely on any data from the associated product table (i.e. price) because if you change the price of the product and your invoice uses that to calculate any totals, then those amounts will change, which you don’t want. Capture data at the time you create the invoice. Another scenario is if you completely delete the product—if your invoice is pulling data from the relation then your application is going to throw a 500 error when a customer tries to view an invoice and it’s referencing data on a model that no longer exists.

Thanks @martinbean thats really good advice. I’ll get that implemented ASAP.

I guess that I should really do the same for the order aswell then rather than having a pivot table of order_product because as you say, if a user deletes a product or updates the price it will affect the order.

@ollie_123 Well like I say, you shouldn’t be calculating the amounts on the fly any way. Calculate when the order’s created.

If you have an accessor that calculates a total and you change the price of a product, that’s going to affect old orders where the order was placed when the product was the old price.

Thanks @martinbean. I appreciate the advice. It makes a lot of sense.

Im just trying to implement it in now but having a bit of trouble with an error of Property [name] does not exist on this collection instance when trying to pull the product name, price etc... Which i'm a but confused about as if i do a ddd($products) i can see the attributes in an array.

$order = array(
            'uuid'          => (string) \Str::uuid(),
            'ref'           => request()->ref,
            'assigned_to'   => request()->assigned_to,
            'customer_id'   => $customerId,
);

$newOrder = \App\Order::create($order);

// Pull product id's from the order form
$products = \App\Product::whereIn('id', request()->product_id)->get();

$orderLineItems = array(
            'order_id'      => $newOrder->id,
            'prod_name'     => $products->name, //Property [name] does not exist on this collection instance
            'prod_desc'     => $products->description,
            'prod_cost'     => $products->cost,
            'prod_sale'     => $products->sale,
	     //Plus vat calculations etc...
        );

        \App\OrderLineItems::create($orderLineItems); 

If you could advise where i might be going wrong it would be much appreciated.

You are getting multiple products

$products = \App\Product::whereIn('id', request()->product_id)->get();

And try to get the name of them..

'prod_name'     => $products->name

So either loop over $products if you need to handle them all, or change get() to first() if you just need one item.

Hey @sinnbeck.

Sorry if i'm missing something but what you posted is what i currently have which is giving me the error Property [name] does not exist on this collection instance

Can i not loop over the products in an array like the following?

$products = \App\Product::whereIn('id', request()->product_id)->get();

$orderLineItems = array(
            'order_id'      => $newOrder->id,
            'prod_name'     => $products->name, //Property [name] does not exist on this collection instance
            'prod_desc'     => $products->description,
            'prod_cost'     => $products->cost,
            'prod_sale'     => $products->sale,
	     //Plus vat calculations etc...
        );

        \App\OrderLineItems::create($orderLineItems); 

Again my apologies if i'm missing something obvious.

Thank you in advance.

@ollie_123 variable $products->name doesn't exist, because $products is a Collection of products, not one product.

So you need to do something like

$orderLineItems = [];
foreach ($products as $product) {
    $orderLineItems[] = [
        'order_id' => $newOrder->id,
        'prod_name' => $product->name,
        // ... 
    ];
}

Thanks @povilaskorop much appreciated.

@ollie_123 also, you can't pass an array to create() method. Multiple records can be saved only with \App\OrderLineItems::insert($orderLineItems) but then that insert() method won't automatically save timestamps and has some other drawbacks. So I would suggest just creating records one by one, in a foreach loop.