CODEWITHSUNDEEP
phpsqllaravel
Andrea D_
Andrea D_

Reputation: 2141

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

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:

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.

Upvotes: 1

Views: 474

Answers (1)

nbk
nbk

Reputation: 49395

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]);
        }

Upvotes: 1

Related Questions