0
366
VistasLaravel select unique products with lowest price from one table
Good day, I'm trying to get unique products with lowest prices.
I'm having a products table like this:
I would like to get a list of products with all columns. Now there are some products that have more than one supplier in that case I want to grab the product with the lowest cost_price.
So far I have tried this
$products = DB::table('products')
->select('identifier')
->selectRaw('MIN(cost_price) as cost_price')
->where('stock', '>', 0)
->groupBy('identifier')
->orderBy('cost_price', 'asc')
->distinct()->get();
This query returns me the correct results but I cant add more columns every time I add a column for example stock in select I need to add as well in GroupBy and then I'm just getting all products.
How to do it? Thank you for reading.
·
Santiago Trujillo
1 Respuestas
Responde la pregunta0
You need greatest-n-per-group solution/approach for this problem.
The query;
SELECT products.*
FROM products
INNER JOIN (SELECT identifier, MIN(cost_price) AS minPrice
FROM products
WHERE stock > 0
GROUP BY identifier) AS sub
ON sub.minPrice = products.cost_price and sub.identifier = products.identifier;
The query builder version;
$sub = DB::table('products')
->where('stock', '>', DB::raw(0))
->groupBy('identifier')
->select('identifier', DB::raw('min(cost_price) as minPrice'));
return DB::table('products')
->join(DB::raw('(' . $sub->toSql() . ') as sub'), function ($join) {
$join->on('sub.minPrice', '=', 'products.cost_price');
$join->on('sub.identifier', '=', 'products.identifier');
})
->get(['products.*']);
·
Santiago Trujillo
Denunciar
Responde la pregunta