CODEWITHSUNDEEP
phplaravel
krlosfgx
krlosfgx

Reputation: 41

Calculated fields in Laravel using Query Builder

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.

Upvotes: 4

Views: 8944

Answers (3)

SUB0DH
SUB0DH

Reputation: 5240

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.

Upvotes: 2

oseintow
oseintow

Reputation: 7411

Try this

InvoiceHeader::join("invoicedetail",function($query){
    $query->on("invoiceheader.id", "=", "invoicedetail.invoiceid")
})
->select("invoiceheader.id", 
         "invoiceheader.customerlastname",
         "invoiceheader.customerfirstname", 
         "invoiceheader.customernit",
         "invoicedetail.productid", 
         "invoicedetail.quantity",
         "invoicedetail.saleprice", 
\DB::raw("(invoicedetail.quantity * invoicedetail.saleprice) as Total"))
->get();

Upvotes: 0

Carlos
Carlos

Reputation: 1331

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

Upvotes: 4

Related Questions