postgresql Sequelize是在where子句中自动添加子查询,有没有办法让它跳过添加where子句

6qqygrtg 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(135)

我有一个使用INNER JOINS的Sequelize查询。问题是sequelize在内部添加了另一个where子句，并在子表上添加了子查询。这会消耗查询性能。下面是我的代码和原始查询输出的示例。
有没有办法让sequelize跳过添加这个where子句？
续集版本：6.x

PostModel.findAll({
   where: {
     id: 1,
   },
   include: [
     {
        model: CommentsModel,
        required: true,
     }
   ]
})

该查询构建一个SQL查询，如下所示。

SELECT "post".*
FROM (SELECT "post"."*"
      FROM "posts" AS "post"
      WHERE "post"."id" = 2
        AND (SELECT "post_id"
             FROM "comments" AS "c"
             WHERE "comments"."post_id" = "post"."id" AND ("c"."text_search" @@ 'who:*')) IS NOT NULL
      ORDER BY "post"."id" DESC
      LIMIT 50 OFFSET 0) AS "post"
         LEFT OUTER JOIN "post_tags" AS "tags" ON "post"."id" = "tags"."post_id"
         LEFT OUTER JOIN "tag" AS "tags->tag" ON "tags"."tag_id" = "tags->tag"."id"
         INNER JOIN "comments" AS "c" ON "post"."id" = "c"."post_id" AND ("c"."text_search" @@ 'who:*')
ORDER BY "post"."id" DESC;

如您所见，WHERE子句新增了一个

(SELECT "post_id"
             FROM "comments" AS "c"
             WHERE "comments"."post_id" = "post"."id" AND ("c"."text_search" @@ 'who:*'))

这基本上会降低查询的性能。

postgresql

来源：https://stackoverflow.com/questions/68217654/sequelize-is-automatically-adding-a-sub-query-within-the-where-clause-is-there

2条答案

按热度按时间

s4chpxco1#

经过大量的研究，我找到了解决方案。
我们需要在关联中添加subQuery: false。

PostModel.findAll({
   where: {
     id: 1,
   },
   include: [
     {
        subQuery: false,
        model: CommentsModel,
        required: true,
     }
   ]
})

查询输出：

SELECT "post".*
FROM (SELECT "post"."*"
      FROM "posts" AS "post"
      WHERE "post"."id" = 2 
      ORDER BY "post"."id" DESC
      LIMIT 50 OFFSET 0) AS "post"
         LEFT OUTER JOIN "post_tags" AS "tags" ON "post"."id" = "tags"."post_id"
         LEFT OUTER JOIN "tag" AS "tags->tag" ON "tags"."tag_id" = "tags->tag"."id"
         INNER JOIN "comments" AS "c" ON "post"."id" = "c"."post_id" AND ("c"."text_search" @@ 'who:*')
ORDER BY "post"."id" DESC;

赞(0）回复(0）举报 2023-06-22

m1m5dgzv2#

就在Mir Adnan回答之前，它是正确的，但不适用于根查询。
只是放在include之外。subQuery等于false。

PostModel.findAll({
   where: {
     id: 1,
   },
   **subQuery: false,**
   include: [
     {
        subQuery: false,
        model: CommentsModel,
        required: true,
     }
   ]
})

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql Sequelize是在where子句中自动添加子查询,有没有办法让它跳过添加where子句

2条答案

相关问题

热门标签

最新问答