A
A
Anatoly Demyanenko2019-02-09 15:12:24
Yii
Anatoly Demyanenko, 2019-02-09 15:12:24

How to form an unusual query on Yii2 related tables?

There are 2 tables with a many-to-many relationship.
First (users)
id | name
1 | maxim
2 | lena
Second (roles)
id | role_name
1 | admin2
| moder
3 | author
AND relation table
id | user_id | role_id
1 | 1 | 2
2 | 2 | 2
3 | 2 | 3
And so Maxim we have only moderator and Lena and moder and author.
The Users model has a relationship

public function getRoles(){
    return $this->hasMany(Roles::className(),['id'=>'role_id'])
                ->viaTable('relation', ['user_id' => 'id']);
  }

Making a request
$query = Users::find()
      ->joinWith(['roles rl'])
 ->andWhere(['rl.id', $searchModel->roles])
->all();

"$searchModel->roles" - The parameter passed from the form is an array of numbers ( role id) in this example, we will pass such an array [2,3].
And of course we get Maxim and Lena, which is correct, since in "Where" we have "rl.id in (2,3)"
but I need to get only the record that has both values ​​\u200b\u200band not one of (in this case, Lena)
tried do like this
$query = Users::find()
      ->joinWith(['roles rl']);
 foreach ($searchModel->roles as $role){
      $query->andWhere(['=','rl.id', $role]);
}
$result = $query->all();

And by itself received null that is expected.
Please suggest a solution to this issue. Namely, getting a record that will have both values ​​from a knitted table and not one of them.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question