Using Orderby before GroupBy in Laravel

Question

I have a table that I want to select product_name with lowest/min price the following:

product_name | price 
Cat          |   12
Dog          |   21
Cat          |   14
Dog          |   20
Fish         |   10
Fish         |    3

THE DESIRE Output should be

Cat  | 12
Dog  | 20
Fish | 3

BELOW IS MY SQL QUERY

$products = DB::table('products')
            ->orderBy('products.products_price', 'asc')
            ->groupBy('products.products_name')
            ->get();

When I used this script, it only shows the highest/max price, not the lowest price

I have not tested this but should simply changing the direction of the sort not achieve what you want? `orderBy('products.products_price', 'desc')`? — j4g0, Jul 30 '20 at 14:19

aynber · Accepted Answer · 2020-07-30T16:35:31.223

1

You need an aggregate instead of ordering. For Laravel, that means passing in the columns along with a DB::raw:

$products = DB::table('products')
        ->orderBy('products.products_price', 'asc')
        ->groupBy('products.products_name')
        ->select(['product_name', DB::raw('min(price) as price')])
        ->get();

Edit for ID

Taking off the answer here : SQL: Group by minimum value in one field while selecting distinct rows the mysql query would be

SELECT p1.*     
FROM products p1 INNER JOIN
    (
        SELECT product_name, MIN(price) AS as min_price
        FROM products
        GROUP BY product_name
    ) p2 ON p1.product_name = p2.product_name AND p1.price = p2.min_price

Now we have to convert it to Query Builder

$products = DB::table('products AS p1')
   ->join(DB::raw('(
    SELECT product_name, MIN(price) AS as min_price
    FROM products
    GROUP BY product_name
) AS p2'), 
    function($join)
    {
       $join->on('p1.product_name', '=', 'p2.product_name');
       $join->on('p1.price', '=', 'p2.min_price');
    })
    ->get(['p1.id', 'p1.product_name', 'p1.price']);

This has not been tested, so I hope it works

edited Jul 30 '20 at 16:35

answered Jul 30 '20 at 14:18

aynber

22,380
8
50
63

Thanks but this does not bring out the correct product "ID". ie if Cat is ID 1 and 3. The Cat with lowest/min is ID 1. hence the output should be Product ID =1 and Product_name = Cat and Price = 12. The SQL query you advice gave correct product_name but the product ID is wrong. i.e it use Product ID 3 with MIN price as against Product ID 1 with min – Lamp Soft Jul 30 '20 at 15:11
Ah, you didn't mention the product ID. That adds a new wrinkle – aynber Jul 30 '20 at 15:17
So sorry. It was when I closely look at the output I discovered. Please help, I've been on this for over 24hrs – Lamp Soft Jul 30 '20 at 15:36
I've updated my answer. I hope it helps. Also of note is that Laravel now has [subquery joins](https://laravel.com/docs/7.x/queries#joins) which might help as well – aynber Jul 30 '20 at 16:38

Aless55 · Answer 2 · 2020-07-30T16:42:15.000

0

Problem:

You are not defining that the price attribute should be aggregated to a minimum.

Solution:

If you want the max price you need to select the MIN() aggregation. You can do this by using ->selectRaw('MIN(price) as max_price').

Please Note:

If you want other attributes also selected simply add them comma separated.

->selectRaw('name, MAX(price) as max_price')

@edit

Do you still use the oderBy? If not, try it with orderBy('products.products_price', 'ASC')

edited Jul 30 '20 at 16:42

answered Jul 30 '20 at 14:18

Aless55

2,652
2
15
26

Thanks but this does not bring out the correct product "ID". ie if Cat is ID 1 and 3. The Cat with lowest/min is ID 1. hence the output should be Product ID =1 and Product_name = Cat and Price = 12. The SQL query you advice gave correct product_name but the product ID is wrong. i.e it use Product ID 3 with MIN price as against Product ID 1 with min. – Lamp Soft Jul 30 '20 at 15:37
ye the id is not correct, but that was not asked in your question – Aless55 Jul 30 '20 at 16:27
@LampSoft look at my edit do you still use the orderBy? – Aless55 Jul 30 '20 at 16:42

Using Orderby before GroupBy in Laravel

2 Answers2