CODEWITHSUNDEEP
laraveljoinsql-order-by
Andy Karczewski
Andy Karczewski

Reputation: 55

Laravel: Sorting a column in the Left Join

Lets say I have a simple table of users

id | userName 3 Michael 4 Mike 5 George

And another table of their cars and prices

id | belongsToUser | carPrice 1 4 5000 2 4 6000 3 4 8000

I would like to do a left join that would return the highest or lowest carPrice At the moment, the query would return the last/first instance of that users carPrice. I've tried entering the orderBy in various join queries but to no avail.

I have a helper function that would return the highest/lowest price on demand but I'm not sure how that would fit within this query as I would like to use laravels inbuilt paginate

Upvotes: 1

Views: 111

Answers (1)

Filip Koblański
Filip Koblański

Reputation: 10008

This is the aggregate problem so here is the solvation:

DB::table('users')
->leftJoin('carPrices', 'belongsToUser', '=', 'users.id')
->select('users.*', DB::raw('MAX(carPrice) as highestCarPrice'), DB::raw('MIN(carPrice) as lowestCarPrice'))
->groupBy('users.id')
->get();

Upvotes: 1

Related Questions