Where condition on extra field many to many with Sequelize

user_5453732 0
Viewed 103

I would like to know how can I execute a where condition on extra field.

Currently I have 3 tables :

Courses : id, title, description,
Sections: id, title, description,
CoursesSections : id, fk_course_id, fk_section_id, position

Currently I have this request and It works to have Course with Sections associate order by position :

let course = await db.Course.findOne({
    where: {
        id: parent.dataValues.id,
    },
    include: [
        {
            model: db.Section,
            as: "Section",
            through: {
                attributes: ["position"],
            },
        },
    ],
    order: [[Sequelize.literal("position"), "ASC"]],
})

But I would like to apply a where condition, to have only position 1, or 2 for example.

I tried this, but it doesn't work :

course = await db.Course.findOne({
    where: {
        id: 1,
    },
    include: [
        {
            model: db.Section,
            as: "Section",
            through: {
                attributes: ["position"],
            },
            where: {
                position: 2,
            },
        },
    ],
    order: [[Sequelize.literal("position"), "ASC"]],
})

I have this error :

"message": "Unknown column 'Section.position' in 'on clause'"

sequelize.js node.js
1 Answers

I put the where condition in through :

const course = await db.Course.findOne({
    where: {
        id: parent.dataValues.id,
    },
    include: [
        {
            model: db.Section,
            as: "Section",
            through: {
                attributes: ["position"],
                where: {
                    position: 1,
                },
            },
        },
    ],
    order: [[Sequelize.literal("position"), "ASC"]],
})
user_54537320
Related