Yii2: Why yii\rbac\DbManager::$assignmentTable user_id is varchar ?

[cetver]

I propose to do it integer, because if you keep role in yii\rbac\DbManager::$assignmentTable and yii\rbac\DbManager::$itemTable you cannot display it, if users table PK is integer.
To get the role you need such query:

SELECT *
FROM users u 
INNER JOIN auth_assignments aa 
ON u.id = CAST(aa.user_id AS INTEGER) -- <-- HERE IS THE PROBLEM 
INNER JOIN auth_items ai 
ON ai.name = aa.item_name
AND ai.type = 1 --\yii\rbac\Item::TYPE_ROLE

You cannot create query above via "relation magic", because \yii\db\ActiveQuery::$link doesn't support SQL functions.

Additional info

Getting this error if trying to show role via data provider:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer = character varying
LINE 1: ...ers" LEFT JOIN "auth_assignments" ON "users"."id" = "auth_as...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The SQL being executed was: SELECT COUNT(*) FROM "users" LEFT JOIN "auth_assignments" ON "users"."id" = "auth_assignments"."user_id" LEFT JOIN "auth_items" ON "auth_assignments"."item_name" = "auth_items"."name" WHERE "type"=1

// ActiveDataProvider.query
$query = self::find()->joinWith([
    'authAssignment' => function(Assignment $query) {
        $query->joinWith('role');
    }
]);
// User
public function getAuthAssignment()
{
    return $this->hasOne(Assignment::className(), ['user_id' => 'id']);
}
// Assignment
public function getItem()
{
    return $this->hasOne(Item::className(), ['name' => 'item_name']);
}
public function getRole()
{
    //role() - is a search query with condtion: $this->andWhere(['type' => \yii\rbac\Item::TYPE_ROLE]);
    return $this->getItem()->role();
}

Additional info

| Q | A |
| --- | --- |
| Yii version | 2.0.6 |
| PHP version | 7.0.8-0ubuntu0.16.04.1 |
| Operating system | Ubuntu 16.04 |

[samdark]

Because Identity could use anything as IDs. Not only ints.

[krukru]

Could we override this when generating the initial migration for rbac? This way we could have a proper foreign key.

[samdark]

Yes. You're free to create your own migration for your RBAC structure.

[cebe]

its acutally better to copy the yii migration into application code to make sure it does not change after being applied.

[rob006]

@cebe We should not change existing migrations in the first place.

[cebe]

still I now consider including migrations from code you do not control as a problem. Seen it too often that migrations are changed in code that is shared in extensions. Copying makes sure it stays the same. Duplication in migrations is not a problem as they are not subject to change.

[samdark]

@cebe worth documenting it..

[rob006]

@cebe Copying every migration is problematic if you use extension/module which changes DB structure pretty often. Each update will require to check if there are new migrations in every extension. As a freamework we should teach how to write migrations in the right way, rather than recommend copying migrations even from framework core.

[cebe]

fine then, but we should ensure that we do not change our migrations (which is what happened with rbac a lot).