Yii2 via join join condition

Question

Is it possible to set a condition in the connection section via viaTable? Currently I got this:

return $this->hasMany(User::className(), ['id' => 'id_user']) ->from(User::tableName()) ->viaTable(RoomActiveUser::tableName(), ['id_room' => 'id'], function($query) { return $query->andWhere(['id_role' => RoleHelper::getConsultantRole()->id]); }); 

But this is a bad decision. What for? When you do a left join, the id_role condition will make it an inner join. The id_role condition must be placed inside the ON section of the connection.

I searched the website and checked the code, but I don’t see how this can be solved.

Answer 1

I got a response from Qiang Xue - $query->onCondition() should be used for what I need. Result Code:

 return $this->hasMany(User::className(), ['id' => 'id_user']) ->from(User::tableName()) ->viaTable(RoomActiveUser::tableName(), ['id_room' => 'id'], function($query) { $query->onCondition(['id_role' => RoleHelper::getConsultantRole()->id]); }); 

Answer 2

Did you try to do it like

 ->viaTable(RoomActiveUser::tableName(), ['id_room' => 'id', 'id_role' => RoleHelper::getConsultantRole()->id]) 

This should create JOIN X on 'id_room' = 'id' AND 'id_role' = '$ConsultantRole_id'