Calculated fields in Laravel using Query Builder

Question

I've been working in a Laravel Project and I want to know how can I show a calculated field in a blade view? I want to retrieve some information from an invoice and a Total calculated field.

I would like to get this result, but using Eloquent ORM. The query is this:

SELECT 
    invoiceheader.id, 
    invoiceheader.customerlastname,
    invoiceheader.customerfirstname, 
    invoiceheader.customernit,
    invoicedetail.productid, 
    invoicedetail.quantity,
    invoicedetail.saleprice, 
    (quantity * saleprice) as Total
FROM invoiceheader 
INNER JOIN invoicedetail
    ON invoiceheader.id = invoicedetail.invoiceid

Thank you so much in advance.

Answer 1

You can use laravels DB::raw(), which injects the string to the query, like so:

Laravel raw expressions

InvoiceHeader::select('invoiceheader.id', 'invoiceheader.customerlastname',
   'invoiceheader.customerfirstname', 'invoiceheader.customernit', 
   'invoicedetail.productid', 'invoicedetail.quantity', 
   'invoicedetail.saleprice', DB::raw('(quantity * saleprice) as Total'))
->join('invoicedetail', 'invoiceheader.id', '=', 'invoicedetail.invoiceid')
->get();

Note: make sure to import use DB; on the top of the page

Answer 2

You can do this by utlizing Eloquent relations and accessors.

In your InvoiceHeader model:

/*
Relation with the invoicedetail table
 */
public function detail()
{
    return $this->hasOne(InvoiceDetail::class, 'invoiceid', 'id');
}

In your InvoiceDetail model:

/*
The accessor field
 */
protected $appends = ['total_price'];

/*
Accessor for the total price
 */
public function getTotalPriceAttribute()
{
    return $this->quantity * $this->saleprice;
}

To understand the created accessor name from the method name, here's a text from the laravel docs:

To define an accessor, create a getFooAttribute method on your model where Foo is the "studly" cased name of the column you wish to access. In this example, we'll define an accessor for the first_name attribute. The accessor will automatically be called by Eloquent when attempting to retrieve the value of the first_name attribute:

For your query you could do:

// get all invoices in descending order of creation
$invoices = InvoiceHeader::recent()->get();

// loop invoice data to get the desired fields
foreach ($invoices as $invoice) {
    $customerfirstname = $invoice->customerfirstname;

    $totalprice = $invoice->detail->total_price;

    // more code
}

You can read more about Accessors & Mutators on the official documentation here. Read about eloquent relations here.