Laravel Order By Relationship Sum Column Example

By Hardik Savani | September 14, 2019 | | 5233 Viewer | Category : Laravel


If you are looking for how to laravel order by relation sum then i will help you how to create sum of eloquent relationships model and order by in laravel 6 and laravel 5 application. you can sum relationship column and orderby using subquery in laravel 6 and laravel 5 project.

Laravel provide relationship concept and that's really very useful. If you use relationship in your laravel application then it make very easily stuff everything and you don't have to write long long database query. but some stuff like use sum, count etc function you can not use directly on relation model for order by. If you need order by on relation sum in laravel then i will give you bellow example that will help you.

In this bellow example, i created "customers" and "customer_balances" table and i manage each customer balance. I want to order by most highest balance customer should display on top order. So you can see bellow laravel db query for do this stuff with laravel 6 and laravel 5.

Laravel 6:

$customers = Customer::addSelect(['balance' => CustomerBalance::selectRaw('sum(amount) as total')

->whereColumn('customer_id', 'customers.id')

->groupBy('customer_id')

])

->orderBy('balance', 'DESC')

->get()

->toArray();

dd($customers);

Laravel 5:

$customers = User::select("*",

\DB::raw('(SELECT SUM(amount) FROM customer_balances WHERE customer_balances.customer_id = customers.id) as balance'))

->orderBy('balance', 'DESC')

->get()

->toArray();

dd($customers);

Output:

array:2 [▼

0 => array:7 [▼

"id" => 2

"name" => "Paresh"

"email" => "test@gmail.com"

"email_verified_at" => null

"created_at" => "2019-09-14 03:10:38"

"updated_at" => "2019-09-14 03:10:38"

"balance" => "50"

]

1 => array:7 [▼

"id" => 1

"name" => "Hardik"

"email" => "savanihd@gmail.com"

"email_verified_at" => null

"created_at" => "2019-09-14 03:10:38"

"updated_at" => "2019-09-14 03:10:38"

"balance" => "30"

]

]

I hope it can help you.




Hardik Savani
My name is Hardik Savani. I'm a full-stack developer, entrepreneur and owner of Aatman Infotech. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.
Follow Me: Github Twitter
***Do you want me hire for your Project Work? Then Contact US.

We are Recommending you: