Sequelize的顶层where子句和LIMIT的bug

问题复现

module与member关系：一对多

sequelize语句：

const conditions: WhereOptions = {}
conditions.name = {
        [Op.like]: `%${keyword}%`,
}
conditions['$members.user_id$'] = userId
conditions['$members.type$'] = MEMBER_CATEGORY.MODULE
​
const { rows, count: totalCount } =
  await this.moduleRepository.findAndCountAll({
      {
        attributes: [],
        model: MemberModel,
        required: true,
      }
    offset,
    where: conditions,
    limit,
    order: [['id', 'DESC']],
    raw: true,
  })

生成的SQL:

-- duplicating: true; 使用 limit 分页查询
SELECT  ModuleModel.*
FROM
(
  SELECT  ModuleModel.id
         ,ModuleModel.scope
         ,ModuleModel.name
         ,ModuleModel.created_at AS createdAt
  FROM module AS ModuleModel
  WHERE ModuleModel.name LIKE '%t%'
  AND members.user_id = '2lFuiw4xBEiNucFsuP5PMG'
  AND members.type = 2
  AND (
  SELECT  res_id
  FROM member AS members
  WHERE (members.res_id = ModuleModel.id)
  LIMIT 1 ) IS NOT NULL
  ORDER BY ModuleModel.id DESC
  LIMIT 0, 10
) AS ModuleModel
INNER JOIN member AS members
ON ModuleModel.id = members.res_id
ORDER BY ModuleModel.id DESC;

由于ModuleModel上没有user_id，报错：

排查原因

在 sequelize官网的findAll 方法发现一个duplicating属性，释义如下：

Mark the include as duplicating, will prevent a subquery from being used.

即当使用include来指定预加载模型时，sequelize默认使用子查询，使用duplicating属性标记不使用子查询。

-- duplicating: false; 使用 limit 分页查询
SELECT  ModuleModel.id
       ,ModuleModel.scope
       ,ModuleModel.name
       ,ModuleModel.created_at AS createdAt
FROM module AS ModuleModel
INNER JOIN member AS members
ON ModuleModel.id = members.res_id
WHERE ModuleModel.name LIKE '%te%'
AND members.user_id = '2lFuiw4xBEiNucFsuP5PMG'
AND members.type = 2
ORDER BY ModuleModel.id DESC
LIMIT 0, 10;

这就是我们想要的SQL。

但是 sequelize中文官网示例 也是这样的写法，为什么生成的sql语句没有问题？开始排除大法

示例使用的查询都没有使用LIMIT分页，当去掉limit和offset时，即使不指定duplicating:false，发现生成的SQL语句是对的了

-- duplicating: true; 不使用 limit 分页查询
SELECT  ModuleModel.id
       ,ModuleModel.scope
       ,ModuleModel.name
       ,ModuleModel.created_at AS createdAt
FROM module AS ModuleModel
INNER JOIN member AS members
ON ModuleModel.id = members.res_id
WHERE ModuleModel.name LIKE '%test%'
AND members.user_id = '2lFuiw4xBEiNucFsuP5PMG'
AND members.type = 2
ORDER BY ModuleModel.id DESC;

尝试不使用顶层where子句，而是在include里使用where子句，作为INNER JOIN的ON子句的条件：

sequelize语句：

const conditions: WhereOptions = {}
conditions.name = {
        [Op.like]: `%${keyword}%`,
}
const { rows, count: totalCount } =
  await this.moduleRepository.findAndCountAll({
    include: filterMine
      ?
      {
        attributes: [],
        model: MemberModel,
        // duplicating: false,
        required: true,
        where:{
          user_id: userId,
          type: MEMBER_CATEGORY.MODULE
        }
      }
      : null,
    offset,
    where: conditions,
    limit,
    order: [['id', 'DESC']],
    raw: true,
  })

生成的sql:

-- 预先加载where，使用limit
SELECT  ModuleModel.*
FROM
(
  SELECT  ModuleModel.id
         ,ModuleModel.scope
         ,ModuleModel.name
         ,ModuleModel.created_at AS createdAt
  FROM module AS ModuleModel
  WHERE ModuleModel.name LIKE '%te%'
  AND (
  SELECT  res_id
  FROM member AS members
  WHERE ((members.user_id = '2lFuiw4xBEiNucFsuP5PMG' AND members.type = 2) AND members.res_id = ModuleModel.id)
  LIMIT 1 ) IS NOT NULL
  ORDER BY ModuleModel.id DESC
  LIMIT 0, 10
) AS ModuleModel
INNER JOIN member AS members
ON ModuleModel.id = members.res_id AND members.user_id = '2lFuiw4xBEiNucFsuP5PMG' AND members.type = 2
ORDER BY ModuleModel.id DESC;

这次生成了正确的SQL，也返回了正确的结果。

可见原因在于，使用include时，同时使用顶层where子句和limit 进行查询，sequelize生成的SQL语句有问题。

解决

仅针对我遇到的这种情形，有以下两种方法

一、指定include的duplicating属性为false

二、不使用顶层where子句，在include中指定where