laravel query builder count relationship

Question

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 ?

If you did a straight query in MySQL `SELECT * FROM users GROUP BY users.id` How many rows are returned? Could it be there are duplicate IDs? — Zak, Nov 19 '21 at 18:10
Try that, without knowing your tables, and what's in them .. I am guessing one of your JOIN statements is limiting the number returned. — Zak, Nov 19 '21 at 18:14
i have updated the question with the structure of the tables — calin24, Nov 19 '21 at 18:18
I would STLL run the query manually .. So you have a better understanding of what's being returned. — Zak, Nov 19 '21 at 18:21
It would look something like: `SELECT a.id, a.username, a.email, COUNT(b.user_id) as numberOfContracts FROM users a JEFT JOIN contracts b ON a.id = b.user_id LEFT JOIN roles c ON c.id = a.role_id GROUP BY a.id` — Zak, Nov 19 '21 at 18:26
I am still guessing that unless EVERY user has at least one entry in "contracts" AND every user has at least one entry in "roles" -- That's your issue. — Zak, Nov 19 '21 at 18:29
your query from up is ok....it return all 103 users and counts the contracts. — calin24, Nov 19 '21 at 18:33
Good .. Try the original query with `LEFT JOINS` -- `->leftJoin(` That should account for the "empties" — Zak, Nov 19 '21 at 18:34

Zak · Accepted Answer · 2021-11-19T18:40:29.333

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

i didn't know that simple join don't count empty ....in my case contracts :-) — calin24, Nov 19 '21 at 18:40

laravel query builder count relationship

1 Answers1