# saveMany and query loop - better way to do this?

**URL:** <https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612>\
**Category:** Need Help\
**Created:** [April 6, 2020, 7:18pm UTC](https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612 "2020-04-06T19:18:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![met](https://avatars.discourse-cdn.com/v4/letter/m/3e96dc/32.png) [@met](https://discourse.cakephp.org/u/met)\
**Post date:** [April 6, 2020, 7:18pm UTC](https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612/1 "2020-04-06T19:18:29Z")

</div>

**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

---

<div class="post-metadata">

**Author:** ![dreamingmind](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dreamingmind/32/1857_2.png) [@dreamingmind](https://discourse.cakephp.org/u/dreamingmind)\
**Post date:** [April 7, 2020, 2:29am UTC](https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612/2 "2020-04-07T02:29:09Z")

</div>

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](https://book.cakephp.org/4/en/orm/behaviors/counter-cache.html)

---

<div class="post-metadata">

**Author:** ![dreamingmind](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dreamingmind/32/1857_2.png) [@dreamingmind](https://discourse.cakephp.org/u/dreamingmind)\
**Post date:** [April 7, 2020, 5:33am UTC](https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612/3 "2020-04-07T05:33:55Z")

</div>

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:

```auto
        // $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;

```

---

<div class="post-metadata">

**Author:** ![met](https://avatars.discourse-cdn.com/v4/letter/m/3e96dc/32.png) [@met](https://discourse.cakephp.org/u/met)\
**Post date:** [April 7, 2020, 10:53am UTC](https://discourse.cakephp.org/t/savemany-and-query-loop-better-way-to-do-this/7612/4 "2020-04-07T10:53:17Z")

</div>

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