Sort Laravel Collection by Many-to-Many Pivot Value

If you have a Laravel many-to-many relationship and need to sort the Collection by the pivot table value, the query may be complicated. Let's take a look at this example.

Imagine this DB structure:

companies:

• id
• name

services

• id
• name

company_service

• company_id
• service_id
• avg_rating

Your task is to get the companies that offer service with a specific ID, ordered by their average rating for that service.

First, I want to emphasize that it's better to perform that ordering on the database level and not on the Collections level with Query Builder, something like this:

$serviceId = 7;
$companies = Company::query()
    ->join('company_service', 'companies.id', '=', 'company_service.company_id')
    ->where('company_service.service_id', $serviceId)
    ->orderBy('company_service.avg_rating', 'desc')
    ->get();

But sometimes it's impossible: for example, when the Collection of Companies is returned from an external data source like Algolia or Elasticsearch. Then we have a Collection and need to order it by a pivot field.

Then, in Collections, we need to use the sortBy() or sortByDesc() methods, with a callback function as a parameter.

$serviceId = 7;
$companies = Company::query()
    ->with(['services'])
    ->get()
    ->sortByDesc(function (Company $company) use ($serviceId) {
        return $company->services
            ->where('id', $serviceId)
            ->first()
            ?->pivot
            ->avg_rating ?? 0;
    });

Let's break it down a bit so that you will understand it better.

The first part, Company::query()->with(['services'])->get() is our initial Collection, before sorting. We must also eager-load services to avoid the N+1 query problem.

Next, we use sortByDesc(), passing use ($serviceId) to a callback function, and the result of that callback function should be the number we should sort by.

We get that number from the ->first() row of the services relationship, taking ->pivot->avg_rating from it.

Just in case that related record may not exist, we add two checks:

• first()?->pivot would not throw an error and would just return NULL if that first() doesn't exist
• ?? 0 would be the default fallback value in case avg_rating doesn't return anything

This is just a quick example but with a broader message: if you want to sort the Collection with many-to-many relationship by pivot table value, the code above is one way to structure the query.

You can see more examples of Collection manipulations in my course: Laravel Collections Chains: 15 Real Examples