My Laravel Code running slow because foreach, should i change my query to chunk? and how to optimize it?

Question

The concept is simple that i want to sum some value that name total_hs from table analysis with same workingname. This code running so slow because of foreach if has alot of data.

public function totalHSBySameName()
    {

        $result = Analysis::selectRaw('workingname')->get();

        $name = [];
        $total = [];
        
        foreach ($result as $i) {
            if (!in_array($i->workingname, $name)) {
                $name[] = $i->workingname;
            }
        }
        foreach ($name as $i) {
            $temp = 0;
            $x = Analysis::selectRaw('workingname,total_hs')
            ->where('workingname', $i)
            ->get();
            foreach ($x as $j) {
                $temp += $j->total_hs;
            }
            $total[] = ["name" => $i, 'total_hs' => $temp];
        }
        return $total;
    }

and for model like this

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Analysis extends Model
{
    use HasFactory;

    protected $analysis;
    public $table = 'analysis';
    const CREATED_AT = 'created_at';
    const UPDATED_AT = 'updated_at';
    protected $fillable = [
        'workingname',
        'code',
        'koef',
        'total_hs',
        'created_by',
        'updated_by',
    ];
}

Can't you use a groupBy and sum on the querybuilder. I think that should give you right wjat you need strait from the database. See https://laravel.com/docs/8.x/queries#aggregates — Giso Stallenberg, Dec 14 '21 at 11:50
for some reason i need to sum outside database and workingname can be duplicate but it has different value in code column — Robert Yusuf, Dec 14 '21 at 12:06
If you can't perform the `sum` operation on the database for whatever reason, and performing the operation in PHP is taking a while due to the quantity of data involved, consider using a [`queue`](https://laravel.com/docs/8.x/queues). — Peppermintology, Dec 14 '21 at 12:13
You can skip this condition: `if (!in_array($i->workingname, $name)) {` and then use `array_unique($name)` once. It may speed up a little. I would also try to run one query using `IN` and `implode` all `workingname`. But it may need to increase some database settings (`max_allowed_packet` https://stackoverflow.com/questions/4275640/mysql-in-condition-limit), if `$name` has a lot elements. — Robert, Dec 14 '21 at 12:15
``queues`` actually work well, but kindda hard to implements its new for me — Robert Yusuf, Dec 15 '21 at 02:04

score 0 · Answer 1 · answered Dec 14 '21 at 13:42

try this

public function totalHSBySameName()
    {
        $total = Analysis::query()
            ->groupBy('workingname')
            ->selectRaw('sum(total_hs) as sum, workingname')
            ->pluck('sum', 'workingname');
        return $total;
    }

vahid zolfaghari · Accepted Answer · 2021-12-15T05:17:13.793

0

You want to return total value for every workingname, If I understand. You can replace your function with the new one that I mention below.so, It's might be helpful:

public function totalHSBySameName() {
        $result = Analysis::groupBy('workingname')
            ->selectRaw('sum(total_hs) as sum, workingname')
            ->pluck('sum','workingname');

        return $result;
}

edited Dec 15 '21 at 05:17

answered Dec 14 '21 at 13:49

vahid zolfaghari

91
2
7

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 14 '21 at 15:48

My Laravel Code running slow because foreach, should i change my query to chunk? and how to optimize it?

2 Answers2