Multi foreign key on hasMany association Sequelize

Question

In sequelize, if I have an association like :

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

Sequelize will automatically add UserId column to Article table. But now I want to add more like UserName and Email to Article so when I query an article I have author's name and I do not need to query again by UserId.

How can I do that ? I tried

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

but it only UserId appear in Article table.

Here is my User model:

"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;
};

This is not going to work. The foreign key must map to one User and you could have a name that maps to many users. You basically want to copy over the names and emails from the user table to the article table. This is not a good practice. — Maria Ines Parnisari

Hemant Kumar Hemant Kumar · Accepted Answer · 2017-09-17T14:49:44

You don't need to run 2 Queries to get user information. Just add a belongsTo relation from Article to User

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

and query the Articles like this

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

you will have the result in the following format:

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

On a separate note, your User model has multiple primary keys, which may not work as expected. If you want to use a composite primary key then there is a separate approach for that.

Multi foreign key on hasMany association Sequelize

1 Answers