Sequelize Pagination with Eager Loading and subQuery Configuration

Recommended Configuration for Pagination Accuracy

To maintain pagination accuracy when eager loading one-to-many (1:M) associations, the recommended configuration is to keep subQuery: true (the default behavior). This ensures that the limit and offset are applied to the primary model's records before the join occurs, preventing the "missing records" phenomenon where a single primary entity with multiple associated rows consumes multiple slots of the limit.

Comparing subQuery Strategies

Configuration	SQL Behavior	Pagination Result	Performance Impact
subQuery: true	Nested SELECT for primary keys, then JOIN.	Accurate (Limit applies to main model).	Higher overhead on massive datasets.
subQuery: false	Single flat JOIN query.	Inaccurate for 1:M (Limit applies to joined rows).	Generally faster execution.

When to Use separate: true

The separate: true option should be preferred over subQuery adjustments when you encounter significant performance degradation caused by complex nested queries or when dealing with very large datasets where a single massive JOIN is inefficient.

Setting separate: true instructs Sequelize to execute the association loading in a completely separate SQL query. This is ideal under the following conditions:

• One-to-Many Associations: It is only available for 1:M or M:N associations.
• Large Result Sets: When the subquery generated by subQuery: true becomes too slow due to the volume of primary keys being passed to the outer query.
• Avoiding Cartesian Products: When eager loading multiple 1:M associations simultaneously, which can lead to an exponential increase in the number of returned rows.

Implementation Example

const users = await User.findAndCountAll({
  limit: 10,
  offset: 0,
  include: [{
    model: Post,
    separate: true // Executes a separate query: SELECT * FROM Posts WHERE userId IN (...) 
  }]
});

Verification Steps

To verify your configuration is behaving as expected:

1. Check SQL Logs: Enable logging to confirm if Sequelize is producing a nested SELECT (subquery) or two distinct queries (separate).
2. Validate Array Length: Compare results.length against your limit value. If results.length < limit despite having enough data, subQuery: false is likely the cause.
3. Verify Count: When using findAndCountAll, ensure the count property reflects the number of primary entities, not the total number of joined rows.

Diagnostic Note: To provide a more specific optimization, please specify if you are eager loading multiple 1:M associations in a single call, as this significantly changes the performance profile of subQuery.