SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role

I just want to get count from users based on roles table, but I when I try to code, I get the error message SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role_user.id' in 'on clause'

this is the Controller

 $countUser = DB::table(DB::raw('users'))
                    ->select(DB::raw('count(users.id) AS countUser'))
                    ->join('role_user', 'users.id', '=', 'role_user.user_id')
                    ->join('roles', 'role_user.id', '=', 'roles.id')
                    ->groupByRaw('role_user.role_id')->pluck('countUser');

In my SQL is work, this the code

SELECT r.name, count(u.id) AS countUser  
FROM users u, role_user ru, roles r 
WHERE u.id = ru.user_id 
AND ru.role_id = r.id
GROUP BY r.id

and this the result

How to fix it?

CodePudding user response：

Are you sure your role_user table has an id field?

It looks like the table relates users to roles so I imagine it only has user_id and role_id.

Your join probably needs to be

->join('roles', 'role_user.role_id', '=', 'roles.id')

The SQL you added to your question shows the table having those fields and not an id.