node.jssequelize.jsfeathers-sequelize

Sequelize where scope with an joined table


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.


Solution

  • 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.