Laravel slow collection filtering

I just found a way that is fast, but maybe not the most elegant. Curious if there is a better way (although this could work):

$productIds = $this->repository->getProductIds(); // 20,000 results
$productInventory = $this->repository->getInventoryForProducts($productIds->toArray()); // 60,000 results

$productsInventoryData = [];

$productsInventory->each(function(ProductInventory $productInventory) use (&$productsInventoryData) {
  $productsInventoryData[$productInventory->product_id][] = $productInventory
});

$productIds->each(function (int $productId) use ($productInventoryData) {
  $productInventoryForProduct = $productInventoryData[$productId];
  // Processing of $productInventoryForProduct
}

Here is the entire context... after I did the preprocessing before the chunk each, it is now very fast, but running out of memory :/:

<?php

namespace App\Actions;

use App\Helpers;
use App\Models\InventoryMovement;
use App\Models\InventorySnapshot;
use App\Repositories\InventoryMovementRepository;
use App\Repositories\InventorySnapshotRepository;
use Illuminate\Support\Facades\DB;

class TakeInventorySnapshot
{
    /**
     * @var array
     */
    protected array $productIds;

    protected InventoryMovementRepository $inventoryMovementRepository;
    protected InventorySnapshotRepository $inventorySnapshotRepository;

    /**
     *
     * @param array $productIds
     */
    public function __construct(array $productIds = [])
    {
        $this->inventoryMovementRepository = app(InventoryMovementRepository::class);
        $this->inventorySnapshotRepository = app(InventorySnapshotRepository::class);
        $this->productIds = $productIds;
    }

    public function handle()
    {
        $this->inventorySnapshotRepository->seed();

        foreach ($this->inventorySnapshotRepository->getProductIdsWithInvalidCache($this->productIds)->chunk(10000) as $chunk)
        {
            DB::transaction(function () use ($chunk) {
                /*
                 * The following datasets get preloaded from the database 10,000 products at a time so that we don't
                 * overwhelm the database with reads for each product
                 */
                $validSnapshots = $this->inventorySnapshotRepository->getValidInventorySnapshots($chunk->toArray());
                $validSnapshotsData = [];
                $validSnapshots->each(function (InventorySnapshot $inventorySnapshot) use (&$validSnapshotsData) {
                    $validSnapshotsData[$inventorySnapshot->product_id][] = $inventorySnapshot;
                });
                unset($validSnapshots);

                $invalidSnapshots = $this->inventorySnapshotRepository->getInvalidProductSnapshots($chunk->toArray());
                $invalidSnapshotsData = [];
                $invalidSnapshots->each(function(InventorySnapshot $inventorySnapshot) use(&$invalidSnapshotsData) {
                    $invalidSnapshotsData[$inventorySnapshot->product_id][] = $inventorySnapshot;
                });

                $startDate = Helpers::utcStartOfLocalDate($invalidSnapshots->min('date'));
                $endDate = Helpers::utcEndOfLocalDate($invalidSnapshots->max('date'));
                unset($invalidSnapshots);

                //Return inventory movements starting at start date and ending at end date
                $inventoryMovements = $this->inventoryMovementRepository->getInventoryMovementsDailySummaryWithinRange($startDate, $endDate, $chunk->toArray());

                $inventoryMovementsData = [];
                $inventoryMovements->each(function (InventoryMovement $inventoryMovement) use (&$inventoryMovementsData) {
                    $inventoryMovementsData[$inventoryMovement->product_id][] = $inventoryMovement;
                });
                unset($inventoryMovements);

                $data = [];
                $chunk->each(function (int $productId) use (&$bulkData, $validSnapshotsData, $inventoryMovementsData, &$data, $invalidSnapshotsData) {
                    $invalidSnapshotsForProduct = collect($invalidSnapshotsData[$productId]);
                    $startDate = Helpers::utcStartOfLocalDate($invalidSnapshotsForProduct->min('date'));
                    $endDate = Helpers::utcEndOfLocalDate($invalidSnapshotsForProduct->max('date'));

                    $inventoryMovementsForProduct = collect($inventoryMovementsData[$productId])
                        ->whereBetween('date', [$startDate, $endDate]);

                    $productSnapshot = isset($validSnapshotsData[$productId]) ? collect($validSnapshotsData[$productId])
                        ->where('date', '<=', $startDate->clone()->subDay())
                        ->sortBy('date', SORT_DESC)
                        ->last() : null;

                    $tally = [
                        'inventory_available' => $productSnapshot?->inventory_available ?? 0,
                        'inventory_reserved' => $productSnapshot?->inventory_reserved ?? 0,
                        'inventory_in_transit' => $productSnapshot?->inventory_in_transit ?? 0,
                        'inventory_stock_value' => $productSnapshot?->inventory_stock_value ?? 0,
                    ];

                    $inventoryMovementsForProduct->each(function (InventoryMovement $inventoryMovement) use (&$data, $productSnapshot, $productId, &$tally)
                    {
                        switch ($inventoryMovement->inventory_status)
                        {
                            case InventoryMovement::INVENTORY_STATUS_ACTIVE:
                                $tally['inventory_available'] += $inventoryMovement->total_quantity;
                                break;
                            case InventoryMovement::INVENTORY_STATUS_RESERVED:
                                $tally['inventory_reserved'] += $inventoryMovement->total_quantity;
                                break;
                            case InventoryMovement::INVENTORY_STATUS_IN_TRANSIT:
                                $tally['inventory_in_transit'] += $inventoryMovement->total_quantity;
                                break;
                        }

                        $data[$inventoryMovement->date][$productId]['inventory_available'] = $tally['inventory_available'];
                        $data[$inventoryMovement->date][$productId]['inventory_reserved'] = $tally['inventory_reserved'];
                        $data[$inventoryMovement->date][$productId]['inventory_in_transit'] = $tally['inventory_in_transit'];


                        if (!isset($data[$inventoryMovement->date][$productId]['inventory_stock_value']))
                        {
                            $data[$inventoryMovement->date][$productId]['inventory_stock_value'] = $tally['inventory_stock_value'];
                        }

                        $data[$inventoryMovement->date][$productId]['inventory_stock_value'] += $inventoryMovement->inventory_value;
                    });
                });

                foreach ($data as $date => $products)
                {
                    foreach ($products as $product_id => $inventory_data)
                    {
                        $bulkData[] = [
                            'is_cache_valid' => 1,
                            'date' => $date,
                            'product_id' => $product_id,
                            'inventory_available' => $inventory_data['inventory_available'] ?? 0,
                            'inventory_reserved' => $inventory_data['inventory_reserved'] ?? 0,
                            'inventory_in_transit' => $inventory_data['inventory_in_transit'] ?? 0,
                            'inventory_stock_value' => $inventory_data['inventory_stock_value'],
                        ];
                    }
                }

                if (empty($bulkData))
                {
                    return;
                }

                $this->inventorySnapshotRepository->saveBulk($bulkData);
            });
        }
    }
}

I don't know if this is what you're looking for, but earlier I was reading an article from James Bannister about using generator for pagination, in his example he uses generators and LazyCollection, hope it can help.

I got it to run successfully by unsetting variables to clear memory throughout the script and upping the memory_limit.

The scale of the number of records would only be high on the first run of this script. Subsequent runs will only process newly invalidated cache records. So for the scale of data, the whole thing executes in 3.5 minutes and 1.43 GB of memory which I don't think is bad considering everything.

Subsequent run after initial ran in just 2 seconds!

Note I was able to improve things even further by using mysql CASE statements to return less "inventoryMovement" records.

public function getInventoryMovementsDailySummaryWithinRange(Carbon $startDate, Carbon $endDate, array $productIds): EloquentCollection
    {
        $inventoryMovements = InventoryMovement::query()
            ->select([
                'inventory_movements.product_id',
            ])
            ->selectRaw("
            CONVERT_TZ(
                DATE_FORMAT(
                    CONVERT_TZ(
                        inventory_movement_date, 'UTC', '" . Helpers::setting(Setting::KEY_DEFAULT_TIMEZONE) . "'
                    ), '%Y-%m-%d'
                ), '" . Helpers::setting(Setting::KEY_DEFAULT_TIMEZONE) . "', 'UTC'
            ) as date")
            ->selectRaw('SUM(CASE WHEN inventory_status = "' . InventoryMovement::INVENTORY_STATUS_ACTIVE . '" THEN quantity ELSE 0 END) as inventory_available')
            ->selectRaw('SUM(CASE WHEN inventory_status = "' . InventoryMovement::INVENTORY_STATUS_RESERVED . '" THEN quantity ELSE 0 END) as inventory_reserved')
            ->selectRaw('SUM(CASE WHEN inventory_status = "' . InventoryMovement::INVENTORY_STATUS_IN_TRANSIT . '" THEN quantity ELSE 0 END) as inventory_in_transit')
            ->selectRaw('IFNULL(SUM(quantity * (fifo_layers.total_cost / fifo_layers.original_quantity)), 0) as inventory_stock_value')
            ->leftJoin('fifo_layers', function(JoinClause $join) {
                $join->on('inventory_movements.layer_id', 'fifo_layers.id')
                    ->where('layer_type', FifoLayer::class);
            })
            ->whereBetween('inventory_movement_date', [$startDate, $endDate])
            ->groupBy([
                DB::raw("DATE_FORMAT(CONVERT_TZ(inventory_movement_date, 'UTC', '" . Helpers::setting(Setting::KEY_DEFAULT_TIMEZONE) . "'), '%Y-%m-%d')"),
                'inventory_movements.product_id',
            ]);

        if (!empty($productIds))
        {
            $inventoryMovements->whereIn('inventory_movements.product_id', $productIds);
        }

        return $inventoryMovements->get();
    }

The next challenge of optimization is that I'm still using a loop in memory to build the tally:

$inventoryMovementsForProduct->each(function (InventoryMovement $inventoryMovement) use (&$data, $productSnapshot, $productId, &$tally)
                {
                    $tally['inventory_available'] += $inventoryMovement->inventory_available;
                    $tally['inventory_reserved'] += $inventoryMovement->inventory_reserved;
                    $tally['inventory_in_transit'] += $inventoryMovement->inventory_in_transit;
                    $tally['inventory_stock_value'] += $inventoryMovement->inventory_stock_value;

                    $data[] = [
                        'date' => $inventoryMovement->date,
                        'product_id' => $inventoryMovement->product_id,
                        'is_cache_valid' => 1,
                        'inventory_available' => $tally['inventory_available'],
                        'inventory_reserved' => $tally['inventory_reserved'],
                        'inventory_in_transit' => $tally['inventory_in_transit'],
                        'inventory_stock_value' => $tally['inventory_stock_value'],
                    ];
                });

I've been reading up on the use of SUM over in MySQL with Common Table Expressions: https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql

I could see how that could work in a simpler case but the complication in my context is that we do not want to load ALL inventory movements to tally, but rather we want to utilize any prior valid snapshots as a starting point to make things faster, which is what I'm trying to do here:

$productSnapshot = isset($validSnapshots[$productId]) ? collect($validSnapshots[$productId])
                    ->where('date', '<=', $startDate->clone()->subDay())
                    ->sortBy('date', SORT_DESC)
                    ->last() : null;

If I can figure out how to do more of this in MySQL it would save even more memory and time.

Update here: https://laracasts.com/discuss/channels/eloquent/mysql-window-functions-for-cumulative-totals-avoiding-php-foreach-loops-and-using-summing-groups-within-window-functions