mysql - Sequelize 获取属性中的关联模型数据

Question

我有两个模型，User 和 Category

用户模型:

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    id: {
      type: DataTypes.BIGINT(19),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    firstname: {
      type: DataTypes.STRING(150),
      allowNull: false
    },
    lastname: {
      type: DataTypes.STRING(150),
      allowNull: false
    },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true
    },
    password: {
      type: DataTypes.STRING(255),
      allowNull: false
    }
  }, {
    tableName: 'users',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    defaultScope: {
      attributes: {
        exclude: ['password']
      }
    },
    scopes: {
      withPassword: {
        attributes: {}
      }
    }
  })

  User.prototype.toJSON = function () {
    let values = Object.assign({}, this.get())
    delete values.password
    return values
  }

  return User
}

类别模型:

module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define('Category', {
    id: {
      type: DataTypes.BIGINT(19),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING(150),
      allowNull: false
    },
    created_by: {
      type: DataTypes.BIGINT(19),
      allowNull: false,
      references: {
        model: 'users',
        key: 'id'
      }
    }
  }, {
    tableName: 'categories',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  })

  Category.associate = (models) => {
    Category.belongsTo(models.User, {
      foreignKey: 'created_by'
    })
  }

  return Category
}

我查找类别的查询:

const category = await Category.findOne({
  include: [{
    model: User,
    required: true,
    attributes: ['id', 'firstname', 'lastname', 'email']
  }],
  where: {
    id: req.params.id
  },
  limit: 1
})

当我执行查询时，我期望的响应是，

{
    "id": 1,
    "name": "License",
    "created_by": {
        "id": 1,
        "firstname": "Magesh",
        "lastname": "Kumaar",
        "email": "mk@test.com"
    },
    "created_at": "2018-09-03T07:41:29.000Z",
    "updated_at": "2018-09-03T07:41:29.000Z"
}

但我得到的回应是，

{
    "id": 1,
    "name": "License",
    "created_by": 1, // I expected the user information here
    "created_at": "2018-09-03T07:41:29.000Z",
    "updated_at": "2018-09-03T07:41:29.000Z",
    "User": {
        "id": 1,
        "firstname": "Magesh",
        "lastname": "Kumaar",
        "email": "mk@test.com"
    }
}

用户信息在 created_by 作为 Id 和另一个包含其他信息的 User 属性中可用

我尝试了 as 选项，但它似乎不起作用。有什么方法可以在 created_by 键本身中获取用户信息。

Answer 1

需要使用别名。 http://docs.sequelizejs.com/manual/tutorial/associations.html

示例:

索引.js

require('dotenv').load();

const { User, Category } = require('./models');
const sequelize = require('./database');

(async () => {
    await sequelize.sync({ force: true });
await Category.associate({ User });

await User.create({
    firstname: 'firstname',
    lastname: 'lastname',
    email: 'email@email.email',
    password: 'password',
});

await Category.create({
    name: 'name',
    created_by: '1',
});

const category = await Category.findOne({
    attributes: ['id'],
    include: {
        model: User,
        required: true,
        as: 'created',
        attributes: ['id', 'firstname', 'lastname', 'email']
    },
    where: {
        id: 1,
    },
});

console.log(category.toJSON());
})();

类别模型文件:

module.exports = (sequelize, DataTypes) => {
const Category = sequelize.define('Category', {
    id: {
        type: DataTypes.BIGINT(19),
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING(150),
        allowNull: false
    },
    created_by: {
        type: DataTypes.BIGINT(19),
        allowNull: false,
        references: {
            model: 'users',
            key: 'id'
        }
    }
}, {
    tableName: 'categories',
    timestamps: true,
    createdAt: false,
    updatedAt: false,
});

Category.associate = (models) => {
    Category.belongsTo(models.User, {
        foreignKey: 'created_by',
        as: 'created',
    })
};

return Category;
};

结果:

{ id: 1,
  created: 
   { id: 1,
     firstname: 'firstname',
     lastname: 'lastname',
     email: 'email@email.email' } }