Offset pagination with subquery in Sequelize

When we are using offset pagination and try to use LIMIT in our query, we could encounter some problems due to Sequelize specification. There are two methods to select multiple records, findAll and findAndCountAll where it’s possible to define limit property. If our query contains some subqueries, then in Sequelize the limit could be applied to our subqueries rather than the main query.

After checking code on the GitHub repository, we can find additional property subQuery not posted in the official documentation. If we add it to findAll parameters with false value, the limit and offset are placed at the end of the main query and not evaluated to the subquery. Now should be received well-paginated results.

Questionnaire.findAndCountAll({
      attributes: ["id", "name"],
      order: [["id", "DESC"]],
      include: [
       /* Some associations */
      ],
      offset: 5,
      limit: 5,
      subQuery: false,
    });

The downside of disabling subqueries optimisation is a worse performance because all queries are separated. In every single case, it is essential to evaluate usability and performance cost before using that approach.

Tweet