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.
Rate this post
4 of 5 based on 7477 votesComments