问题

I need to make an advanced query in Laravel hopefully using Eloquent to make that a collection but a query builder or raw normal SQL query is acceptable.

我需要在Laravel中进行高级查询，最好使用Eloquent来将其转换为集合，但查询构建器或原始的SQL查询也可以接受。

I have users table with User model having relation:

我有一个名为"users"的表，使用User模型，具有以下关联：

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

I have roles table, with Role model having relation:

我有一个名为"roles"的表，使用Role模型，具有以下关联：

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

So, I have users_roles table with columns [user_id, role_id], so I can have users with multiple roles

因此，我有一个名为"users_roles"的表，具有列[user_id, role_id]，因此我可以拥有具有多个角色的用户。

I want to get all users with roles different from a certain role, for example get all users that are all kind of roles except role of id 1

我想获取具有不同于特定角色的所有用户，例如获取除了id为1的角色之外的所有角色的用户。

how to do that?

如何做到这一点？

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

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

答案1

得分: 0

你可以将一个 where 子句传递给 withWhereHas，以定义根据关联来返回哪些模型的规则，如下所示：

$usersWhereRoleIsNotOne = User::query()
    ->withWhereHas('roles', function(Builder $builder){
        $builder->whereNot('id', 1);
    })
    ->get();

$usersWhereRoleIsNotOne = User::query()
    ->withWhereHas('roles', function(Builder $builder){
        $builder->whereNot('id', 1);
    })
    ->get();

答案2

得分: 0

I understand. Here is the translated code:

$roleId = 1;
$users = User::with($withArray)->whereDoesntHave('roles', function ($query) use ($roleId) {
    $query->where('roles.id', $roleId);
})->get();

$roleId = 1;
$users = User::with($withArray)->whereDoesntHave('roles', function ($query) use ($roleId) {
                    $query->where('roles.id', $roleId);
})->get();