CakePHP Map/Reduce or alternative?

Question

I have an Invoices Table and a LineItems Table. I want to be able to return the invoice total when I get a list of the invoices on the index action. So I would like to return the Invoice, LineItems and the total of LineItems.price field as a Total.

Current index code in the controller is the default generated.

public function index()
{
    $this->paginate = [
        'contain' => ['Clients', 'Users', 'LineItems']
    ];
    $this->set('invoices', $this->paginate($this->Invoices));
    $this->set('_serialize', ['invoices']);
}

Should I create a map and reduce for this associated item to get the total or is there a better way maybe extending the select to include an additional field. I figured out how to add the select but not how to add it as an additional field. If I overwrite LineItems I just end up with the total and not the other data.

Any pointers on how to do a map reduce in this instance on an associated data or is there a better way?

Wow I cannot believe I did that, I will edit my post. I would like to get the total of the invoice as an additional field. A sum of the price column for all associated LineItems — KaffineAddict, Nov 15 '15 at 20:18
Calculate it *before* and add it to a field in the table then you save the invoice and you won't need to run a reduce on it every time. — floriank, Nov 15 '15 at 21:59
So you are saying if I add or remove or add an item to add or subtract from the total. That would work but it seems like it would be a terribly normalized schema if I was in effect storing that information twice. — KaffineAddict, Nov 15 '15 at 22:01

score 0 · Accepted Answer · answered Nov 15 '15 at 22:36

I figured it out. I need to use leftJoinWith and autoFields to get the desired result. This leaves me with the LineItems and the total for the invoice!

    $invoice = $this->Invoices->find('all', ['contain' =>
        ['Clients', 'Users', 'Payments', 'Payments.PaymentTypes', 'LineItems']]);

    $invoice->select(['total' => $invoice->func()->sum('LineItems.price')])
        ->leftJoinWith('LineItems')
        ->autoFields(true);

CakePHP Map/Reduce or alternative?

1 Answers1