Sequelize Top level 使用热切加载的模型创建子查询 [英] Sequelize Top level where with eagerly loaded models creates sub query

问题描述

我遇到了一个问题，即 Sequelize 创建主模型的子查询，然后将包含项与该子查询连接，而不是直接与主模型表连接.包含的查询条件最终在子查询的 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.

环境:节点:6.11.3Sequelize: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:

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 的孩子，而 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 Top level 使用热切加载的模型创建子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！