一个查询条件引发的惨案……

近日，在进行多租户改造时，增加了一个查询条件，却导致了意外的发生……

原本的查询是这样的：

typescriptCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
this.userModel
  .findByPk(userId, {
    attributes: attrs,
    include: [
      {
        model: UserMetaModel,
        attributes: ['metaKey', 'metaValue']
      }
    ]
  })
  .then((user) => {
    ...
  })this.userModel
  .findByPk(userId, {
    attributes: attrs,
    include: [
      {
        model: UserMetaModel,
        attributes: ['metaKey', 'metaValue']
      }
    ]
  })
  .then((user) => {
    ...
  })

改造后，增加了appId条件，于是变成了下面这样：

typescriptCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
this.userModel
  .findOne({
    attributes: attrs,
    where: {
      userId,
      appId
    },
    include: [
      {
        model: UserMetaModel,
        attributes: ['metaKey', 'metaValue'],
        where: {
          appId
        }
      }
    ]
  })
  .then((user) => {
    ...
  })this.userModel
  .findOne({
    attributes: attrs,
    where: {
      userId,
      appId
    },
    include: [
      {
        model: UserMetaModel,
        attributes: ['metaKey', 'metaValue'],
        where: {
          appId
        }
      }
    ]
  })
  .then((user) => {
    ...
  })

但此时，查询返回的user却是空的。去看SQL日志，得到的SQL查询语句是这样的：

sqlCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
  `UserModel`.*,
  `userMeta`.`meta_id` AS `userMeta.metaId`,
  `userMeta`.`user_id` AS `userMeta.userId`,
  `userMeta`.`meta_key` AS `userMeta.metaKey`,
  `userMeta`.`meta_value` AS `userMeta.metaValue`
FROM
  (
    SELECT
      *
    FROM
      `users` AS `UserModel`
    WHERE
      `UserModel`.`user_id` = 'xxx' AND `UserModel`.`app_id` = 'xxx' AND (
      SELECT
        `user_id`
      FROM
        `user_meta` AS `userMeta`
      WHERE
        `userMeta`.`app_id` = 'xxx' AND `userMeta`.`user_id` = `UserModel`.`user_id`
      LIMIT 1
    ) IS NOT NULL
    LIMIT 1
  ) AS `UserModel`
INNER JOIN `user_meta` AS `userMeta`
ON
  `UserModel`.`userId` = `userMeta`.`user_id` AND `userMeta`.`app_id` = 'xxx';SELECT
  `UserModel`.*,
  `userMeta`.`meta_id` AS `userMeta.metaId`,
  `userMeta`.`user_id` AS `userMeta.userId`,
  `userMeta`.`meta_key` AS `userMeta.metaKey`,
  `userMeta`.`meta_value` AS `userMeta.metaValue`
FROM
  (
    SELECT
      *
    FROM
      `users` AS `UserModel`
    WHERE
      `UserModel`.`user_id` = 'xxx' AND `UserModel`.`app_id` = 'xxx' AND (
      SELECT
        `user_id`
      FROM
        `user_meta` AS `userMeta`
      WHERE
        `userMeta`.`app_id` = 'xxx' AND `userMeta`.`user_id` = `UserModel`.`user_id`
      LIMIT 1
    ) IS NOT NULL
    LIMIT 1
  ) AS `UserModel`
INNER JOIN `user_meta` AS `userMeta`
ON
  `UserModel`.`userId` = `userMeta`.`user_id` AND `userMeta`.`app_id` = 'xxx';

很是奇怪，怎么一个关联查询中多出了一个IS NOT NULL查询？而且，按照规范，关联查询的where也应该是作为关联表的where或者on，而不是通过增加一个子查询去进行过滤。

于是回过头重新去看了API，才发现一个隐藏的坑：

If true, converts to an inner join, which means that the parent model will only be loaded if it has any matching children. True if include.where is set, false otherwise.

required?: boolean;

也就是说，在上面的代码中，增加了where条件后，关联查询就变成了inner join，而问题便出在这里，这里本应该是left outer join，于是查询结果变成了空。

因此，解决方案便也很简单了，将关联查询显式声明为left outer join即可。修改后的查询代码如下：

typescriptCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
this.userModel
  .findOne({
    attributes: attrs,
    where: {
      userId,
      appId
    },
    include: [
      {
        model: UserMetaModel,
        required: false,
        attributes: ['metaKey', 'metaValue'],
        where: {
          appId
        }
      }
    ]
  })
  .then((user) => {
    ...
  })this.userModel
  .findOne({
    attributes: attrs,
    where: {
      userId,
      appId
    },
    include: [
      {
        model: UserMetaModel,
        required: false,
        attributes: ['metaKey', 'metaValue'],
        where: {
          appId
        }
      }
    ]
  })
  .then((user) => {
    ...
  })

此时，其执行的SQL查询语句如下：

sqlCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
  `UserModel`.*,
  `userMeta`.`meta_id` AS `userMeta.metaId`,
  `userMeta`.`user_id` AS `userMeta.userId`,
  `userMeta`.`meta_key` AS `userMeta.metaKey`,
  `userMeta`.`meta_value` AS `userMeta.metaValue`
FROM
  (
    SELECT
      *
    FROM
      `users` AS `UserModel`
    WHERE
      `UserModel`.`user_id` = 'xxx' AND `UserModel`.`app_id` = 'xxx'
    LIMIT 1
  ) AS `UserModel`
LEFT OUTER JOIN `user_meta` AS `userMeta`
ON
  `UserModel`.`userId` = `userMeta`.`user_id` AND `userMeta`.`app_id` = 'xxx';SELECT
  `UserModel`.*,
  `userMeta`.`meta_id` AS `userMeta.metaId`,
  `userMeta`.`user_id` AS `userMeta.userId`,
  `userMeta`.`meta_key` AS `userMeta.metaKey`,
  `userMeta`.`meta_value` AS `userMeta.metaValue`
FROM
  (
    SELECT
      *
    FROM
      `users` AS `UserModel`
    WHERE
      `UserModel`.`user_id` = 'xxx' AND `UserModel`.`app_id` = 'xxx'
    LIMIT 1
  ) AS `UserModel`
LEFT OUTER JOIN `user_meta` AS `userMeta`
ON
  `UserModel`.`userId` = `userMeta`.`user_id` AND `userMeta`.`app_id` = 'xxx';

结果按照预期的那样，通过on在关联查询中进行过滤，而关联查询也不再是inner join……[]~(￣▽￣)~*

Node.js