Sails.js + Waterline: many-to-many through association

Question

I am new to Sails.js (v0.10.5) and Waterline ORM. I have 3 tables in the database: users (id, name), roles (id, alias) and join table users_roles (user_id, role_id). It is important not to change the table names and field names in the database. I want the policy object to be a unified object between the user and the role. Here is the display code:

//User.js module.exports = { tableName: 'users', autoCreatedAt: false, autoUpdatedAt: false, attributes: { id: { type: 'integer', required: true }, name: { type: 'string' }, roles: { collection: 'role', via: 'users', through: 'policy' }, } } //Role.js module.exports = { tableName: "roles", autoCreatedAt: false, autoUpdatedAt: false, attributes: { id: { type: 'integer', required: true }, alias: { type: 'string', required: true }, users: { collection: 'user', via: 'roles', through: 'policy' } } } //Policy.js module.exports = { tableName: "users_roles", tables: ['users', 'roles'], junctionTable: true, autoCreatedAt: false, autoUpdatedAt: false, attributes: { user: { columnName: 'user', type: 'integer', foreignKey: true, references: 'user', on: 'id', via: 'role', groupBy: 'user' }, roles: { columnName: 'role', type: 'integer', foreignKey: true, references: 'role', on: 'id', via: 'user', groupBy: 'role' } } } 

But when I try to access attribute roles in the controller

 User.findOne({id: 1}).populate('roles').exec(function(err, user) { console.log(JSON.stringify(user.roles)); }); 

it returns

 [] 

AND

 User.findOne({id: 1}).populate('roles').exec(function(err, user) { console.log(JSON.stringify(user)); }); 

returns

 {"id":1,"name":"test", "roles":[]} 

I double-checked that the user, role and the relationship between them exist in the database. What is my mistake?

Answer 1

I found a way to solve this problem. This is not what I definitely want, but it works. First: join entity:

 //Policy.js module.exports = { tableName: "users_roles", autoPK: false, attributes: { id: { type: 'integer', primaryKey: true, autoIncrement: true, }, user: { columnName: 'user_id', model: 'user' }, role: { columnName: 'role_id', model: 'role' } }, //tricky method to get all users for specified role_id //or to get all roles for specified user_id get: function(id, modificator, cb) { var fields = ['user', 'role']; if (fields.indexOf(modificator) < 0) { cb(new Error('No such modificator in Policy.get()'), null); } var inversedField = fields[(fields.indexOf(modificator) + 1) % 2]; var condition = {}; condition[inversedField] = id; this.find(condition).populate(modificator).exec(function(err, policies) { if (err) { cb(err, null); return; } var result = []; policies.forEach(function(policy) { result.push(policy[modificator]); }); cb(null, result); return; }); } } 

As you can see, I added an identifier field to this object (and to db table users_roles too), so this is not a great solution.

 //User.js module.exports = { tableName: 'users', autoPK: false, attributes: { id: { type: 'integer', primaryKey: true, autoIncrement: true, unique: true, }, name: { type: 'string' }, policies: { collection: 'policy', via: 'user' } } } 

And the role:

 //Role.js module.exports = { tableName: 'roles', autoPK: false, attributes: { id: { type: 'integer', primaryKey: true, autoIncrement: true, }, alias: { type: 'string', required: true, unique: true, }, policies: { collection: 'policy', via: 'role' } } } 

The way I get all the roles for the specified user_id:

 ... id = req.session.me.id; //user_id here Policy.get(id, 'role', function(err, roles) { var isAdmin = false; roles.forEach(function(role) { isAdmin |= (role.id === 1); }); if (isAdmin) { next(null); return; } else { return res.redirect('/login'); } }); ... 

Maybe it will be useful for someone =)