CODEWITHSUNDEEP
sqlnode.jspostgresqlsequelize.js
Mir Adnan
Mir Adnan

Reputation: 884

Sequelize is automatically adding a sub query within the where clause. Is there a way to make it skip adding the where clause

I have a Sequelize query that uses INNER JOINS. The issue is that sequelize is internally adding another where clause with a sub-query on the child table. That is eating up the query performance. Below are an examples of my code and the raw query output.

Is there a way to make sequelize skip adding this where clause?

Sequelize version: 6.x

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

The query builds an SQL query as below.

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;

As you can see the WHERE clause has a new added

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

This is basically killing the performance of the query.

Upvotes: 3

Views: 1816

Answers (2)

Abhishek Srivastava
Abhishek Srivastava

Reputation: 151

just ahead of Mir Adnan answer, it was correct but not working for Root query.

Just put outside of include . subQuery is equal to false.

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

Upvotes: 1

Mir Adnan
Mir Adnan

Reputation: 884

After a lot research I figured out the solution.

We need to add subQuery: false within the association.

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

Query output:

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;

Upvotes: 2

Related Questions