How to `include` two separate references of same model using Sequelize ORM?

Question

I'm building a website using the MEAN stack but replacing MongoDB with PostGRES and, as a result, using Sequelize ORM.

I have two models -- User and AudioConfig. A User can have many AudioConfig and an AudioConfig belongs to a User by the createdBy and updatedBy.

Here's how my association looks like using Sequelize

models.User.hasMany(models.AudioConfig, {
  foreignKey: {
    name: 'createdBy',
    allowNull: false
  }
});
models.User.hasMany(models.AudioConfig, {
  foreignKey: {
    name: 'updatedBy'
  }
});
models.AudioConfig.belongsTo(models.User, {
  foreignKey: {
    name: 'createdBy',
    as: 'createdBy',
    allowNull: false
  }
});
models.AudioConfig.belongsTo(models.User, {
  foreignKey: {
    name: 'updatedBy',
    as: 'updatedBy'
  }
});

In my findAll query for AudioConfig, I have tried several variations from what I've found online but none appear to work as I expect:

var Db = require('../../models');
var entityModel = Db.AudioConfig;

exports.index = function (req, res) {
  entityModel.findAll({
      include: {all:true}
    })
    .then(function (entities) {
      return res.status(200).json(entities);
    })
    .catch(function (err) {
      return handleError(res, err);
    })
};

// And I've tried this...
exports.index = function (req, res) {
  entityModel.findAll({
      include: [
        {model: Db.User, as: 'createdBy'},
        {model: Db.User, as: 'updatedBy'}
      ]
    })
    .then(function (entities) {
      return res.status(200).json(entities);
    })
    .catch(function (err) {
      return handleError(res, err);
    })
};

// And this too...
exports.index = function (req, res) {
  entityModel.findAll({
      include: [
        {
          model: Db.User
        }
      ]
    })
    .then(function (entities) {
      return res.status(200).json(entities);
    })
    .catch(function (err) {
      return handleError(res, err);
    })
};

Now, in my database, I have a single record of AudioConfig that has two different User references -- one for createdBy and another for updatedBy. But when I do a query for AudioConfig, I only get the User record back for the updatedBy field.

[
  {
    "id": "e3011e31-b907-47ad-99f3-61016283a523",
    "sampleRate": 16000,
    "format": "WAV",
    "channel": 2,
    "bitRate": 16,
    "createdAt": "2016-05-01T16:30:11.847Z",
    "updatedAt": "2016-05-01T16:30:11.847Z",
    "createdBy": "1375263f-a3f0-4eef-800f-99b28fdce9d8",
    "updatedBy": "5bb8cac0-b916-4000-81fe-9b1f8f597847",
    "User": {
      "id": "5bb8cac0-b916-4000-81fe-9b1f8f597847",
      "email": "[email protected]",
      "firstName": "John",
      "lastName": "Doe",
      "resetPasswordToken": null,
      "resetPasswordTokenExpiresOn": null,
      "createdAt": "2016-05-01T16:30:11.816Z",
      "updatedAt": "2016-05-01T16:30:11.816Z",
      "roleId": "10ae3879-9f9f-4370-aa47-3677c492afd8"
    }
  }
]

How do I get it so that createdBy value of UUID is replaced with the User object associated with it? And same for the updatedBy field?

I'm somewhat expecting the same behavior as with MongoDB and Mongoose's populate

Have you tried defining the 'as' property in the foreign key object on both ends of the relationship? I see you only have it on the belongsTo but not hasMany. In their documentation it is set on both ends. — grimurd
@GrimurD, I tried what you suggested but I got the same result back. — FilmiHero
Try changing the names in the 'as' so the names dont conflict with the fields on the models. For example createdByUser and updatedByUser — grimurd

grimurd grimurd · Accepted Answer · 2016-05-01T17:23:54

I finally realized what the problem is, you have the as property set on the foreign key object. This should hopefully solve it, unfortunately I can't test it myself at the moment.

models.User.hasMany(models.AudioConfig, {
  as: 'createdByUser',
  foreignKey: {
    name: 'createdBy',
    allowNull: false
  }
});
models.User.hasMany(models.AudioConfig, {
  as: 'updatedByUser',
  foreignKey: {
    name: 'updatedBy'
  }
});
models.AudioConfig.belongsTo(models.User, {
  as: 'createdByUser',
  foreignKey: {
    name: 'createdBy',
    allowNull: false
  }
});
models.AudioConfig.belongsTo(models.User, {
  as: 'updatedByUser'
  foreignKey: {
    name: 'updatedBy'
  }
});

How to `include` two separate references of same model using Sequelize ORM?

1 Answers