Sequelize where scope with an joined table

Question

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.

Answer 1

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.