Sequelize条件联接查询

Question

我正在使用以下语法选择一些记录：

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

它正在生成以下查询：

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` = '[e-mail]';
        ^^^^^^^^^^^^^^^^^^^^^^

所以它给了我一个错误：Unknown column 'account.user.email' in 'where clause'。我只想在WHERE子句中使用user.email。期望的查询应该是：

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

我做错了什么吗？

- Manwal

Answer 1

在 include 部分为用户表添加 where 过滤器:

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

- Evan Siroky