yii2 get activerecords by relation column

Question

I need to select ActiveRecord's that have related AR's with specific column value.

Situation: 'User' may have many 'Branches' - via junction table, and Branch is related to Department. I have department_id, and I want to select Users, that have branches from this single Department.

Department:

... $this->hasMany(Branch::className(), ['department_id' => 'id']);

Branch:

... $this->hasMany(User::className(), ['id' => 'user_id'])
                ->viaTable('{{%user_to_branch}}',['branch_id' => 'id']);

The thing is, that I do not want to access this from Department in any way (e.g. $department->getUsers()....), but i want to define this in ActiveQuery.

So i could select Users like:

User::find()->fromDepartment(5)->all();

THANK YOU in advance !

Answer 1

User model method

public function getBranch()
{
    return $this->hasMany(Branch::className(), ['id' => 'branch_id'])
                ->viaTable('{{%user_to_branch}}', ['user_id' => 'id']);
}

public static function fromDepartment($id)
{
    $query = self::find();
    $query->joinWith(['branch'])
          ->andWhere(['department_id'=>$id]);
    return $query->all();
}

Usage:

User::fromDepartment(5);