Creating relationships in Yii2 through 2 tables

Question

There are 4 tables in the database

CREATE TABLE user
(
    id INT,
    date_birth DATE,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email VARCHAR(30),
    phone VARCHAR(20)
)

CREATE TABLE account
(
    id INT,
    user_id INT,
    ssml_number INT,
    room_number INT,
)

CREATE TABLE account_role
(
    id INT,
    account_id INT,
    role_id INT,
)

CREATE TABLE role
(
    id INT,
    name VARCHAR(80),
    description TEXT,
)

Relationship between tables User <-> Account - One-to-many One user can have multiple accounts Relationship between tables Account <-> Role - Many-to-many (via table account_role)

Is it possible to create a relation in the User Model so that I can get all user roles from all accounts? Example through hasmany viatable, or in a smarter way

What would I then use like this $user->roles

And how can I filter in the UserSearch (with $query for DataProvider) all users who have at least one role or all users who do not have roles (no role at all)?

Answer 1

Create models for User, Account, Role. Relations may be created in one model or in each model separately. I prefer the last one, but the both ways correct.

User

public function getAccounts()
{
    return $this->hasMany(Account::class, ['user_id' => 'id']);
}

Account

public function getRoles()
{
    return $this->hasMany(Role::class, ['id' => 'role_id'])
            ->viaTable('account_role', ['account_id' => 'id']);
}

Example

$user = User::find()
    // eager loading "accounts" and the nested relation "accounts.roles"
    ->with(['accounts.roles'])
    ->andWhere(['id' => $id])
    ->one();

foreach ($user->accounts as $account) {
    foreach ($account->roles as $role) {

    }
}

If you want to get roles directly. Let combine all relations together. Create additional AccountRole model.

public function getRoles()
{
    return $this->hasMany(Role::class, ['id' => 'role_id'])
            ->via('accountRoles');
}

public function getAccountRoles()
{
    return $this->hasMany(AccountRole::class, ['account_id' => 'id'])
            ->via('accounts');
}

public function getAccounts()
{
    return $this->hasMany(Account::class, ['user_id' => 'id']);
}

Example

$user = User::find()
    // eager loading "accounts" and the nested relation "accounts.roles"
    ->with(['accounts.roles'])
    ->andWhere(['id' => $id])
    ->one();

foreach ($user->roles as $role) {
}