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 | 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.
Advertisement
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 { }