How to display a record that has no relation to a table

What I need is to count the number of vehicles created by each user, but the problem is that when I make the query for the relationship, it only brings up the users who have vehicles created, but I also need to show the users who do not have vehicles created.

public function clients()
{
    $user_id = Auth::user()->id;

    $users = DB::table('users')
        ->join('mechanic_client', 'users.id', '=', 'mechanic_client.user_id')
        ->where('mechanic_client.mechanic_id', '=', $user_id)
        ->select('users.id', 'users.name', 'users.email', 'users.password', 'users.url', 'users.cant_vehicle');
        

    $vehicles = DB::table('vehicles')
             ->joinSub($users, 'users', function ($join) {
                 $join->on('vehicles.user_id', '=', 'users.id');
             })->select('users.id', 
                        'users.name', 
                        'users.email', 
                        'users.password', 
                        'users.url', 
                        DB::raw('count(*) as total'))
             ->groupBy('user_id')->get();

   return $vehicles
}

what i get

id	name	email	vehicles
65	name1	name1@name1	5
66	name2	name2@name2	1
67	name3	name3@name3	6

These are the users who have vehicles created, but what I need is to show all the independent users who have or do not have vehicles and also count the vehicles of each user.

Answer

It’s never the best option to use the raw DB:: query builder in Laravel. This could be very easily achieved with Eloquent approaches. Laravel has withCount() for counting relationships, described here. For check if mechanicClint exists, you can use has() method, that check if the relationship exists.

User::has('mechanicClients')
    ->withCount('vehicles');

This requires you to define the class and relationship. In Users.php.

use IlluminateFoundationAuthUser as Authenticatable;
use AppModelsVehicle;

class User extends Authenticatable
{

    public function vehicles()
    {
        return $this->hasMany(Vehicle::class);
    }

    public function mechanicClients()
    {
        return $this->hasMany(MechanicClient::class);
    }
}

And define the Vehicle.php and MechanicClient.php models.

use IlluminateDatabaseEloquentModel;

class Vehicle extends Model
{

}

class MechanicClient extends Model
{

}