saveMany and query loop - better way to do this?

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

I wonder if this is something you could get done using the CounterCache behavior. https://book.cakephp.org/4/en/orm/behaviors/counter-cache.html

Also, I’m not sure how you feel about anonymous functions. You might feel this makes the code feel more inscrutable or you might like the way the work-horse loop is simpler to read:

        // $theData contains multiple entities.
        $incomes = $this->Incomes->newEntities($theData);
        $this->Incomes->saveMany($incomes);
        //returns a 'conditions' array for search or 'data' array for save
        $col_map = function($income, bool $condition) use ($sumMonthTotal) {
            $comparator = $condition ? ' <>' : '';
            $result = [
                'period_start' => $income->period_start,
                'period_end' => $income->period_end,
                'project_id' => $income->project_id,
                "income_type$comparator" => 3
            ];
            //this value is calculated by the query and save to the new record
            if (!$condition) :
                $result['value'] = $sumMonthTotal;
            endif;
        };

        // great - all my data is saved. However I need to do a calculation
        // for each entity.
        foreach ($incomes as $income) :
            $sumMonthTotal = null;
            // do a query to find the sum of all matching records
            $sumMonthTotal = $this->Incomes->query()
                 ->all()
                 ->select(['total_income' => $sumMonthTotal->func()->sum('Incomes.value')])
                 ->where($col_map($income, true))
                 ->first()
                 ->total_income;
            // insert the new row
            $this->Incomes->save(
                $this->Incomes->newEntity($col_map($income, false))
            );
        endforeach;

That’s great, I appreciate you taking the time to input.