Laravel Order By Relationship Sum Column Example

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.