Laravel select unique products with lowest price from one table

user_13512075 0
Viewed 504

Good day, I'm trying to get unique products with lowest prices. I'm having a products table like this: enter image description here

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.

laravel-query-builder laravel greatest-n-per-group mysql database
1 Answers

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.*']);
user_21889220
Related