Sequelize 关联查询详解

有两个表，一个是主表，一个是子表，需要进行关联查询

const User = sequelize.define(
  'User',
  { name: DataTypes.STRING },
  { timestamps: false }
);

const UserInfo = sequelize.define(
  'UserInfo',
  { userID: DataTypes.STRING },
  { timestamps: false }
);

// 表示通过子表的 userID 关联主表 中的 id，关系为一对多
User.hasMany(UserInfo, {
  foreignKey: 'userID',
  sourceKey: 'id',
});


// 联表查询子表中的数据
await PostModel.findAll({
  group: ['posts.id'],
  order: [['createdAt', 'DESC']],
  include: [
    {
      model: CategoryModel,
      attributes: ['title'],
      where: { title: categoryTitle }
    },
    { model: CommentModel },
    { model: UserModel, attributes: ['fullname', 'id'] }
  ],
  attributes: [
    'title', 'content', 'description', 'thumbnail', 'baner', 'createdAt', 'updatedAt',
    [Sequelize.fn('COUNT', 'comment.id'), 'commentsCounter']
  ]
});

参考文章

• https://www.jianshu.com/p/bf5399fd1b69
• https://sequelize.org/docs/v6/core-concepts/assocs/
• https://stackoverflow.com/questions/52496842/sequelize-hasmany-associatedmodel-count-in-attributes-in-query-execution
• https://stackoverflow.com/questions/47924465/sequelize-hasmany-with-where