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 thatfirst()
doesn't exist -
?? 0
would be the default fallback value in caseavg_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
No comments or questions yet...