Yii 2 Filter many-to-many relation on a value in the junction table

Question

How can I use a many to many relation in Yii 2.0 where I filter the results on values that are in the connection/junction table?

I have the following tables

Member (id INT, name, ...)
Event (id INT, name, date, ...)
EventMemberConnection (id INT, event_id INT, member_id INT, accepted TINYINT(1) DEFAULT 0, foreign keys....)

Where members are connected to events through EventMemberConnection, with an integer telling if they are accepted or declined

From model file Event.php:

/**
 * @return \yii\db\ActiveQuery
 */
public function getAcceptedMembers() {
    return $this->hasMany(Member::className(), ['id' => 'member_id'])->viaTable('EventMemberConnection acceptedConnection', ['event_id' => 'id'])->onCondition(['acceptedConnection.accepted' => 1]);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getDeclinedMembers() {
    return $this->hasMany(Member::className(), ['id' => 'member_id'])->viaTable('EventMemberConnection declinedConnection', ['event_id' => 'id'])->onCondition(['declinedConnection.accepted' => 0]);
}

And the query, inside a rest controller:

public function prepareDataProvider() {
    $query = Event::find();

    $query->joinWith('acceptedMembers acceptedMember');
    $query->joinWith('declinedMembers declinedMember');

    $query->groupBy(['Event.id']);

    return new ActiveDataProvider([
        'query' => $query
    ]);
}

I am using the REST API to fetch event objects extended with both acceptedMembers and declinedMembers. I get the following database error from the call:

Column not found: 1054 Unknown column 'acceptedConnection.accepted' in 'where clause'
The SQL being executed was: SELECT * FROM `Member` `acceptedMember` WHERE (`id` IN ('5', '9')) AND (`acceptedConnection`.`accepted`=1)

I guess that the main query went fine, but the extra call where Yii fetches the related members it adds the condition used in the onConnection function.

I read in the documentation that it is only supposed to be used to filter on the related table and not the junction table. So I don't believe that onCondition is the way to go.

Is there a way to filter the relation on values set in the junction table?

Answer 1

I found in the documentation that you can add a callback to the viaTable() function that can be used to customize the join query. You can use it like this:

public function getAcceptedMembers() {
    return $this->hasMany(Member::className(), ['id' => 'member_id'])->viaTable('EventMemberConnection acceptedConnection', ['event_id' => 'id'], function($query){
        $query->where(['acceptedConnection.accepted' => 1]);
    });
}