How to Solve Pagination and Filter Issue in Sequelize?

November 4th, 2020 . 4 minutes read
Blog featured image

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Sequelize is a powerful tool that deals with these databases nicely, but there are certain limitations that one can encounter while working with it. One such limitation is the issue encountered using pagination and filter queries.

What’s the Problem?

Let’s see the issue –

Consider the below function getProductOrderDetails, to get the product’s order details for a given order (orderId:e188cd13-452b-d1f5-1715-d6fa0e6cf950):

When we execute this function to get the desired results, it fails and shows the below-given error:

It is because of the below code block where we have used where condition (filter) along with limit and offset (pagination).

Ideally, there should not be a problem here and the query must work fine, but that is not the case here. The reason being SQL generated by Sequelize internally is not correct, as it applies the filter condition on the parent table (inside subquery).

So, if we apply a filter on a column other than the parent table (in this case products), it fails. Below is the SQL generated internally by Sequelize:

The Solution?

In order to fix this issue, we need to use QueryGenerator. We will generate a part of the SQL first using the generateSql function, after removing the filter, pagination attributes from the condition object. This function looks like below:

Next, we have changed our getProductOrderDetails function as below:

As we can see, generateSql function is using QueryGenerator to create the SQL. We have called it baseQuery.

Next, we have defined two variables countQuery and productIdQuery to store the SQL queries for getting total count of records and first 10 product Ids respectively (which is the value of limit).

We have stored the result of these 2 queries in variables count and data.

From data we have got productIds to fetch as per the limit (which is 10 currently):

After that, we have added our original condition along with another for productIds in our new condition object.

Now as we have got the count separately, we don’t have to use findAndCountAll function instead we will use findAll to get the rows. The final query that is going to be executed is as follows:

As we can see, this is the correct SQL and will work without any errors.

Bottom Line

Hope you have now understood how to solve pagination and filter issues in Sequelize.

We would be glad to hear your response about this blog. Please drop your comments in the comment box.
Thanks for Reading!!

Author: Naved Akhtar