laravel query builder count relationship

November 19, 2021

I am stuck with a query builder in laravel 8

DB::table('users')
    ->join('contracts', 'users.id', '=', 'contracts.user_id')
    ->join('roles', 'roles.id', '=', 'users.role_id')
    ->select('users.id', 'users.username', 'users.email', DB::raw('COUNT(contracts.user_id) as numberOfContracts'))
    ->groupBy('users.id')
    ->paginate(10);

i am getting only 80 of total entries …. and I have 103 users.

I want to display all the users with paginate of course….. and show for each user the role and how many contracts have.

What I did wrong ?

*** UPDATE ***

the tables are these:

users (id, username, email, role_id)

contracts (id, name, user_id)

roles (id, name)

*** update 2 ***

If run the eloquent

User::with('contracts', 'role')
->withCount('contracts')
->paginate(10);

it return all the 103 with count. But how to do it with query builder ?

>Solution :

You were using straight JOIN (which equated to an INNER JOIN since that’s what MySQL defaults to) in Laravel Query Builder. It will not account for Empty contracts, or roles. You need a LEFT JOIN to do this.

DB::table('users')
    ->leftJoin('contracts', 'users.id', '=', 'contracts.user_id')
    ->leftJoin('roles', 'roles.id', '=', 'users.role_id')
    ->select('users.id', 'users.username', 'users.email', DB::raw('COUNT(contracts.user_id) as numberOfContracts'))
    ->groupBy('users.id')
    ->paginate(10);

See This SO Question to see the difference in JOINS — Difference in MySQL JOIN vs LEFT JOIN