从Yii2中的联结表中检索数据

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.

我试图从Yii2中的连接表中获取数据，而无需额外的查询。我有2个模型（用户，组）通过联结表（user_group）关联。在user_group表中，我想为此关系存储额外数据（admin flag，...）。

• 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.

  将数据添加到联结表的最佳方法是什么？ link方法接受参数extraColumns，但我无法弄清楚它是如何工作的。

• 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

Group.php

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

User.php

user.php的

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

4 个解决方案

#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.

简而言之：像你建议的那样使用ActiveRecord作为联结表是恕我直言，因为你可以设置via（）来使用现有的ActiveRecord。这允许您使用Yii的link（）方法在联结表中创建项目，同时添加数据（如管理标志）。

---

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.

官方的Yii Guide 2.0说明了两种使用联结表的方法：使用viaTable（）和使用via（）（见这里）。虽然前者期望联结表的名称作为参数，但后者期望关系名称作为参数。

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():

如果您需要访问联结表中的数据，我会按照您的建议使用ActiveRecord作为联结表，并使用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):

这样，您可以使用userGroups关系获取联结表的数据而无需其他查询（与任何其他一对多关系一样）：

$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:

这一切都可以使用hasMany关系完成。所以你可能会问为什么你应该使用via（）声明多对多关系：因为你可以使用Yii的link（）方法在联结表中创建项目：

$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())
}

#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:

由于我差不多14天没有得到答案，我会发布我是如何解决这个问题的。这不是我想到的，但是它有效，现在已经足够了。所以...这就是我所做的：

1. Added a model UserGroup for the junction table
2. 为联结表添加了一个模型UserGroup

3. Added a relation to Group

   添加了与Group的关系

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

4. Joined UserGroup in my search model function

   在我的搜索模型函数中加入了UserGroup

   $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.

这就是我想要的，包含所有用户的组，以及我的新模型UserGroup代表的联结表中的数据。

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.

我想首先扩展构建Yii2函数的查询 - 这可能是解决此问题的更好方法。但由于我还不太了解Yii2，我现在决定不这样做。

Please let me know if you have a better solution.

如果您有更好的解决方案，请告诉我。

#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.

在User model public $ flag;中添​​加新的class属性。在您的基本关系中附加两行，但不删除viaTable，这可以（并且应该）保留。

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.

leftJoin可以从联结表中选择数据，并选择自定义返回列。请记住，viaTable必须保留，因为link（）依赖于它。

2) sub-select query

Add new class attribute in User model public $flag;

在User model public $ flag中添加新的class属性;

And in Group model modified getUsers() relation:

并在Group模型中修改了getUsers（）关系：

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.

$ Group-> admins - 获取具有特定管理标志的用户。但是这个解决方案不会添加属性$ flag。您需要知道何时只选择管理员和所有用户。缺点：您需要为每个标志值创建单独的关系。

---

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.

使用单独模型UserGroup的解决方案仍然更灵活，适用于所有情况。就像你可以添加验证和基本的ActiveRecord东西。这些解决方案更多的是单向方向 - 将问题排除在外。

#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

有关更多信息，请查看Yii2联结表属性扩展

Thanks.

谢谢。