Sequelize join query with condition

Question

I am using the following syntax to select some records:

models.account.findAll({
  attributes: ['password'],
  include: [{
    model: models.user,
    as : 'user'
  }],
  where: {
    'password': password,
    'user.email': email
  }
}).then(function(res){
  console.log(res);
});

It's generating the following query:

SELECT * 
FROM   `accounts` AS `account` 
    LEFT OUTER JOIN `users` AS `user` 
        ON `account`.`user_id` = `user`.`id` 
WHERE  `account`.`password` = 'PASSWORD' 
    AND `account`.`user.email` = '[email protected]';
        ^^^^^^^^^^^^^^^^^^^^^^

So it's giving me an error: Unknown column 'account.user.email' in 'where clause'. I want only user.email in the WHERE clause. The expected query should be:

SELECT * 
FROM   `accounts` AS `account` 
    LEFT OUTER JOIN `users` AS `user` 
        ON `account`.`user_id` = `user`.`id` 
WHERE  `account`.`password` = 'PASSWORD' 
    AND `user`.`email` = '[email protected]';

What am i doing wrong?

Answer 1

Put the where filter for the users table in the include section:

models.account.findAll({
  attributes: ['password'],
  include: [{
    model: models.user,
    where: {
      email: email
    }
  }],
  where: {
    password: password
  }
}).then(function(res){
  console.log(res);
});