我有一个使用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:*'))
这基本上会降低查询的性能。
2条答案
按热度按时间s4chpxco1#
经过大量的研究,我找到了解决方案。
我们需要在关联中添加
subQuery: false
。查询输出:
m1m5dgzv2#
就在Mir Adnan回答之前,它是正确的,但不适用于根查询。
只是放在include之外。subQuery等于false。