Sequelize顶层，其中有急于加载的模型会创建子查询 [英] Sequelize Top level where with eagerly loaded models creates sub query

问题描述

我遇到了一个问题，即Sequelize创建主要模型的子查询，然后将includes与该子查询而不是直接与主要模型表联接。包含的查询条件最终出现在子查询的WHERE子句中，这使其无效。我已缩短名称，以尽量保持紧凑性而又不丢失任何相关信息。

I'm running an into issue where Sequelize creates a subquery of the primary model and then joins the includes with that subquery instead of directly with the primary model table. The query conditions for the include(s) ends up inside the subquery's WHERE clause which makes it invalid. I have shortened names down trying to keep this compact hopefully without losing any relevant info.

环境：
Nodejs：6.11.3
后继：3.23.6 =>更新至4.38.1，问题仍然存在
MySql ：5.7.23

Environment: Nodejs: 6.11.3 Sequelize: 3.23.6 => Updated to 4.38.1 and problem persists MySql: 5.7.23

代码片段模型：

I.model:
   models.I.hasMany(models.II);
   models.I.belongsTo(models.CJ);
   models.I.belongsTo(models.CJS);
II.model:
   models.II.belongsTo(models.I);
CJ.model:
   models.CJ.hasMany(models.I);
   models.CJ.hasMany(models.CJS);
CJS.model:
    models.CJS.hasMany(models.I);

代码片段查询定义：

let where = { cId: '2',
iAmt: { '$gt': 0 },
'$or': 
 [ { '$CJ.a1$': {$like: '%246%'}} },
   { '$CJ.a2$': {$like: '%246%'} },
   { '$I.cPN$': {$like: '%246%'} } 
 ] };

 let query = {
   where: where,
   order: orderBy,
   distinct: true,
   offset: offset,
   limit: limit,
   include: [
   {
    model: CJ, 
    as: 'CJ',
    required: false
  }, {
    model: CJS, 
    as: 'CJS',
    required: false
  }, {
    model: II,
    as: 'IIs',
    required: false
  }
  ]
  };

  I.findAll(query)

产生如下所示的SQL：

Produces SQL like the following:

SELECT `I`.*, `CJ`.`_id` AS `CJ._id`, `CJS`.`_id` AS `CJS._id`, `IIs`.`_id` AS `IIs._id`
FROM (SELECT `I`.`_id`, `I`.`CJId`, `I`.`CJSId`, `I`.`CId` 
  FROM `Is` AS `I` 
 WHERE `I`.`CId` = '2' AND 
       `I`.`iA` > 0 AND 
     (`CJ`.`a1` LIKE '%246%' OR 
      `CJ`.`a2` LIKE '%246%' OR 
      `I`.`cPN` LIKE '%246%'
     ) 
     ORDER BY `I`.`iNum` DESC LIMIT 0, 10) AS `I` 
     LEFT OUTER JOIN `CJs` AS `CJ` ON `I`.`CJId` = `CJ`.`_id` 
     LEFT OUTER JOIN `CJSs` AS `CJS` ON `I`.`CJSId` = `CJS`.`_id` 
     LEFT OUTER JOIN `IIs` AS `IIs` ON `I`.`_id` = `IIs`.`IId` 
     ORDER BY `I`.`iNum` DESC;

我期待这样的事情：

SELECT `I`.*, `CJ`.`_id` AS `CJ._id`, `CJS`.`_id` AS `CJS._id`, `IIs`.`_id` AS `IIs._id`
  FROM `Is` AS `I` 
     LEFT OUTER JOIN `CJs` AS `CJ` ON `I`.`CJId` = `CJ`.`_id` 
     LEFT OUTER JOIN `CJSs` AS `CJS` ON `I`.`CJSId` = `CJS`.`_id` 
     LEFT OUTER JOIN `IIs` AS `IIs` ON `I`.`_id` = `IIs`.`IId` 

WHERE `I`.`CId` = '2' AND 
       `I`.`iA` > 0 AND 
     (`CJ`.`a1` LIKE '%246%' OR 
      `CJ`.`a2` LIKE '%246%' OR 
      `I`.`cPN` LIKE '%246%'
) 
ORDER BY `I`.`iNum` DESC LIMIT 0, 10

如果我从包含中删除 II 模型，它将起作用，并将WHERE移到顶层。我承认查询的结构在这里不是直截了当的， I 是 CJ 和 CJS 的子代，而后者又是子代 CJ 。然后 II 是 I 的孩子。我在这里想念什么？

If I remove the II model from the include it does work and moves the the WHERE to the top level. I admit the structure of the query is not straight forward here, with I being a child of CJ and CJS, which in turn is a child of CJ. And then II a child of I. What am I missing here?

Bueller或任何人的2分欢迎！

Bueller's or anyone's 2 cent welcome!

推荐答案

这里发生了什么这是因为您还使用 order 和 limit 以及急切的加载关联查看该问题

what happened here is because you also using order and limit together with eager loading association see the issue

要使其正常运行，有一点棘手的解决方案，则需要将 subQuery：false 一起添加到根模型查询中

to make it work, there is a little hacky solution, you need to add subQuery: false together to your root model query

let query = {
   where: where,
   order: orderBy,
   distinct: true,
   offset: offset,
   limit: limit,
   subQuery: false,
   include: [...]
};

这篇关于Sequelize顶层，其中有急于加载的模型会创建子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！