带条件的联接查询

Manwal 发表于 Dev

手册

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

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` = '[email protected]';
           ^^^^^^^^^^^^^^^^^^^^^^

因此，它给我的错误：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` = '[email protected]';

我究竟做错了什么？？？

埃文·西洛基（Evan Siroky）

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

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。