[FIXED] How to loop through an array and sum values that belong to the same id with Laravel

Issue

I am a very beginner with php ,laravel and sql (started less than 2 weeks ago) and I cannot figure out how to solve a problem. I have 2 tables:

• cf_invoice_claim_items
• cf_so_invoice_items cf_invoice_claim_items is linked to cf_so_invoice_items through the foreign key so_invoice_item_id while the latter has not direct relation with the former.

Now taking this sample of data from the table cf_invoice_claim_items, what I need to do using Laravel is to write a job that takes and sums the tot_qty from the cf_invoice_claim_items for each equal so_invoice_item_id and save the resulting value into the table cf_so_invoice_items column tot_claimed of the corresponding cf_so_invoice_items id. For example from the screenshot above you can see the invoice_claim_id 31 has two rows for so_invoice_item_id 1 and two rows for so_invoice_item_id 5. The job to write would write in the table cf_so_invoice_items 4 for the id 1 and 22 for the id 5.

Here is the code so far:

class UpdateSoInvoiceItemsTotals implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $claim_id;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($claim_id)
    {
        $this->claim_id = $claim_id;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        try {
            // take all the claim items that belong to the claim id passed
            // CfInvoiceClaimItem is the model for the table cf_invoice_claim_items
            $claimItems = CfInvoiceClaimItem::select(['cf_invoice_claim_items.*'])
                ->where('cf_invoice_claim_items.invoice_claim_id', '=', $this->claim_id)
                ->get();

            // loop through the claim items and for each claim Item linked to the same  so_invoice_item_id sum the tot_qty and save it into the cf_so_invoice_items table with the corresponding id

            foreach($claimItems as $claimItem){
               foreach($claimItem->so_invoice_item_id as so_invoice_item_id) {
                 // code to write
               }
            }
        } catch(\Throwable $e) {
            // settare errore
            // $this->setError($claim->id, "ERR_GENERIC");
        }
    }
}

I am stuck here because I cannot figure out how to loop through the

claimItems->cf_so_invoice_items and sum the tot_qty for those cf_so_invoice_items that have the same id.

Solution

Like the comants said, sum the quantity directly in the query and use thresult to udqate

Please don't use image of data see Why should I not upload images of code/data/errors when asking a question?

    try {
        // take all the claim items that belong to the claim id passed
        // CfInvoiceClaimItem is the model for the table cf_invoice_claim_items
        $claimItems = CfInvoiceClaimItem::select(['cf_invoice_claim_items.*, DB::raw('SUM(tot_qty) AS sum_of_qty')'])
            ->where('cf_invoice_claim_items.invoice_claim_id', '=', $this->claim_id)
            ->get();

        // loop through the claim items and for each claim Item linked to the same  so_invoice_item_id sum the tot_qty and save it into the cf_so_invoice_items table with the corresponding id

        // You get only 1 result drom the select query as long as you don't use `GROUP BY`

        foreach($claimItems as $claimItem){
           $UpdateItems = cf_so_invoice_items::where('so_invoice_id', '=', $this->claim_id)
          ->update(['tot_claimed' => $claimItem->sum_of_qty]);
        }

Answered By - nbk

Answer Checked By - Gilberto Lyons (PHPFixing Admin)