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 !

score 0 · Answer 1 · answered Oct 08 '15 at 14:30

In ActiveRecord:

/**
 * @inheritdoc
 * @return MyActiveRecordModelQuery the active query used by this AR class.
 */
public static function find()
{
    return new MyActiveRecordModelQuery(get_called_class());
}

MyActiveRecordModelQuery:

/**
 * @method MyActiveRecordModelQuery one($db = null)
 * @method MyActiveRecordModelQuery[] all($db = null)
 */
class MyActiveRecordModelQuery extends ActiveQuery
{
    /**
     * @return $this
     */
    public function fromDepartment($id)
    {
        $this->andWhere(['departament_id' => $id]); //or use relation

        return $this;
    }
}

Usage:

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

Thanks BUT, I assume that `MyActiveRecordModelQuery` is for my `Department` model, but this way we select Departments, not Users :| — Ixon, Oct 08 '15 at 15:56

score -1 · Accepted Answer · answered Oct 08 '15 at 14:32

-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);

answered Oct 08 '15 at 14:32

Sergey

5,121
22
36

Of course ! this is brilliant for me. THANKS ! I used it in ActiveQuery like: User model: `public static function find() { $q = new UserQuery(get_called_class()); return $q->userDepartment(\Yii::$app->user->identity->department_id)->orderBy('surname ASC'); }` and ActiveQuery: `class UserQuery extends \yii\db\ActiveQuery { public function userDepartment($departmentId) { return $this ->joinWith(['branches']) ->andWhere(['department_id'=>$departmentId]); } }` – Ixon Oct 08 '15 at 16:14

yii2 get activerecords by relation column

2 Answers2