One to many associations using existing join table

Question

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

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?

Answer 1

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.

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.

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'
    }

  }

};