Sails.js + Waterline:通过关联进行多对多 [英] Sails.js + Waterline: Many-to-Many through association

问题描述

我是Sails.js(v0.10.5)和Waterline ORM的新手.我在数据库中有3个表:用户(标识，名称)，角色(标识，别名)和联接表users_roles(用户标识，角色标识).重要的是不要更改数据库中的表名和字段名.我希望策略实体成为用户和角色之间的联接实体.这是一些映射代码:

I'm new to Sails.js (v0.10.5) and Waterline ORM. I have 3 tables in database: users (id, name), roles(id, alias) and join table users_roles(user_id, role_id). It's important not to change table names and field names in database. I want Policy entity to be a join entity between User and Role. Here is some mapping 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 trying to access roles atribute in controller

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

这将返回

[]

还有

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

返回

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

我两次检查数据库中是否存在用户，角色和它们之间的关联.我怎么了

I checked twice that user, role and association between them exists in database. What is my mistake?

推荐答案

我已经找到解决此问题的方法.这不是我真正想要的，但是可以. 首先:加入实体:

I have found way to solve this problem. It's not what I exactly 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;
        });
    }
}

如您所见，我向该实体(以及数据库表users_roles)添加了ID字段，所以这不是一个很好的解决方案.

As you see, I added ID field to this entity (and to db table users_roles too), so it's not the 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'
        }
    }
}

和角色实体:

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

这就是我获取指定user_id的所有角色的方式:

That's how I get all roles for 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');
    }           
});
...

也许对某人有用=)

这篇关于Sails.js + Waterline:通过关联进行多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！