Yii2: Populating model field with value from many-to-many relation

Question

We are trying to set-up our REST API in a way that a user can only retrieve data related to his account. Uptill now, We've only had a public API and it all worked like a charm. The public API will still get used, so we started from scratch.

We have a User model, extending ActiveRecord and implementing IdentityInterface. A User will have a relation to a Customer model (Many:Many, but at least one). A User will have only one MASTER Customer. The above is saved in the database using 3 tables app_customer_users,app_customers and link_customer_users_customers. The join table contains a boolean field master.

In the User model:

public function getCustomers() {
    return $this->hasMany(Customer::className(), ['id' => 'customer_id'])
        ->viaTable('link_customer_users_customers', ['user_id' => 'id']);
}

In the Customer model:

public function getUsers() {
    return $this->hasMany(User::className(), ['id' => 'user_id'])
        ->viaTable('link_customer_users_customers', ['customer_id' => 'id']);
}

This works great if we would request all customers, they'll have 'users' populated (if we add it in extraFields etc, etc...)

The new API uses Basic user:pass in authentication, and we can get the current user object/identity by calling Yii::$app->user->getIdentity().

NOW THE PROBLEM

We would like to be able to include the Customer ID when a user connects in a way that we can get it by calling Yii::$app->user->getIdentity()->customer_id OR Yii::$app->user->getIdentity()->getCustomerId() The Customer ID should be the ID where master in the join table == true.

We've tried adding it to fields, as we did before with hasOne relations, but in this case it does not seem to work:

$fields['customer_id'] = function($model) {
    return $model->getCustomers()->where('link_customer_users_customers.master', true)->one()->customer_id;
}; // probably the where part is our porblem?

And we've tried creating a custom getter like this:

public function getCustomerId() {
    return $this->getCustomers()->andFilterWhere(['link_customer_users_customers.master' => true])->one()->customer_id;
}

This last attempt resulted in an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'link_customer_users_customers.master' in 'where clause'\nThe SQL being executed was: SELECT * FROM `app_customers` WHERE (`link_customer_users_customers`.`master`=TRUE) AND (`id`='93')

We've been searching in the docs and on Google, but did not find an example on how to do this.

SOLUTION

Based on accepted answer we added some code.

In User model:

// Include customer always with user
public static function findIdentity($id) {
    return static::findOne($id)->with('customer');
}

// Next to `getCustomers()`, added this `hasOne` relation to get only the customer from
// which the current user is the master user
public function getCustomer() {
    return $this->hasOne(Customer::className(), ['id' => 'customer_id'])
        ->viaTable('link_customer_users_customers', ['user_id' => 'id'], function ($query) {
            $query->andWhere(['master' => 1]);
        });
}

// Getter, just because
public function getCustomerId() {
    return $this->customer->id;
}

And now we can get the ID from the customer by calling Yii::$app->user->getIdentity()->customer->id OR Yii::$app->user->identity->customer->id etc, etc.. in the project..

Answer 1

You should add a relation like below and use an anonymous function as the third parameter to the viaTable to add another condition to check master field as true.

public function getMasterCustomerId() {
    return $this->hasOne(Customer::className(), ['id' => 'customer_id'])
    ->viaTable('link_customer_users_customers', ['user_id' => 'id'],function($query){
       $query->andWhere(['master' => 1]);
   });
}

and then you can use it for the logged in user as below.

Yii::$app->user->identity->masterCustomerId->id;