Reputation:
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
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
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
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