Sequelize：按关联数量/多个关联筛选查询

问：

我在 sequelize 中有两个模型：modelA 和 modelB。 这些模型处于多对多关系中。

我想获取 modelA 的所有实体，这些实体被关联到 modelB 的（多个）特定实例。

例如，我想获取与 id 为 （1， 3， 4） 的 modelB 相关的所有 modelA 模型。我想让每个模型至少与所有指定的模型B有关系。

如何在续集中实现这一点？

这是我到目前为止尝试过的：

模型 1：

@Table
export class Workout extends Model<Partial<Workout>> {
    @UniqueIndex
    @Column
    name!: string;

    @Column
    length!: number;

    @UniqueIndex
    @ForeignKey(() => User)
    @Column
    userId?: number;

    @BelongsTo(() => User, "userId")
    user?: User;

    @BelongsToMany(() => Tag, () => WorkoutTag)
    tags?: Tag[];
}

模型 2：

@Table({ timestamps: false })
export class Tag extends Model<Partial<Tag>> {
    @Unique
    @Column
    name!: string;

    @BelongsToMany(() => Workout, () => WorkoutTag)
    workouts?: Workout[];
}

和连接表

@Table({ tableName: "workout_tag", timestamps: false })
export class WorkoutTag extends Model<WorkoutTag> {
    @ForeignKey(() => Workout)
    @Column
    workoutId!: number;

    @ForeignKey(() => Tag)
    @Column
    tagId!: number;

    @BelongsTo(() => Workout)
    workout!: Workout;

    @BelongsTo(() => Tag)
    tag!: Tag;
}

我尝试过的查询：

        const include: Includeable[] = [{ model: User, as: "user" }];
        const additionalOptions: FindOptions<Partial<Workout>> = {};

        if ((filter?.tags?.length ?? 0) > 0) {
            console.log("Applying filter", filter?.tags);
            include.push({
                model: Tag,
                through: {
                    where: {
                        tagId: { [Op.in]: filter?.tags },
                    },
                },
            });
            additionalOptions.group = ["`Workout`.`id`"];
            additionalOptions.having = sequelize.literal(
                `COUNT(DISTINCT "\`tags->WorkoutTag\`.\`tagId\`") = ${filter!.tags!.length}`
            );
        }

        console.log("Loading workouts");

        const workouts = await Workout.findAll({
            include,
            limit: WORKOUTS_PER_PAGE,
            offset: (page - 1) * WORKOUTS_PER_PAGE,
            where: {
                name: { [Op.like]: `%${search}%` },
                length: { [Op.between]: [filter?.length?.min ?? 0, filter?.length?.max ?? 180] },
            },
            ...additionalOptions,
        });

我正在尝试获取与数组中指定的所有标签相关的所有锻炼。filter.tags

filter.tags是标记实体的 ID 数组

我使用的数据库是MySQL

这些是我使用的表格：

CREATE TABLE `workouts` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   `length` int DEFAULT NULL,
   `userId` int DEFAULT NULL,
   `createdAt` datetime DEFAULT NULL,
   `updatedAt` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `unique-index` (`name`,`userId`),
   KEY `userId` (`userId`),
   CONSTRAINT `workouts_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci



CREATE TABLE `users` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   `createdAt` datetime DEFAULT NULL,
   `updatedAt` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `name` (`name`),
   UNIQUE KEY `email` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci



CREATE TABLE `tags` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `name` (`name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `workout_tag` (
   `workoutId` int NOT NULL,
   `tagId` int NOT NULL,
   PRIMARY KEY (`workoutId`,`tagId`),
   UNIQUE KEY `workout_tag_tagId_workoutId_unique` (`workoutId`,`tagId`),
   KEY `tagId` (`tagId`),
   CONSTRAINT `workout_tag_ibfk_1` FOREIGN KEY (`workoutId`) REFERENCES `workouts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `workout_tag_ibfk_2` FOREIGN KEY (`tagId`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci