Skip and take doesn't work with join
Problem
Issue type: [ ] question [x] bug report [ ] feature request [ ] documentation issue Database system/driver: [ ] `cordova` [ ] `mongodb` [ ] `mssql` [ ] `mysql` / `mariadb` [ ] `oracle` [x] `postgres` [ ] `cockroachdb` [ ] `sqlite` [ ] `sqljs` [ ] `react-native` [ ] `expo` TypeORM version: [ ] `latest` [ ] `@next` [x] `0.2.18` (or put your version here) Hi I have 7 row in my table and it's my first query. First Query [code block] First test for First query It's my query log for just one request (my resolver dosn't have any additional middleware) with skip: 1 and take: 2 [code block] but i got my whole 7 rows and it seem two query executed!!! In above query log, in first log LIMIT and OFFSET clause exist but in the second query they don't exist. Second test for First query It's my second log for skip: 7 and take: 2 [code block] As i expected, result was empty and just one query executed (i have just 7 row in my table) LIMIT and OFFSET clause exist in query log. Second Query I changed my query to just retrieve rows without any join (relations). [code block] First test for Second query It's my log with skip: 1 and take: 2 [code block] Everything works properly and i got expected rows. Conclusion I can only say it's strange behavior.
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Skip and Take Behavior with Joins in TypeORM
The issue arises from how TypeORM constructs SQL queries with joins. When using 'skip' and 'take' in conjunction with joins, TypeORM may not apply the LIMIT and OFFSET clauses correctly due to the complexity of the generated SQL, leading to unexpected results. This behavior is particularly evident in version 0.2.18, where the ORM does not handle pagination correctly with joined queries.
Awaiting Verification
Be the first to verify this fix
- 1
Upgrade TypeORM
Upgrade TypeORM to the latest version where this issue has been addressed. Newer versions have improved handling of pagination with joins.
bashnpm install typeorm@latest - 2
Refactor Query to Use Subqueries
If upgrading is not feasible, refactor your query to use subqueries instead of direct joins. This can help TypeORM apply LIMIT and OFFSET correctly.
typescriptconst subQuery = await getRepository(Entity) .createQueryBuilder('entity') .skip(1) .take(2); const results = await getRepository(AnotherEntity) .createQueryBuilder('anotherEntity') .where('anotherEntity.id IN (:...ids)', { ids: subQuery.getMany() }) .getMany(); - 3
Test with Different Pagination Values
After making changes, test the query with various skip and take values to ensure that the pagination works correctly with the expected results.
typescriptconst testResults = await getRepository(AnotherEntity) .createQueryBuilder('anotherEntity') .skip(1) .take(2) .getMany(); - 4
Review Query Logs
Check the query logs to confirm that the LIMIT and OFFSET clauses are present in the SQL generated by TypeORM after the changes.
typescriptconsole.log(queryLog); - 5
Document Changes
Update your project's documentation to reflect the changes made to the queries and any necessary migration steps for other developers.
Validation
Confirm the fix by running the modified queries and verifying that the correct number of rows are returned as expected. Check the query logs to ensure LIMIT and OFFSET are applied correctly.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep