Sequelize: Filter query by amount of / multiple associations

Question

I have two models in sequelize: modelA and modelB. Those models are in a many-to-many relation.

I want to get all entitys of modelA that are assiciated to (multiple) specific instances of modelB.

For example, I want to get all models of modelA that have a relation to modelBs with id (1, 3, 4). I want to get every model a that AT LEAST has a relation to all of the specified modelBs.

How can implement this in sequelize?

Here is what I have tried so far:

Model 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[];
}

Model 2:

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

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

And the connection table

@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;
}

The query I have tried:

        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,
        });

I am trying to get all the workouts that are related to all the tags specified in the filter.tags array.

filter.tags is an array of ids of the tag entitys

The database I use is MySQL

These are the tables I use:

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

Answer 1

You need to perform "relational division" (recommended reading).

The SQL solution to find all workouts that have tags 1, 3, 4 (and possibly more) associated with them is:

SELECT *
FROM workouts
WHERE NOT EXISTS (
    SELECT 1
    FROM (VALUES ROW(1), ROW(3), ROW(4)) AS required_tags(id)
    WHERE NOT EXISTS (
        SELECT 1
        FROM workout_tag
        WHERE workout_tag.workoutId = workouts.id AND workout_tag.tagId = required_tags.id
    )
);

Or if you find conditional aggregation more easier to understand then:

SELECT *
FROM workouts
WHERE id IN (
    SELECT workoutId
    FROM workout_tag
    WHERE tagId IN (1, 3, 4)
    GROUP BY workoutId
    HAVING COUNT(*) = 3
    -- the "= 3" part must match the number of items in the IN clause
)

DB<>Fiddle

The second query is easier to convert to Sequelize, you can add the having clause as Sequelize.literal.