Create a one to one relationship between users and roles tables by creating a new field called role_id in your user table.

If you are using eloquent have a look at:h ttps://laravel.com/docs/5.1/eloquent-relationships#defining-relationships

I have it and for User model I have:

public function role()
{
    return $this->belongsTo('App\Role');
}

And now I try to use it:

$users = User::with(['role' => function($query) {
    $query->where('admin', true);
}])->get();

But it shows all users.

Let's see your schema.

You could try $query->where('admin', '=', true);

I analyzed it in DebugBar. For $query->where('admin', true); it returns:

select * from `users`
select * from `roles` where `roles`.`id` in ('1', '2', '5') and `admin` = '1'

// only roles with id 1 and 2 have admin privileges

And $query->where('admin', '=', true) returns in DebugBar the same queries and in results I get still all users.

Is the admin field defined as varchar or bool or as an int in your role table.

If you run this query directly (in the mysql console) select * from roles where roles.id in ('1', '2', '5') and admin = '1'

What results do you get?

It's my migration for roles table:

Schema::create('roles', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->boolean('admin');
});

When I run select * from roles where roles.id in ('1', '2', '5') and admin = '1' in phpMyAdmin it returns only admin roles. I thought this example is exactly what I need: https://laravel.com/docs/5.2/eloquent-relationships#constraining-eager-loads

If you do a 'desc role' in phpmyadmin is the column tiny int?

Put this on your role model file and change your type to integer in the schema builder creation script for the admin field

protected $casts = [ 'admin' => 'boolean', ];

I think the problem is not here. For example I changed my function for this:

$users = User::with(['role' => function($query) {
    $query->where('name', '=', 'Admin');
}])->get();

and I still get all users.

It seems that first I get all users separately and next roles with their parameters separately. But how to join this result together?

The foreign keys need to be setup between the two tables and you need this in the user model

public function role() { return $this->hasOne('App\Role'); }

move the belongto to the role table

public function role() { return $this->belongsTo('App\User'); }

If you have a column called role_id in your users table this is how you would set it up

$table->foreign('role_id')->references('id')->on('roles'); });

lukasz said:

I added foreign key, but I think, I can't use hasOne in my situation, because an one user has an one role, but many users have the same role. So $this->hasOne('App\Role') generates an error:

Column not found: 1054 Unknown column 'roles.user_id'

EDIT:

Finally I found a solution!

$users = User::whereHas('role', function($query) {
   $query->where('admin', true);
})->get();

It generates an one query:

select * from `users` where exists (select * from `roles` where `users`.`role_id` = `roles`.`id` and `admin` = '1')

and returns exactly this users I need.

PS. @softwaredeveloperca thank you for your help.

Thanks to you guys. This solved my problem. I was using "with", but it should be whereHas.