Laravel how to Order by relation in scope?

Question

I have the following scope in User model

$query->with('country')
          ->when($filters['search'] ?? null, function ($query, $search) {
              return $query->where('name', 'LIKE', '%' . $search . '%')
                    ->orWhereHas('country', function ($query) use ($search) {
                        $query->where('name', 'LIKE', '%' . $search . '%');
                    });
          });

       
            $query->orderBy('name', 'asc');
       }

        return $query;
    }

I am pretty new to Laravel - I currently the above query is sorting by user name but I would like to sort by country name. I can do this with country_id as there is a relation but not sure how to sort by country name.

Thanks

Answer 1

there are two approaches we can use to order these users by their company. The first is using a join:

$users = User::select('users.*')
->join('countries', 'countries.id', '=', 'users.country_id')
->orderBy('companies.name')
->get();

Here is the generated SQL for this query:

select users.*
from users
inner join countries on countries.id = users.country_id
order by countries.name asc

The second way is using a subquery:

$users = User::orderBy(Country::select('name')
    ->whereColumn('countries.id', 'users.country_id')
)->get();

And you can see the reference here: ordering database queries by relationship columns in laravel