Sequelize多表OR语句未知列错误-Java 学习之路

我正在尝试对我的模型进行搜索，我想让所有用户将email，firstName或lastName与搜索字符串相似，或者UsersSecondaryEmails表（相关模型）包含类似该字符串的电子邮件 .

因为OR语句的各个部分位于不同的表中，所以这有点棘手，我只能找到其他StackOverflow答案来帮助我 .

这是我的查询（简化）：

const companiesUsersParams = {
  where: {
    companyId: req.params.companyId,
    roleId: role.id
  },
  include: [{
    model: models.Users,
    attributes: ['id', 'firstName', 'lastName', 'email'],
    where: req.query.s ? {
      [Op.or]: [{
        firstName: {
          [Op.like]: `%${req.query.s}%`
        }
      }, {
        lastName: {
          [Op.like]: `%${req.query.s}%`
        }
      }, {
        email: {
          [Op.like]: `%${req.query.s}%`
        }
      }, {
        '$usersSecondaryEmails.email$': {
          [Op.like]: `%${req.query.s}%`
        }
      }]
    } : null,
    include: [{
      model: models.UsersSecondaryEmails,
      attributes: ['id', 'email'],
      as: 'usersSecondaryEmails'
    }]
  }]
}

如果未定义 req.query.s ，则查询按预期运行（无OR语句），因此我知道这不是我的关联的问题 .

当我运行此查询WITH req.query.s 已定义时，我得到

'on子句'中的未知列'usersSecondaryEmails.email'

这里是生成的SQL（尽可能格式化）：

SELECT `companiesUsers`.`id`,
       `companiesUsers`.`company_id` AS `companyId`, 
       `companiesUsers`.`user_id` AS `userId`,
       `companiesUsers`.`role_id` AS `roleId`,
       `companiesUsers`.`created_at` AS `createdAt`, 
       `companiesUsers`.`updated_at` AS`updatedAt`,
       `user`.`id` AS `user.id`,
       `user`.`first_name` AS `user.firstName`,
       `user`.`last_name` AS `user.lastName`,
       `user`.`email` AS `user.email`,
       `user->usersSecondaryEmails`.`id` AS `user.usersSecondaryEmails.id`,
       `user->usersSecondaryEmails`.`email` AS
       `user.usersSecondaryEmails.email` 
 FROM `CompaniesUsers` AS `companiesUsers`
 INNER JOIN `Users` AS `user`
       ON `companiesUsers`.`user_id` = `user`.`id` AND 
       (`user`.`first_name` LIKE '%bob%' OR
       `user`.`last_name` LIKE '%bob%' OR
       `user`.`email` LIKE '%bob%' OR
       `usersSecondaryEmails`.`email` LIKE '%bob%')
 LEFT OUTER JOIN `UsersSecondaryEmails` AS `user->usersSecondaryEmails`
       ON `user`.`id` = `user->usersSecondaryEmails`.`user_id`
 WHERE `companiesUsers`.`company_id` = '1'
       AND `companiesUsers`.`role_id` = 20;

任何关于Sequelize中多表OR语句的文档的任何建议或链接都会很棒（我在文档中找不到任何高级内容） .

1 回答

1
我不知道如何修改 companiesUsersParams 的Sequelize代码，但您的最终查询应如下所示，以获得所需的输出 . 这可以帮助您重写Sequelize代码 .
- 左连接用户而不是内连接 .
- 您应该将最后一个OR选项 usersSecondaryEmails.email LIKE '%bob%' 移动到UsersSecondaryEmails连接条件
- 在where子句中，检查条件（用户表中应存在至少一行或userid存在usersSecondaryEmails）
SELECT companiesUsers.id, companiesUsers.company_id AS companyId, companiesUsers.user_id AS userId, companiesUsers.role_id AS roleId, companiesUsers.created_at AS createdAt, companiesUsers.updated_at ASupdatedAt, user.id AS user.id, user.first_name AS user.firstName, user.last_name AS user.lastName, user.email AS user.email, user->usersSecondaryEmails.id AS user.usersSecondaryEmails.id, user->usersSecondaryEmails.email AS user.usersSecondaryEmails.email FROM CompaniesUsers AS companiesUsers LEFT OUTER JOIN Users AS user ---- #1 ON companiesUsers.user_id = user.id AND (user.first_name LIKE '%bob%' OR user.last_name LIKE '%bob%' OR user.email LIKE '%bob%' OR) LEFT OUTER JOIN UsersSecondaryEmails AS user->usersSecondaryEmails ON companiesUsers.user_id = user->usersSecondaryEmails.user_id AND user->usersSecondaryEmails.email LIKE '%bob%' ---- #2 WHERE companiesUsers.company_id = '1' AND companiesUsers.role_id = 20 AND (user.email is Not null OR user->usersSecondaryEmails.user_id is Not Null); ---- #3
回复于 2024-04-29T16:07:35+08:00

Sequelize多表OR语句未知列错误

1 回答

相关问题