Laravel Eloquent: how to sort by sum of values ​​in relation to model?

Question

MySQL

Hello everyone, there are such models:

Vendor (id, title)
Service (id, vendor_id, type, price)

Relationship: Vendor hasMany Service
We need to show all Vendors, sorted by price sum in Services, with a certain type
. For example, we have the following data in the services table:

1  |  1  |  development  |  10
2  |  2  |  development  |  20
3  |  1  |  testing  |  20
4  |  1  |  testing  |  15
5  |  1  |  other  |  15

You need to select all vendors that have testing and development services, and sort them by the sum of the prices of all services of this vendor.
How to do it with Eloquent?

Answer 1

It is possible like this:

$vendors = Vendor::with(['services' => function($query) {
    $query->selectRaw('SUM(price) as sum, vendor_id, type')
    ->groupBy('vendor_id')
    ->groupBy('type')
    ->orderBy('sum', 'desc');
}])
->selectRaw('*, (SELECT SUM(price) FROM service WHERE service.vendor_id = vendor.id) as sum')
->orderBy('sum', 'desc')
->get();

In this case, you can get the following result in view:

@foreach($vendors as $vendor)
    <p><strong>{{ $vendor->title }} - сумма: {{ $vendor->sum }}</strong></p>
    @foreach($vendor->services as $service)
        <p>--- {{$service->type}}: {{ $service->sum }}</p>
    @endforeach
@endforeach

--- development: 2000
--- testing: 1000
--- development: 1800
--- testing: 800
--- development: 1600
-- - testing: 600