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

6qqygrtg  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(2)|浏览(127)

我有一个使用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:*'))

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

s4chpxco

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;
m1m5dgzv

m1m5dgzv2#

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

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

相关问题