Sequelize关联模型Where查询时subQuery与分页冲突的解决方法咨询

我使用如下where条件进行查询：

where = {
  [Op.or]: [
    {
      name: {
        [Op.like]: `%${word}%`,
      },
    },
    {
      description: {
        [Op.like]: `%${word}%`,
      },
    },
    {
      '$types.name$': {
        [Op.like]: `%${word}%`,
      },
    },
  ],
};

并通过以下方法获取包含Type和Feature模型的分页products数据：

const products = await this.productModel.findAndCountAll({
  subQuery: false,
  limit,
  offset,
  distinct: true,
  order: [['name', 'ASC']],
  where,
  include: [
    {
      model: Type,
      include: [
        {
          model: Feature,
        },
      ],
    },
  ],
});

为使$types.name$查询生效必须设置subQuery: false，但该配置与limit、offset配合时无法获取完整数据。使用的数据库方言为MariaDB，Product和Type模型定义如下：

Product模型：

@Table({ tableName: 'products' })
export class Product extends Model<Product, ProductCreationAttrs> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;

  @Column({ type: DataType.STRING, unique: true, allowNull: false })
  name: string;

  @Column({ type: DataType.STRING, allowNull: false })
  description: string;

  @HasMany(() => Type, { foreignKey: 'productId' })
  types: Type[];
}

Type模型：

@Table({ tableName: 'types' })
export class Type extends Model<Type, TypeCreationAttrs> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;

  @Column({ type: DataType.STRING, allowNull: false })
  name: string;

  @BelongsTo(() => Product, { foreignKey: 'productId' })
  product: Product;

  @BelongsToMany(() => Feature, () => TypeFeatures)
  features: Feature[];
}

请问有什么办法可以解决这个问题？

方法一：先筛选符合条件的Product ID，再分页关联查询 #

先通过子查询获取所有匹配条件的Product ID集合，再基于这些ID执行分页和关联查询，规避subQuery: false导致的分页异常：

// 第一步：获取所有符合条件的Product ID
const matchedProductIds = await this.productModel.findAll({
  attributes: ['id'],
  subQuery: false,
  distinct: true,
  where,
  include: [{ model: Type }]
}).then(list => list.map(item => item.id));

// 第二步：基于ID分页查询，同时关联Type和Feature
const products = await this.productModel.findAndCountAll({
  limit,
  offset,
  distinct: true,
  order: [['name', 'ASC']],
  where: { id: matchedProductIds },
  include: [
    {
      model: Type,
      include: [{ model: Feature }]
    }
  ]
});

若匹配的Product数量极大，可改用嵌套子查询避免生成过长的ID数组：

const products = await this.productModel.findAndCountAll({
  limit,
  offset,
  distinct: true,
  order: [['name', 'ASC']],
  where: {
    id: {
      [Op.in]: this.productModel.findAll({
        attributes: ['id'],
        subQuery: false,
        distinct: true,
        where,
        include: [{ model: Type }]
      })
    }
  },
  include: [
    {
      model: Type,
      include: [{ model: Feature }]
    }
  ]
});

方法二：用Op.exists替代关联字段查询 #

将$types.name$的查询逻辑改为Op.exists子查询，这样可以开启subQuery: true，保证分页功能正常：

where = {
  [Op.or]: [
    { name: { [Op.like]: `%${word}%` } },
    { description: { [Op.like]: `%${word}%` } },
    {
      [Op.exists]: this.typeModel.findAll({
        attributes: [],
        where: {
          productId: { [Op.col]: 'product.id' },
          name: { [Op.like]: `%${word}%` }
        }
      })
    }
  ]
};

// 开启subQuery: true，分页正常工作
const products = await this.productModel.findAndCountAll({
  subQuery: true,
  limit,
  offset,
  distinct: true,
  order: [['name', 'ASC']],
  where,
  include: [
    {
      model: Type,
      include: [{ model: Feature }]
    }
  ]
});

核心是把关联表的查询逻辑移到exists子查询中，避免主查询因关联产生重复行干扰分页计数。

方法三：编写原生SQL查询 #

如果ORM方法无法满足需求，直接用原生SQL精确控制查询逻辑：

// 查询数据的SQL
const dataSql = `
  SELECT DISTINCT p.*, t.*, f.*
  FROM products p
  LEFT JOIN types t ON p.id = t.productId
  LEFT JOIN type_features tf ON t.id = tf.typeId
  LEFT JOIN features f ON tf.featureId = f.id
  WHERE p.name LIKE ? 
     OR p.description LIKE ? 
     OR t.name LIKE ?
  ORDER BY p.name ASC
  LIMIT ? OFFSET ?
`;

// 查询总条数的SQL
const countSql = `
  SELECT COUNT(DISTINCT p.id) AS total
  FROM products p
  LEFT JOIN types t ON p.id = t.productId
  WHERE p.name LIKE ? 
     OR p.description LIKE ? 
     OR t.name LIKE ?
`;

// 执行数据查询
const productRows = await this.productModel.sequelize.query(dataSql, {
  replacements: [`%${word}%`, `%${word}%`, `%${word}%`, limit, offset],
  model: Product,
  include: [{ model: Type, include: [{ model: Feature }] }]
});

// 执行计数查询
const countResult = await this.productModel.sequelize.query(countSql, {
  replacements: [`%${word}%`, `%${word}%`, `%${word}%`],
  type: this.productModel.sequelize.QueryTypes.SELECT
});

const finalResult = {
  rows: productRows,
  count: countResult[0].total
};

这种方式完全自定义查询逻辑，能彻底解决分页与关联查询的冲突，但需要维护原生SQL，灵活性高但可维护性稍低。

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