JAVASCRIPT

sequlize where clause involving associated relationship

// Inner where, with default `required: true`
await User.findAll({
  include: {
    model: Post,
    as: 'Posts',
    where: {
      status: { [Op.ne]: 'draft' }
    }
  }
});
SELECT * FROM `users` AS `user`
INNER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
  AND `Posts`.`status` != 'draft';
// Inner where, `required: false`
await User.findAll({
  include: {
    model: Post,
    as: 'Posts',
    where: {
      status: { [Op.ne]: 'draft' }
    },
    required: false
  }
});
SELECT * FROM `users` AS `user`
LEFT OUTER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
  AND `Posts`.`status` != 'draft';

// Top-level where, with default `required: false`
await User.findAll({
  where: {
    '$Posts.status$': { [Op.ne]: 'draft' }
  },
  include: {
    model: Post,
    as: 'Posts'
  }
});
SELECT * FROM `users` AS `user`
LEFT OUTER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
WHERE `Posts`.`status` != 'draft';
// Top-level where, `required: true`
await User.findAll({
  where: {
    '$Posts.status$': { [Op.ne]: 'draft' }
  },
  include: {
    model: Post,
    as: 'Posts',
    required: true
  }
});
SELECT * FROM `users` AS `user`
INNER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
WHERE `Posts`.`status` != 'draft';
User.findAll({   
 include: [{    
  model: Post,     
  where: { 
   status: { 
    [Op.ne]: 'draft' 
   } 
  },     
  required: false     
  right: true // will create a right join   
 }] 
});