Yii2 query for users in group

Question

I had a DB that had a user table and a group table and the group table had a column user_id which made it simply to return a list of users in a group:

$users = User::find()
  ->where(['{{user}}.group_id' => $group_id])
  ->all();

Now the user_id column is gone and there is a third table group_user with user_id and group_id columns for the relationship.

I tried this:

$users = User::find()
  ->innerJoinWith('group_user)
  ->where(['{{group_user}}.group_id' => $group_id])

but received this error:

User has no relation named "group_user"

But I set the relationship in the User model:

public function getGroupUser() {
    return $this->hasOne(GroupUser::className(), ['user_id' => 'id']);
}

What am I missing? This is used in a Humhub API.

Answer 1

I would reprogram your getGroupUser (renaming it to getGroups) relation using viaTable:

public function getGroups() {
    return $this->hasMany(Group::className(), ['user_group.id_group' => 'group.id'])
        ->viaTable('user_group', ['user.id' => 'user_group.id_user']);
}

That would give you the Group(s) a User belongs to. But I think you are aiming to get the Users that belong to a given group, so similarly I would create a getUsers relation in your Group model:

public function getUsers() { 
      return $this->hasMany(User::className(), ['id' => 'user_id']) 
        ->viaTable('group_user', ['group_id' => 'id']); 

}

Then:

$group = Group::findOne($id_group);
$users = $group->getUsers()->all();