I have a belongs to many relationship with a joined table
courseTree.associate = function (models) {
models.courses.belongsToMany(models.courses, {
through: 'course_tree',
as: 'children',
foreignKey: 'parent_id',
otherKey: 'child_id'
});
};
Currently when I run a find I get all my courses back, even the courses that are children, this is expected behavior but I want to have another scope where I can request only the courses with children.
In my scope I have the following where:
scopes: {
parents: {
where: {
children: { [Op.not] : null }
}
}
}
But the Rest Api gives me the following output
"name": "GeneralError",
"message": "column courses.children does not exist",
"code": 500
In the documentation I can't find any way to do this, I tried sequelize.literal and the in operator but without success. Does anyone knows how this is done, I'm sure I'm missing something.
Solved this by using Sequelize.literal
courses.addScope('getParents', {
where: {
[Op.and]: Sequelize.literal('"courses"."id" NOT IN (SELECT "course_tree"."child_id" FROM "course_tree")')
}
})
Another problem I ran into was that the child was the same model as the parent, in which case the where is also applied on the child. I solved this by adding where: false
in the include.