如何用Sequelize实现多标签过滤的复杂自关联查询？

需要实现Resource与ContentTag多对多关联的复杂查询，目标是获取同时包含标签A、B、C的所有资源。对应的SQL示例如下：

SELECT * FROM Resources-Ctag
NATURAL JOIN (SELECT * FROM Resource-Ctag WHERE ContentTag.keyword="A") as ResourceWithTagA 
NATURAL JOIN (SELECT * FROM Resource-Ctag WHERE ContentTag.keyword="B") as ResourceWithTagB
WHERE  ContentTag.keyword="C"

其中Resources-Ctag是Resource和ContentTag的中间关联表。

现有模型代码 #

Resource模型 #

import { ContentTag } from "./content_tag";

export class Resource extends Model {
  // Default incremental ID
  public id!: number;

  // Model attributes
  public name!: string;

  public description!: string;

  public format!: string;

  public path!: string;

  public metadata!: any;
  public typologyTagId!: string;

  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;

  // Associations
  public addContentTag!: HasManyAddAssociationMixin<ContentTag, number>;
  public getContentTags!: HasManyGetAssociationsMixin<ContentTag>;
  public setContentTags!: HasManySetAssociationsMixin<ContentTag, number>;

  public static initialize(sequelize) {
    this.init(
      {
        id: {
          primaryKey: true,
          type: DataTypes.UUID,
          defaultValue: DataTypes.UUIDV4,
        },
        name: {
          type: DataTypes.STRING,
          allowNull: false,
          unique: false,
        },
        description: {
          type: DataTypes.STRING,
          allowNull: true,
          unique: false,
        },
        format: {
          type: DataTypes.STRING,
          allowNull: false,
          unique: false,
        },
        path: {
          type: DataTypes.STRING,
          allowNull: false,
          unique: false,
        },
        metadata: {
          type: DataTypes.JSON,
          allowNull: true,
          unique: false,
        },
      },
      {
        sequelize,
        timestamps: true,
        paranoid: true,
        tableName: "resource",
      }
    );
  }
}

ContentTag模型 #

export class ContentTag extends Model {
  // Default incremental ID
  public id!: string;

  // Model attributes
  public keyword!: string;

  public static initialize(sequelize) {
    this.init(
      {
        id: {
          primaryKey: true,
          type: DataTypes.UUID,
          defaultValue: DataTypes.UUIDV4,
        },
        keyword: {
          type: DataTypes.STRING,
          allowNull: false,
          unique: false,
        },
      },
      {
        sequelize,
        timestamps: true,
        createdAt: false,
        updatedAt: false,
        tableName: "content_tag",
      }
    );
  }
}

尝试过的无效代码 #

router.get("/", async (req: any, res: Response) => {
  try {

    const { typologyTag, timeFrom, timeTo, author } = req.query;
    const cTags = JSON.parse(req.query.contentTags)
    const where = {};
    const include = [
      {
        model: TypologyTag,
        as: "typologyTag",
      },
      {
        model: User,
        as: "author",
      },
      {
        model: ContentTag,
        as: "contentTags",
      },
    ];

    if (typologyTag) {
      include[0]["where"] = { keyword: { [Op.eq]: typologyTag } };
    };
    if (author) {
      include[1]["where"] = { displayName: { [Op.eq]: author } };
    };
    if (timeFrom && timeTo) {
      where["createdAt"] = {
        [Op.lt]: timeTo,
        [Op.gt]: new Date(timeFrom)
      };
    } else if (timeFrom) {
      where["createdAt"] = {
        [Op.gt]: new Date(timeFrom)
      };
    } else if (timeTo) {
      where["createdAt"] = {
        [Op.lt]: new Date(timeTo)
      };
    };

    if (cTags && cTags.length != 0 && cTags.reduce(
      (accumulator, currentValue) => accumulator && currentValue,
      true)) {
      include[2]["where"] = { keyword: { [Op.eq]: cTags[0] } };
      if (cTags[1]) {
        include[2]["include"] = [{
          model: Resource,
          as: "resources",
          include: [{
            model: ContentTag,
            as: "contentTags",
            where: { keyword: { [Op.eq]: cTags[1] } },
            include: [{
              model: Resource,
              as: "resources",
              include: [{
                model: ContentTag,
                as: "contentTags",
                where: { keyword: { [Op.eq]: cTags[2] } },
              }]
            }]
          }]
        }]
      }
    };

    const result = await Resource.findAll({
      where: where,
      include: include,
    });
    // ... 后续代码
  } catch (err) {
    // ... 错误处理
  }
});

正确实现方法 #

方法一：分组统计匹配标签数（推荐） #

这种方法通过关联ContentTag筛选出包含目标标签的记录，再按资源分组，要求匹配的标签数量等于目标标签总数，逻辑简洁且扩展性强。

router.get("/", async (req: any, res: Response) => {
  try {
    const { typologyTag, timeFrom, timeTo, author } = req.query;
    const cTags = JSON.parse(req.query.contentTags);
    const where: any = {};
    const include: any[] = [
      {
        model: TypologyTag,
        as: "typologyTag",
        required: !!typologyTag, // 有筛选条件时强制内连接
      },
      {
        model: User,
        as: "author",
        required: !!author,
      },
    ];

    // 处理时间范围条件
    if (timeFrom && timeTo) {
      where.createdAt = {
        [Op.lt]: new Date(timeTo),
        [Op.gt]: new Date(timeFrom),
      };
    } else if (timeFrom) {
      where.createdAt = { [Op.gt]: new Date(timeFrom) };
    } else if (timeTo) {
      where.createdAt = { [Op.lt]: new Date(timeTo) };
    }

    // 处理分类标签筛选
    if (typologyTag) {
      include[0].where = { keyword: { [Op.eq]: typologyTag } };
    }

    // 处理作者筛选
    if (author) {
      include[1].where = { displayName: { [Op.eq]: author } };
    }

    // 处理多内容标签筛选
    if (cTags && cTags.length > 0) {
      include.push({
        model: ContentTag,
        as: "contentTags",
        where: { keyword: { [Op.in]: cTags } },
        required: true, // 内连接，只保留有匹配标签的资源
      });

      const result = await Resource.findAll({
        where,
        include,
        group: ["Resource.id"], // 按资源ID分组
        // 确保资源匹配的标签数量等于目标标签总数
        having: sequelize.literal(`COUNT(DISTINCT contentTags.id) = ${cTags.length}`),
      });

      res.json(result);
    } else {
      // 无标签筛选时的普通查询
      const result = await Resource.findAll({ where, include });
      res.json(result);
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

方法二：多次关联中间表（对应原生SQL思路） #

这种方法通过多次关联ContentTag模型（每次用不同别名），确保资源同时满足所有标签条件，逻辑和你给出的SQL示例一致。

router.get("/", async (req: any, res: Response) => {
  try {
    const { typologyTag, timeFrom, timeTo, author } = req.query;
    const cTags = JSON.parse(req.query.contentTags);
    const where: any = {};
    const include: any[] = [
      {
        model: TypologyTag,
        as: "typologyTag",
        required: !!typologyTag,
      },
      {
        model: User,
        as: "author",
        required: !!author,
      },
    ];

    // 处理时间、分类标签、作者条件（同方法一）
    if (timeFrom && timeTo) {
      where.createdAt = {
        [Op.lt]: new Date(timeTo),
        [Op.gt]: new Date(timeFrom),
      };
    } else if (timeFrom) {
      where.createdAt = { [Op.gt]: new Date(timeFrom) };
    } else if (timeTo) {
      where.createdAt = { [Op.lt]: new Date(timeTo) };
    }

    if (typologyTag) {
      include[0].where = { keyword: { [Op.eq]: typologyTag } };
    }

    if (author) {
      include[1].where = { displayName: { [Op.eq]: author } };
    }

    // 为每个标签添加独立关联
    if (cTags && cTags.length > 0) {
      cTags.forEach((tag: string, index: number) => {
        include.push({
          model: ContentTag,
          as: `contentTag_${index}`, // 每个关联用唯一别名避免冲突
          through: { attributes: [] }, // 不返回中间表字段
          where: { keyword: tag },
          required: true, // 内连接，确保匹配当前标签
        });
      });

      const result = await Resource.findAll({ where, include });
      res.json(result);
    } else {
      const result = await Resource.findAll({ where, include });
      res.json(result);
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

方法对比 #

• 分组统计法：代码简洁，不管标签数量多少都无需修改逻辑，性能在标签数量不多时表现良好，推荐使用。
• 多次关联法：逻辑贴近原生SQL示例，适合标签数量较少的场景，标签过多时会增加关联次数，可能影响查询性能。

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