使用现有连接表的一对多关联 [英] One to many associations using existing join table

问题描述

我正在将使用 MariaDB 的现有应用程序的后端转换为使用 Sails (v0.10.0-rc7)，但我一直在试图弄清楚如何获取填充到 Role 模型中的角色的所有权限给定我必须使用的底层架构结构.

I'm converting the backend of an existing application that uses MariaDB to use Sails (v0.10.0-rc7) and I'm stuck trying to figure out how to get all permissions for a role populated into the Role model given the underlying schema structure I have to work with.

有三个表用于当前获取角色及其关联的权限，工作查询如下所示:

There are three tables that are used to currently get a role and it's associated permissions, and the working query looks something like this:

SELECT pm.permission, pm.permkey
FROM role_master rm
INNER JOIN role_perm rp ON ( rm.roleid = rp.roleid )
INNER JOIN perm_master pm ON ( rp.permid = pm.permid )
WHERE rm.roleid = 1
GROUP By pm.permission

如您所见，role_master 中有角色定义，role_perm 中有每个角色的单独权限，最后是perm_master 中的权限定义>.

As you can see, there is the role definition in role_master the individual permissions for each role in role_perm and finally the permission definitions in perm_master.

我已阅读这个很棒的维基关于关联，但我看不到任何对我有帮助的东西.

I've read this awesome wiki regarding associations but I don't see anything in there that will help me.

理想情况下，我想最终得到一个输出的角色模型:

Ideally, what I want to end up with is a Role model that outputs:

{
  "id"          : 1,
  "name"        : "My Role Name",
  "description" : "My Role Description",
  "permissions" : [ 'canread', 'canwrite', 'canjump', 'canplay' ]
}

在不修改底层数据库的情况下完成此任务的最佳方法是什么?

What is the best way of accomplishing this without modifying the underlying database?

推荐答案

Waterline 的优点之一是能够将模型映射到您的自定义表和列名称.然而，目前我们没有一个很好的方法来为自动生成的连接表做到这一点.我投入的早期作品之一是能够创建through 关联.这些本质上允许您构建一个用作连接表的模型.我们后来决定这基本上只是一个嵌套的填充，但我将 through 逻辑留在了那里，用于这样的用例.

One of the nice things about Waterline is the ability to map the models to your custom table and column names. However currently we don't have a nice way of doing this for the automatically generated join tables. One of the early pieces I put in was the ability to create through associations. These essentially allow you to build a model that functions as a join table. We later decided that this is basically just a nested populate but I left the through logic in there for use cases like this.

您无法向直通表添加其他属性，但如果您为连接表绘制了两个值，您的查询和蓝图路由将正常运行.现在有一个关于直通表所需主键值的小说明，但这只是架构构建器中的一个错误，应该很快解决.

You can't add additional attributes to the through table but if you map out the two values for the join table your queries and blueprint routes will function like normal. There is a small note about the primary key values needed on the through table for now but that is just a bug in the schema builder that should be resolved soon.

以下逻辑当前未记录在案，但可以帮助您获得所需的结果.

The following logic isn't currently documented but will work to get you the results you need.

注意，但是它仍处于测试版发布状态，所以它还不会非常稳定.我们没有在 mysql 适配器上进行正确的外连接调用，因此您将看到进行了三个查询，然后结果将在应用程序层的内存中连接.一旦标准解析器更新，这将被清理以执行单个 sql 查询，就像您期望的那样.

Note however this is still in a beta release state so it's not going to be rock solid quite yet. We don't have the correct outer join calls being made on the mysql adapter so you will see three queries being made and then the results will be joined in memory at the application layer. This will be cleaned up to do a single sql query like you would expect once the criteria parser has been updated.

此外，每当您处理现有数据时，请确保您具有 migrate: safe 标志，就像您在下面指定的那样，这样不会对当前数据库应用任何更改.

Also whenever you are working with existing data be sure you have the migrate: safe flag on like you specified below so no changes will be applied to the current database.

// Role.js
module.exports = {
  identity      : 'Role',
  tableName     : 'role_master',
  migrate       : 'safe',
  schema        : true,
  autoPK        : false,
  autoCreatedAt : false,
  autoUpdatedAt : false,

  attributes: {

    id : {
      columnName : 'rolefk',
      type       : 'string',
      required   : true,
      primaryKey : true,
      unique     : true,
      uuidv4     : true
    },

    // A Role can have many permissions through the roleperm model
    permissions : {
      collection : 'permission',
      through: 'roleperm'
    }

  }
};

// Permission.js
module.exports = {
  identity      : 'Permission',
  tableName     : 'perm_master',
  migrate       : 'safe',
  schema        : true,
  autoPK        : false,
  autoCreatedAt : false,
  autoUpdatedAt : false,

  attributes : {

    id : {
      columnName : 'permfk',
      type       : 'string',
      required   : true,
      primaryKey : true,
      unique     : true,
      uuidv4     : true
    },

    // A Permission can belong to many roles using the roleperm model
    roles : {
      collection: 'role',
      through: 'roleperm'
    }

  }

};

// RolePerm.js
module.exports = {
  identity      : 'RolePerm',
  tableName     : 'role_perm',
  migrate       : 'safe',
  schema        : true,
  autoPK        : false,
  autoCreatedAt : false,
  autoUpdatedAt : false,

  attributes : {

    // Due to a bug in the schema generator this seems to be needed at the
    // model level but not on the actual database.
    id: {
      type: 'integer',
      primaryKey: true
    },

    roleid : {
      model: 'role',
      columnName: 'role_id'
    },

    permid : {
      model: 'permission',
      columnName: 'perm_id'
    }

  }

};

这篇关于使用现有连接表的一对多关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！