Retrieve data from junction table in Yii2

Question

I'm trying to get data from a join table in Yii2 without an additional query. I have 2 models (User, Group) associated via the junction table (user_group). In the user_group table, I want to store extra data (admin flag, ...) for this relation.

• What's the best way to add data to the junction table? The link method accepts a parameter extraColumns but I can't figure out how this works.

• What's the best way to retrieve this data? I wrote an additional query to get the values out of the junction table. There must be a cleaner way to do this?!

FYI, this is how I defined the relation in the models:

Group.php

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

User.php

public function getGroups() {
    return $this->hasMany(Group::className(), ['id' => 'group_id'])
        ->viaTable('user_group', ['user_id' => 'id']);
}

Answer 1

In short: Using an ActiveRecord for the junction table like you suggested is IMHO the right way because you can set up via() to use that existing ActiveRecord. This allows you to use Yii's link() method to create items in the junction table while adding data (like your admin flag) at the same time.

---

The official Yii Guide 2.0 states two ways of using a junction table: using viaTable() and using via() (see here). While the former expects the name of the junction table as parameter the latter expects a relation name as parameter.

If you need access to the data inside the junction table I would use an ActiveRecord for the junction table as you suggested and use via():

class User extends ActiveRecord
{
    public function getUserGroups() {
        // one-to-many
        return $this->hasMany(UserGroup::className(), ['user_id' => 'id']);
    }
}

class Group extends ActiveRecord
{
    public function getUserGroups() {
        // one-to-many
        return $this->hasMany(UserGroup::className(), ['group_id' => 'id']);
    }

    public function getUsers()
    {
        // many-to-many: uses userGroups relation above which uses an ActiveRecord class
        return $this->hasMany(User::className(), ['id' => 'user_id'])
            ->via('userGroups');
    }
}

class UserGroup extends ActiveRecord
{
    public function getUser() {
        // one-to-one
        return $this->hasOne(User::className(), ['id' => 'user_id']);
    }

    public function getGroup() {
        // one-to-one
        return $this->hasOne(Group::className(), ['id' => 'userh_id']);
    }
}

This way you can get the data of the junction table without additional queries using the userGroups relation (like with any other one-to-many relation):

$group = Group::find()->where(['id' => $id])->with('userGroups.user')->one();
// --> 3 queries: find group, find user_group, find user
// $group->userGroups contains data of the junction table, for example:
$isAdmin = $group->userGroups[0]->adminFlag
// and the user is also fetched:
$userName = $group->userGroups[0]->user->name

This all can be done using the hasMany relation. So you may ask why you should declare the many-to-many relation using via(): Because you can use Yii's link() method to create items in the junction table:

$userGroup = new UserGroup();
// load data from form into $userGroup and validate
if ($userGroup->load(Yii::$app->request->post()) && $userGroup->validate()) {
    // all data in $userGroup is valid
    // --> create item in junction table incl. additional data
    $group->link('users', $user, $userGroup->getDirtyAttributes())
}

Answer 2

Since I have received no answer for almost 14 days, I'll post how I solved this problem. This is not exactly what I had in mind but it works, that's enough for now. So... this is what I did:

1. Added a model UserGroup for the junction table

2. Added a relation to Group

   public function getUserGroups()
   {
       return $this->hasMany(UserGroup::className(), ['user_id' => 'id']);
   }
   

3. Joined UserGroup in my search model function

   $query = Group::find()->where('id =' . $id)->with('users')->with('userGroups');
   

This get's me what I wanted, the Group with all Users and, represented by my new model UserGroup, the data from the junction table.

I thought about extending the query building Yii2 function first - this might be a better way to solve this. But since I don't know Yii2 very well yet, I decided not to do for now.

Please let me know if you have a better solution.

Answer 3

I don't know for sure it is best solution. But for my project it will be good for now :)

1) Left join

Add new class attribute in User model public $flag;. Append two lines to your basic relation but don't remove viaTable this can (and should) stay.

public function getUsers()
{
    return $this->hasMany(User::className(), ['id' => 'user_id'])
        ->viaTable('user_group', ['group_id' => 'id'])
        ->leftJoin('user_group', '{{user}}.id=user_id')
        ->select('{{user}}.*, flag') //or all ->select('*');
}

leftJoin makes possible to select data from junction table and with select to customize your return columns. Remember that viaTable must stay because link() relies on it.

2) sub-select query

Add new class attribute in User model public $flag;

And in Group model modified getUsers() relation:

public function getUsers()
{
    return $this->hasMany(User::className(), ['id' => 'user_id'])
        ->viaTable('user_group', ['group_id' => 'id'])
        ->select('*, (SELECT flag FROM user_group WHERE group_id='.$this->id.' AND user_id=user.id LIMIT 1) as flag');
}

As you can see i added sub-select for default select list. This select is for users not group model. Yes, i agree this is litle bit ugly but does the job.

3) Condition relations

Different option is to create one more relation for admins only:

// Select all users
public function getUsers() { .. }

// Select only admins (users with specific flag )
public function getAdmins()
{
    return $this->hasMany(User::className(), ['id' => 'user_id'])
        ->viaTable('user_group', ['group_id' => 'id'],
        function($q){
             return $q->andWhere([ 'flag' => 'ADMIN' ]); 
        });
}

$Group->admins - get users with specific admin flag. But this solution doesn't add attribute $flag. You need to know when you select only admins and when all users. Downside: you need to create separate relation for every flag value.

---

Your solution with using separate model UserGroup still is more flexible and universal for all cases. Like you can add validation and basic ActiveRecord stuff. These solutions are more one way direction - to get stuff out.

Answer 4

For that purpose I've created a simple extension, that allows to attach columns in junction table to child model in relation as properties. So after setting up this extension you will be able to access junction table attributes like

foreach ($parentModel->relatedModels as $childModel)
{
    $childModel->junction_table_column1;
    $childModel->junction_table_column2;
    ....
}

For more info please have look at Yii2 junction table attributes extension

Thanks.