php - How to find "similar" orders based on SKU number

So, I am banging my head for a few days now... I have to somehow find "similar" order based on seed order items SKU and group them in batches with 20 slots * 40kg each slot so in total 800kg max.

I have two tables orders and order_items. Orders table contains the ID and the shop id and some timestamps. The Order Items table contains the order_id, sku, quantity, and volume/weight.

I am Using Laravel 8.*, PHP 7.4 and Postgres

What i tried already:

Getting The seed order which is the oldest in the orders pool found by particular batch group which looks like that

//The batch group query

if (!empty($batchGroup->group['carriers'])) {
            $this->whereIn('carrier', $batchGroup->group['carriers']);
        }

        if (!empty($batchGroup->group['methods'])) {
            $this->whereIn('method', $batchGroup->group['methods']);
        }

        if (!empty($batchGroup->group['countries'])) {
            foreach ($batchGroup->group['countries'] as $k => $country) {
                if ($k === 0) {
                    $this->where('country', '=', $country);
                } else {
                    $this->where('country', '=', $country, 'or');
                }
            }
        }
Copy code

After that I am getting the SKU's of that order by doing that:

$seedItemsSku = $seedOrder->items->pluck('sku')->toArray();

//Here i have array with SKUS: [102312, 150021, 125125] etc...
Copy code

Here comes the fun part...

I made a function in the query builder which should return all matching orders

public function similar(Order $seedOrder, BatchGroup $batchGroup, array $exceptOrders = []): self
    {
        $skus = $seedOrder->items->pluck('sku')->toArray();

        $query = $this;

        if (!empty($exceptOrders)) {
            $query->whereNotIn('id', $exceptOrders);
        }
        return $this->whereHas('items', function (OrderItemQueryBuilder $builder) use ($skus) {
                $builder->whereIn('sku', $skus);
            })
            ->whereNull('batch_id')
            ->where('total_weight', '<=', 200);
    }
Copy code

The problem with the function above is that it gets result which is with mixed skus not EXACTLY matched like the seed skus. Also it's really slow.

I am looking for a way/advice how can I achieve this result.

Here is scenario: The shop got 5k orders overnight. They should be packed on the next day. The system gets the oldest order takes the skus and start looking for similar orders 100% match of the skus will be perfect. But if we have order with that sku and one more which is not present in the seed order we have match again. The goal is to achieve maximum efficiency of the batch and avoid walking around the big warehouse for 5 orders. Also the items in the warehouse have location by which i will sort the end result of the batch slots.

IMO it is strange logic but this is how the client works and pickers/packers got used to.

UPDATE: This is the query which should find similar orders.

SELECT
    id, country, total_items, total_weight, batch_id
FROM
    "orders"
WHERE
    EXISTS (
        SELECT
            *
        FROM
            "order_items"
        WHERE
            "orders"."id" = "order_items"."order_id"
            AND "sku" in('104026')
            AND "order_items"."deleted_at" IS NULL)
        AND "country" = 'Germany'
        AND "orders"."deleted_at" IS NULL
Copy code

This is the result:

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.