How to select models with related models count 0 in Laravel 4?

2
votes

I'm developing PHP application using Laravel 4 framework. I have setup user and role models. Role can belong to many users and a user can have many roles. I would like to select users without any roles. How to do this query using Laravel's ORM?

Here is my Role.php

class Role extends Eloquent {

    protected $table = 'roles';

    public function users() {
       return $this->belongsToMany('User', 'user_roles');
    }

}

And in my User.php I have this code:

public function roles() {
    return $this->belongsToMany('Role', 'user_roles');
}

I have tried the code below, but it does not return any users even I have created users without roles.

User::has('roles', '=', 0)->paginate(15);

The code below works, but the users are stdClass objects. I need them to be User objects.

$users = DB::table('users')->whereNotIn('id', function($query){
            $query->select('user_id')->from('user_roles');
         })->paginate(15);

EDIT

Added query log for User::has('roles', '=', 0)->paginate(15)

array(3) { ["query"]=> string(189) "select * from "users" where (select count(*) from "roles" inner join "user_roles" on "roles"."id" = "user_roles"."role_id" where "user_roles"."user_id" = "users"."id") = ? limit 15 offset 0" ["bindings"]=> array(1) { [0]=> int(0) } ["time"]=> float(0.11) }

1
phpormlaravellaravel-4
User::has('roles', '=', 0)->paginate(15); Check the query that is generated by that statement. (It will see if a user has a role with id 0 which shouldn't exist. Use where NOT EXISTS. $users = DB::table('users'). You query a table, you don't ask the framework to deliver a model. (I don't know laravel so I can't tell you how to fix that one) – AmazingDreams
I think User:has('roles', '=', 0) queries by the count not by the id. See this: laravel.com/docs/eloquent#querying-relations – Joonas
If the User is not set a role, is the value set to 0 or null on your DB. User::has('roles','=',0)->paginate(15) should work. If the value null is being set to the non-assigned roles, then set default to 0 to that column and check again – Abishek

1 Answers

2
votes

Try this:

User::whereNotIn('id', function($query){
        $query->select('user_id')->from('user_roles');
     })->paginate(15);