join - 使用条件对连接查询进行序列化

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` = 'xyz@gmail.com';
           ^^^^^^^^^^^^^^^^^^^^^^

所以它给了我错误: Unknown column 'account.user.email' in 'where clause' .我只想要 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` = 'xyz@gmail.com';

我究竟做错了什么？？？

Answer 1

将用户表的 where 过滤器放在 include 部分:

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