Sequelize使用findAndCountAll、belongsTo、hasMany、include、count、sum、group进行多表关联统计查询问题小结

场景回顾

数据库：Mysql5

Sequelize版本：5.21.5

我们有两个表，分别为 用户表（user）和文章表（article），建立Sequelize模型如下

User.js

module.exports = function (sequelize, DataTypes) {
  return sequelize.define('user', {
    id: { // 编号
      type: DataTypes.BIGINT,
      primaryKey: true,
      autoIncrement: true
    },
    name: { // 昵称
      type: DataTypes.STRING(32),
      defaultValue: ''
    },
    avatar: { // 头像
      type: DataTypes.STRING(1000),
      defaultValue: ''
    },
  });
};

Article.js

module.exports = function (sequelize, DataTypes) {
  return sequelize.define('article', {
    id: { // 编号
      type: DataTypes.BIGINT,
      primaryKey: true,
      autoIncrement: true
    },
    title: { // 标题
      type: DataTypes.STRING(32),
      defaultValue: ''
    },
    user_id: { // 玩家ID
      type: DataTypes.STRING(1000),
      defaultValue: '',
      references: {
        model: 'User',
        key: 'id'
      },
    },
  });
};

模型关系如下：

association.js

User.hasMany(Article, {foreignKey: 'user_id', as:'article'});
Article.belongsTo(User, {foreignKey: 'user_id'});

我们使用findAndCountAll进行分页查询User列表，如下：

const page = 1, count = 20;
const { count, rows } = await User.findAndCountAll({
      offset: (page - 1) * count,
      limit: count,
      where: filters,
    });

生成对应的SQL语句如下

SELECT `id`,`name`, `avatar` FROM `user` AS `user` LIMIT 0, 20;

如果希望在用户列表显示该用户的文章数量，我们使用include进行关联查询，如下：

const page = 1, count = 20;
const { count, rows } = await User.findAndCountAll({
      offset: (page - 1) * count,
      limit: count,
      attributes:['id', 'name', 'avatar', , [sequelize.fn('COUNT', sequelize.col('article.id')), 'article.count']]
      include:[{
        model: Article,
        as: "article",
        attributes: []
      }],
      group: ['user.id']
    });

生成SQL语句如下

SELECT `user`.* 
  FROM (SELECT `user`.`name`, `user`.`avatar`, COUNT(`article`.`id`) AS `article.count` 
   FROM `user` AS `user` WHERE GROUP BY `user`.`id` LIMIT 0, 20) AS `user` 
 LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id`;

并且报错：Unknown column 'article.id' in 'field list'

加入没有进行分页查询，如下：

const page = 1, count = 20;
const { count, rows } = await User.findAndCountAll({
      attributes:['id', 'name', 'avatar', , [sequelize.fn('COUNT', sequelize.col('article.id')), 'article.count']]
      include:[{
        model: Article,
        as: "article",
        attributes: []
      }],
      group: ['user.id']
    });

生成的SQL语句

SELECT `user`.`name`, `user`.`avatar`, COUNT(`article`.`id`) AS `article.count`
 FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id`
 GROUP BY `user`.`id`;

这正是我们想要的，问题就在于分页查询。

其实我们只需要在include里面设置一下duplicating属性为false就可以了，如下

const page = 1, count = 20;
const { count, rows } = await User.findAndCountAll({
      offset: (page - 1) * count,
      limit: count,
      attributes:['id', 'name', 'avatar', , [sequelize.fn('COUNT', sequelize.col('article.id')), 'article.count']]
      include:[{
        model: Article,
        as: "article",
        duplicating:false,
        attributes: []
      }],
      group: ['user.id']
    });

得到的SQL语句

SELECT `user`.`name`, `user`.`avatar`, COUNT(`article`.`id`) AS `article.count`
 FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id`
 GROUP BY `user`.`id` LIMIT 0, 20;

大功告成！