Automatic join

Hi all,

Imagine I have two models, Invoice and InvoiceLines. Both of them are quite explandatory. (FK invoice_lines.invoice_id = invoice.id). I grab the object using the following:

$invoice = \App\Invoice::get();

Now, Is it possible to, in the model, to extend the response with an custom field called e.g. 'lineTotal' which performs for example:

(SELECT SUM(quantity*price) FROM invoice_lines WHERE invoice_id=$invoice->id)

Bottom line: When I grab an invoice or many invoices, I want to have the invoice_line summorization to it.

What is the best approach? My original thought was to use getAttributeLineTotal - but how would I go ahead and construct that method?

Thanks!

bobbybouwmann

11 years ago

Level 88

I don't know your database structure so this is kinda hard but I think this will help you

You can just use Eloquent to do this

Invoice::select('*', DB::raw('SUM(invoice_lines.quantity * invoice.price) AS lineTotal'))
    ->join('invoice_lines', 'invoice.id', '=', 'invoice_lines.invoice_id')
    ->where('invoice_lines.invoice_id', '=', $invoice->id)
    ->get();

JarekTkaczyk

11 years ago

Best Answer

Level 53

@bobbybouwmann No all() there.

@jeliasson You can use pseudo-relation like here http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ only replace count with your sum(qty*price) part.

1 like

bobbybouwmann

11 years ago

Level 88

@JarekTkaczyk yea of course, updated my answer ;)

jeliasson

11 years ago

Level 2

Sorry for late reply. Apparently my answer did no to thru the first time.

@JarekTkaczyk That was what I was looking for. Thanks!

Please or to participate in this conversation.