CODEWITHSUNDEEP
phpmysqljoinactiverecordyii2
Joe
Joe

Reputation: 2591

Yii2 viaTable join condition

Is it possible to set condition in join section in 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 it's not a good solution. Why? When you do a left join the id_role condition will make it inner join actually. The id_role condition should be placed inside ON section of the join.

I was searching the web plus inspecting the code but I don't see how it could be solved.

Upvotes: 7

Views: 12530

Answers (2)

Joe
Joe

Reputation: 2591

I got the answer 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]);
                      });

Upvotes: 15

Mihai P.
Mihai P.

Reputation: 9367

Have you tried doing it like 

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

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

Upvotes: 0

Related Questions