javascript - 顶级sequelize v5的where子句错误

Question

我希望你能帮助我找到解决这个问题的方法。

基本上，这个查询所做的是从一个名为“jobs”的表中加载具有多个条件的元素。条件是相关市场的负载、工作状态、关键字和排序。

片段如下所示：

          sequelize.models.jobs.findAll( {
            offset: ( filters.page * filters.size ) - filters.size,
            limit: filters.size,
            where: [
              {
                status:"approved"
              }
            ],
            include: [
              {
                model: sequelize.models.users,
                attributes: [
                  'id', 'firstName', 'lastName', 'phoneNumber', 'email', 'profilePicture'
                ],
                required: false
              }
            ]
          } )

如果您注意到有一种关系正在加载，称为用户，并且一切正常（就像我可以说的魅力）。但是当我尝试在查询的顶层执行一些 where 子句时，问题就来了。例如，如果我在整个逻辑中断的地方添加这个：

{
 status: "approved",
 '$users.firstName$': "Put a name here"
}

Sequelize 生成的查询：

 SELECT `jobs`.*, `users`.`id` AS `users.id`, `users`.`firstName` AS `users.firstName`, `users`.`lastName` AS `users.lastName`, `users`.`phoneNumber` AS `users.phoneNumber`, `users`.`email` AS `users.email`, `users`.`profilePicture` AS `users.profilePicture`, `users->jobWorkers`.`workerId` AS `users.jobWorkers.workerId`, `users->jobWorkers`.`jobId` AS `users.jobWorkers.jobId`, `users->jobWorkers`.`hours` AS `users.jobWorkers.hours`, `users->jobWorkers`.`wage` AS `users.jobWorkers.wage`, `users->jobWorkers`.`bonus` AS `users.jobWorkers.bonus`, `users->jobWorkers`.`paid` AS `users.jobWorkers.paid`, `users->jobWorkers`.`createdAt` AS `users.jobWorkers.createdAt`, `users->jobWorkers`.`updatedAt` AS `users.jobWorkers.updatedAt`, `users->jobWorkers`.`deletedAt` AS `users.jobWorkers.deletedAt` FROM (SELECT `jobs`.`id`, `jobs`.`rate`, `jobs`.`discount`, `jobs`.`drivingFee`, `jobs`.`bonus`, `jobs`.`token`, `jobs`.`type`, `jobs`.`jobDateTime`, `jobs`.`jobStartTimestamp`, `jobs`.`laborjacks`, `jobs`.`hours`, `jobs`.`adminNotes`, `jobs`.`description`, `jobs`.`contactName`, `jobs`.`contactPhone`, `jobs`.`contactEmail`, `jobs`.`multipleLocations`, `jobs`.`startAddressStreet1`, `jobs`.`startAddressStreet2`, `jobs`.`startAddressStreet3`, `jobs`.`startAddressCity`, `jobs`.`startAddressState`, `jobs`.`startAddressZip`, `jobs`.`endAddressStreet1`, `jobs`.`endAddressStreet2`, `jobs`.`endAddressStreet3`, `jobs`.`endAddressCity`, `jobs`.`endAddressState`, `jobs`.`endAddressZip`, `jobs`.`specialInstructions`, `jobs`.`hearAboutUs`, `jobs`.`status`, `jobs`.`tip`, `jobs`.`typeOther`, `jobs`.`createdAt` FROM `jobs` AS `jobs` WHERE (`jobs`.`deletedAt` IS NULL AND (((`jobs`.`description` LIKE '%Daniel%' OR `jobs`.`contactName` LIKE '%Daniel%' OR `jobs`.`contactEmail` LIKE '%Daniel%' OR `jobs`.`startAddressCity` LIKE '%Daniel%' OR `jobs`.`startAddressState` LIKE '%Daniel%' OR `jobs`.`startAddressStreet1` LIKE '%Daniel%' OR `jobs`.`startAddressStreet2` LIKE '%Daniel%' OR `jobs`.`startAddressStreet3` LIKE '%Daniel%' OR `jobs`.`startAddressZip` LIKE '%Daniel%' OR `jobs`.`endAddressState` LIKE '%Daniel%' OR `jobs`.`endAddressStreet1` LIKE '%Daniel%' OR `jobs`.`endAddressStreet2` LIKE '%Daniel%' OR `jobs`.`endAddressStreet3` LIKE '%Daniel%' OR `jobs`.`endAddressZip` LIKE '%Daniel%' OR `jobs`.`specialInstructions` LIKE '%Daniel%' OR `jobs`.`adminNotes` LIKE '%Daniel%' OR `users`.`firstName` = 'Daniel') AND `jobs`.`status` = 'active'))) LIMIT 0, 10) AS `jobs` LEFT OUTER JOIN ( `jobWorkers` AS `users->jobWorkers` INNER JOIN `users` AS `users` ON `users`.`id` = `users->jobWorkers`.`workerId`) ON `jobs`.`id` = `users->jobWorkers`.`jobId` AND (`users`.`deletedAt` IS NULL)

续集错误：

'where 子句'中的未知列'users.firstName'

如果您可以看到 sequelize 在作业WHERE中添加 users.firstName而不是 Left Outer Join。这是与名为 JobWorkers 的联结表的多对多关系，配置为：

sequelize.models.jobs.belongsToMany( sequelize.models.users, { through: sequelize.models.jobWorkers, foreignKey: 'jobId'} );

sequelize.models.users.belongsToMany( sequelize.models.jobs, { through: sequelize.models.jobWorkers, foreignKey: 'workerId' } );

我不知道该怎么办。我是否需要在 findAll 函数或其他东西中设置一个选项？表关系错了？我已经尝试了一切，但似乎没有任何效果。

感谢您阅读所有这些主题，如果有什么我没有解释或令人困惑，请告诉我。

标签： javascriptmysqlnode.jssequelize.js