CODEWITHSUNDEEP
mysqllaravelormeloquentaggregate-functions
user2796124
user2796124

Reputation:

Eloquent finding the row with the max value with grouping

Hi I have the problem and solution here http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html which is to find the row with the max of a certain column, grouped by some other value.

My question is how do I convert the query below to Eloquent format?

SELECT s1.article, dealer, s1.price
    FROM shop s1
    JOIN (
      SELECT article, MAX(price) AS price
      FROM shop
      GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;

Thanks.

Upvotes: 2

Views: 3651

Answers (3)

Amin Fazlali
Amin Fazlali

Reputation: 1237

Try this:

Shop::join('shop as shop2', 'shop.article', '=', 'shop2.article')
        ->groupBy('shop.article', 'shop.price')
        ->havingRaw('max(shop2.price) = shop.price')
        ->get()

Upvotes: 0

Sagar Rabadiya
Sagar Rabadiya

Reputation: 4321

using raw Builder you can use below if using eloquent just remove table names and replace with your model instance

DB::table(DB::raw('shop as s1'))
   ->join(
       DB::raw('(SELECT article, MAX(price) AS price FROM shop GROUP BY article) as s2'), 
     function($query) {
       $query->on('s1.article', '=', 's2.article')
             ->on('s1.price', '=', 's2.price');
     })->get();

Upvotes: 2

AddWeb Solution Pvt Ltd
AddWeb Solution Pvt Ltd

Reputation: 21691

You can use DB::query() which is straight forward. You can also get help from Queries - Joins to apply the joins or Raw Expressions.

Upvotes: 0

Related Questions