How to sort the selection according to the related table?

Question

MySQL

There is a table users :
- id
- name
Table drivecat_user :
- drivecat_id
- user_id
Table rating :
- id
- user_id
- total // in addition to total there are many other values, but total is their sum and it is sorted
Here is a short selection code without unnecessary :

$users = User::where('city_id', $city_id);
  if ( $request->drivecats ) {
    $drivecats = explode(',', $request->drivecats);
    $users_id = DrivecatUser::whereIn('drivecat_id', $drivecats)->pluck('user_id');
    $users = $users->whereIn('id', $users_id);
  } else {
    $users = $users->has('drivecats')->with('drivecats');
  }

Here is the sort code:

$users = $users
    ->with('rating')
    ->join('rating', 'users.id', '=', 'rating.user_id')
    ->orderBy('rating.total', 'desc')
    ->paginate(12);

Before I connected sorting, there were no problems with the selection.
As soon as I connected it, then in the first case of the selection (with $request->drivecats) the following error pops up:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate from `users` inner join `rating` on `users`.`id` = `rating`.`user_id` where `city_id` = 4 and `id` in (2, 8, 14, 16))

If we remove the string from the sample $users = $users->whereIn('id', $users_id), then everything is in order again (but, of course, the result is not the same).
How to fix this situation?
Problem in sorting or all the same in selection?

Answer 1

I'm not a Laravelian, but judging by the text of the error, $users = $users->whereIn('id', $users_id); you need to specify what kind of id ? eg `users`.`id`
I hope the point is clear.

Answer 2

Example from google

$categories = Category::select(DB::raw('categories.*, count(*) as `aggregate`'))
    ->join('pictures', 'categories.id', '=', 'pictures.category_id')
    ->groupBy('category_id')
    ->orderBy('aggregate', 'desc')
    ->paginate(10);

Google has EVERYTHING