首页 文章

hasMany上的多个外键关联Sequelize

提问于
浏览
0

在sequelize中,如果我有一个像这样的联想:

User.hasMany(models.Article, { onDelete: 'cascade', hooks: true});

Sequelize会自动将 UserId 列添加到 Article 表 . 但现在我想添加更多像 UserNameEmailArticle 所以当我查询文章时我有作者的名字,我不需要再次通过UserId查询 .

我怎样才能做到这一点 ?我试过了

User.hasMany(models.Article, { onDelete: 'cascade', hooks: true, foreignKey: {'UserId': 'id', 'UserName': 'name' } });

但只有 UserId 出现在 Article 表中 .

这是我的用户模型:

"use strict";
var bcrypt = require('bcryptjs');

module.exports = function (sequelize, DataTypes) {
  var User = sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING,
      primaryKey: true
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      primaryKey: true
    },
    role: {
      type: DataTypes.STRING,
      allowNull: false
    },
    activeToken: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    status: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      allowNull: false
    },
    avatar: {
      type: DataTypes.STRING,
      allowNull: true
    },
    phone: {
      type: DataTypes.STRING,
      allowNull: true
    }
  }, {
      instanceMethods: {
        updatePassword: function (newPass, callback) {
          var self = this;
          bcrypt.genSalt(10, function (err, salt) {
            bcrypt.hash(newPass, salt, function (err, hashed) {
              self.update({
                password: hashed
              }).then(callback);
            });
          });
        },
        updateStatus: function (status, callback) {
          this.update({
            status: status
          }).then(callback);
        },
        comparePassword: function (password, callback) {
          bcrypt.compare(password, this.password, function (err, isMatch) {
            if (err) {
              throw err;
            }
            callback(isMatch);
          });
        },
        updateRole: function (newRole, callback) {
          this.update({
            role: newRole
          }).then(callback);
        }
      },
      classMethods: {
        createUser: function (newUser, callback) {
          bcrypt.genSalt(10, function (err, salt) {
            bcrypt.hash(newUser.password, salt, function (err, hash) {
              newUser.password = hash;
              User.create(newUser).then(callback);
            });
          });
        },
        getUserById: function (id, callback) {
          var query = {
            where: {
              id: id
            }
          }
          User.findOne(query).then(callback);
        },
        getUserByUsername: function (username, callback) {
          var query = {
            where: {
              username: username
            }
          };
          User.findOne(query).then(callback);
        },
        getUserByEmail: function (email, callback) {
          var query = {
            where: {
              email: email
            }
          };
          User.findOne(query).then(callback);
        },
        getAllUser: function (callback) {
          User.findAll().then(callback);
        },
        deleteUser: function (userId, callback) {
          var query = {
            where: {
              id: userId
            }
          };

          User.destroy(query).then(callback);
        },
        associate: function (models) {
          User.hasMany(models.Article, { onDelete: 'cascade', hooks: true, onUpdate: 'cascade', foreignKey: {'UserId': 'id', 'UserName': 'name' } });
          User.hasMany(models.Comment, { onDelete: 'cascade', hooks: true, onUpdate: 'cascade' });
          User.hasMany(models.Device, { onDelete: 'cascade', hooks: true, onUpdate: 'cascade' });
        }
      },
      tableName: 'User'
    });
  return User;
};

1 回答

  • 1

    您无需运行2个查询来获取用户信息 . 只需从 Article 添加 belongsTo 关系到 User

    User.hasMany(models.Article, { onDelete: 'cascade', hooks: true});
    Article.belongsTo(models.User);
    

    并查询这样的文章

    Article.findAll({ where: <condition>, include: {
        model: User,
        attributes: ['name', 'email']
    } });
    

    您将得到以下格式的结果:

    [.....
       {
           articleId: 23,
           articleTitle: "<Some String>",
           User: {
               name: "User Name",
               email: "User Email"
           }
       }
       ........
    ]
    

    另外, User 模型有多个主键,可能无法正常工作 . 如果要使用复合主键,则可以采用单独的方法 .

相关问题