Eloquent group by

Yes, its showing it correctly, the group statistics are being calculated only for MIN(price) as s_price.

1. If you are fetching it only for a specific product id, then order by price and take
2. If you want it for multiple products at one go you will require a self join on

select product_id, min(price) as price from xxx group by product_id

can you please tell me how can i get the data with the self join ?

i have tried this :

DB::table('products AS a')
                            ->join('products AS b', 'a.product_id', '=', 'b.product_id')
                            ->whereIn('a.store_id',$stores)
                            ->where('a.product_id','517')
                            ->whereRaw("a.price = (SELECT MIN(a.price) FROM products)")
                            ->groupBy('a.product_id')
                        ->get()

it only pulls the first row , not the least price one.

http://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value and https://laravel.com/docs/5.2/queries#joins. Give it a try.

this query is freaking me out ; does not work in laravel where as it work in workbench

DB::table('products as a')
            ->whereIn('a.store_id',[24, 28])
            ->where('a.product_id','517')
            ->join(DB::raw('(select product_id, MIN(price) as price FROM products Group By product_id) b'), function($join){
                    $join->on('b.product_id', '=', 'a.product_id');
            })
            ->where('a.price','=','b.price')
            ->get();

the equivalent query (by the above ->toSql()) : is working fine in workbench

select * from `products` as `a` 
inner join (select product_id, MIN(price) as price 
FROM products Group Byproduct_id) b 
on `b`.`product_id` = `a`.`product_id` 
where `a`.`store_id` in (24,28) 
and `a`.`product_id` = 517
and `a`.`price` = 120

If you just need to pass this as json, below works for me. If you are looking for relationship then may be in a different way.

Route::get('/', function() {

    $stores = [24,28];
    $b = DB::table('products as a')
        ->whereIn('a.store_id',[24, 28])
        ->where('a.product_id','517')
        ->join(DB::raw('(select product_id, MIN(price) as price FROM products Group By product_id) b'), function($join){
            $join->on('b.product_id', '=', 'a.product_id');
            $join->on('b.price','=','a.price');
        });

    return $b->get();
});

Result : [{"id":3397,"store_id":28,"product_id":517,"price":120}]

@premsaurav : it will get data if we dont add where('a.price','=','b.price'), if we add this then it will return empty result.

i have solved it by adding raw where : whereRaw('a.price','=','b.price') then it worked :)

Thanks for your time and help :)

Though it will work, its not the correct way. Notice I have added it to the join clause ...

->join(DB::raw('(select product_id, MIN(price) as price FROM products Group By product_id) b'), function($join){
            $join->on('b.product_id', '=', 'a.product_id');
            $join->on('b.price','=','a.price');
        });