Scenario:
I have some data containing multiple entities. I’d like to save them all at once. This works fine. However I need to do a calculation on this new data:
Each entity has a start and end date, a type and a value. Every time a new “income entity” is added, I need to calculate the total of all incomes that have the same start/end date but is not type 3.
I then need to insert another row with the “total” value and type 3.
Every time a new entity is added I need to recalculate my “total” row and insert that in.
// $theData contains multiple entities.
$incomes = $this->Incomes->newEntities($theData);
$this->Incomes->saveMany($incomes);
// great - all my data is saved. However I need to do a calculation
// for each entity.
foreach ($incomes as $income) :
// do a query to find the sum of all matching records
$sumMonthTotal = $this->Incomes->query();
$sumMonthTotal->select(['total_income' => $sumMonthTotal->func()->sum('Incomes.value')])
->where([
'Incomes.period_start' => $income->period_start,
'Incomes.period_end' => $income->period_end,
'Incomes.project_id' => $income->project_id,
'Incomes.income_type <>' => 3,
]);
// insert the new row
$newIncome = $this->Incomes->newEmptyEntity();
$newIncome->period_start = $income->period_start;
$newIncome->period_end = $income->period_end;
$newIncome->project_id = $income->project_id;
$newIncome->income_type = 3;
$newIncome->value = $sumMonthTotal->all()->first()->total_income;
$this->Incomes->save($newIncome);
endforeach;
This seemingly works, but I can’t help think there’s a better way.
Typically there will be at least 12 entities being saved originally, so I’m doing 24 queries every time.
I appreciate this isn’t a huge query, but it feels clunky.
I guess this is a cause for “afterSave” but I understand this doesn’t work with saveMany and even then I can’t see it reducing the number of queries.
Is my approach logical, and is there a better way to do it?
Thanks