Sequelize中belongsToMany关联下findAndCountAll查询去重问题

我有两个通过belongsToMany关联的模型Product和Category，尝试使用[Op.in]运算符筛选属于指定分类的产品，findAndCountAll返回的count值正确，但rows中存在重复数据。

我的代码实现 #

async findAll(page, perPage, by, direction, searchWord, categories) {
     let options = {
        where: { published: true },
        limit: perPage,
        order: [[by, direction]],
        offset: (page - 1) * perPage,
        raw: true,
        distinct: true,
     };
    // Category include
      if (categories) {
        if (typeof categories === "string") categories = Array(categories);
        categories = categories.map((cat) => parseInt(cat));
        
        options.include = {
          model: CategoryModel,
          where: { id: { [Op.in]: categories } },
          through: { attributes: [] },
        };
      }
      result = await ProductModel.findAndCountAll(options);
      console.log(result);
    } else {
      result = await ProductModel.findAll({ where: { published: true } });
    }
    return result;

返回结果示例 #

{
  count: 3,
  rows: [
    {
      id: 8,
      name: 'Tulejka',
      'Categories.id': 10,
      'Categories.name': 'Koła'
    },
    {
      id: 10,
      name: 'Flansza EGR',
      'Categories.id': 9,
      'Categories.name': 'Wyciągarki'
    },
    {
      id: 13,
      name: 'Szybka',
      'Categories.id': 9,
      'Categories.name': 'Wyciągarki'
    },
    {
      id: 13,
      name: 'Szybka',
      'Categories.id': 10,
      'Categories.name': 'Koła'
    }
  ]
}

我已经尝试过separate、distinct、Sequelize.literal[]等配置，目前的替代方案是对结果手动过滤：

result.rows = result.rows.filter(
    (row, index, self) => index === self.findIndex((r) => r.id === row.id)
  );

但希望通过配置findAndCountAll的参数一次性解决该问题。

解决方案 #

问题根源在于raw: true与关联查询的冲突：开启raw后，Sequelize直接返回SQL JOIN后的原始结果，同一个产品对应多个分类时会生成多条记录，此时distinct: true无法自动处理重复。

方案1：关闭raw: true（推荐） #

使用Sequelize的实例对象，distinct: true会自动生效去重，若需要原始数据格式，可后续转换：

async findAll(page, perPage, by, direction, searchWord, categories) {
     let options = {
        where: { published: true },
        limit: perPage,
        order: [[by, direction]],
        offset: (page - 1) * perPage,
        distinct: true, // 保留distinct
        // 移除raw: true
     };
    // Category include
      if (categories) {
        if (typeof categories === "string") categories = Array(categories);
        categories = categories.map((cat) => parseInt(cat));
        
        options.include = {
          model: CategoryModel,
          where: { id: { [Op.in]: categories } },
          through: { attributes: [] },
        };
      }
      const result = await ProductModel.findAndCountAll(options);
      // 转换为原始数据格式
      result.rows = result.rows.map(row => row.get({ plain: true }));
      console.log(result);
    } else {
      const result = await ProductModel.findAll({ where: { published: true } });
    }
    return result;

方案2：保留raw: true，通过SQL分组去重 #

通过group子句按产品ID分组，同时明确指定去重字段：

// 引入Sequelize
const { Sequelize } = require('sequelize');

async findAll(page, perPage, by, direction, searchWord, categories) {
     let options = {
        where: { published: true },
        limit: perPage,
        order: [[by, direction]],
        offset: (page - 1) * perPage,
        raw: true,
        distinct: true,
        attributes: {
          // 明确指定按产品ID去重
          include: [[Sequelize.literal('DISTINCT "Product"."id"'), 'id']]
        },
        // 按产品ID分组，确保同一产品只返回一条
        group: ['Product.id']
     };
    // Category include
      if (categories) {
        if (typeof categories === "string") categories = Array(categories);
        categories = categories.map((cat) => parseInt(cat));
        
        options.include = {
          model: CategoryModel,
          where: { id: { [Op.in]: categories } },
          through: { attributes: [] },
        };
      }
      result = await ProductModel.findAndCountAll(options);
      console.log(result);
    } else {
      result = await ProductModel.findAll({ where: { published: true } });
    }
    return result;

注意：使用group时，order中的排序字段必须包含在group中或为聚合函数，否则可能触发SQL语法错误，需根据实际排序需求调整group配置。

内容的提问来源于stack exchange，提问作者Juzioikasztanka