John Davidson

Message:

I'm working on an inventory database. I have some warehouse and some product. an example database can be:

Please have a look at the following code:

public function scopeOf($query, $warehouse, $product, $date = '')

{

    $date = ($date == '' ? Carbon::today() : $date);

    return $query

        ->where(

            [

                ['warehouse', $warehouse],

                ['product', $product],

            ])

        ->whereDate('updated_at', '<=', $date)

        ->orderByDesc('updated_at')->take(1);

}

with this function I can get leatest data of one product in one warehouse by:
$data = Inventory::Of('w1', 'INJR')->get();

I need to list all latest data of all warehouse.

I read this but it list according to date only. In my case, for each warehouse and product I need to
get newest row.

I'm using SqLite in dev mode with laravel 8.

Update:

Maybe my question is not cleare.
Here is a working function for my question

public function scopeOfList($query, $warehouseName = [], $productName = [], $date = '')

{

    $date = ($date == '' ? Carbon::today() : $date);

    $db = $query->select('product', 'warehouse')->get()->toArray();

    $products = array_unique(

        array_filter(

            array_map(function ($n) use ($productName) {

                return ($productName == [] || in_array($n['product'] ,$productName)? $n['product'] : null );

                },

                $db),

            'strlen')

    );

    $warehouses = array_unique(array_map(function ($n) use ($warehouseName)

    {

        return ($warehouseName == '' || in_array($n['warehouse'] ,$warehouseName)? $n['warehouse'] : null );



    }, $db));

    $data = [];

    foreach ($warehouses as $warehouse){

        foreach ($products as $product){

            $data[] = Inventory::

                where(

                    [

                        ['warehouse', $warehouse],

                        ['product', $product],

                    ])

                ->whereDate('updated_at', '<=', $date)

                ->orderByDesc('updated_at')->get()->first();

        }

    }

    $inventory = array_filter($data, 'strlen');

    sort($inventory);

    return $inventory;

}

Uses:

    //get one/more product from one/more warehouse

    //returns latest data only

    $data = Inventory::OfList(['w1', 'w2'], ['INJR']);

    //returns latest data available before 3 days

    $data = Inventory::OfList(['w1', 'w2'], ['INJR', 'TP'], Carbon::now()->subDays(3));



    //get one product from all warehouse

    //returns latest data only

    $data = Inventory::OfList([], ['INJR']);

    //returns latest data available before 3 days

    $data = Inventory::OfList([], ['INJR'], Carbon::now()->subDays(3));



    //get all product from one warehouse

    //returns latest data only

    $data = Inventory::OfList(['w1']);

    //returns latest data available before 3 days

    $data = Inventory::OfList(['w1'], [], Carbon::now()->subDays(3));



    //get all product from all warehouse

    //returns latest data only

    $data = Inventory::OfList();

    //returns latest data available before 3 days

    $data = Inventory::OfList([], [], Carbon::now()->subDays(3));

I just need to simplify scopeOfList function to make it flexible with large volume of data in inventories table. If i have 500 warehouse 500 products in each warehouse those update every day then for 1 years data my php memory will be exhosted and also it will take considarable time to execute.

---

My Answer:
> where('warehouse_id', $warehouse)
->where('product_id', $product)
->whereDate('created_at', $date)
->latest()
->get();
}

This code defines a scope in Laravel Eloquent that retrieves all items from a specific warehouse and product that were created on a specific date (defaulting to today). The `latest()` method ensures that the items are retrieved in descending order of creation date.

You can use this scope in your controller or model to retrieve the latest items for a specific warehouse and product. For example:

php
$latestItems = Item::of($warehouseId, $productId)->get();


This will retrieve all the latest items for the specified warehouse and product.