sequelize: Pagination Broken in SQL SERVER 2012 - Invalid usage of the option NEXT in the FETCH statement
I’m using Feathers with Sequelize / Tedious in SQL SERVER 2012 and the pagination is not working, I have another database but is SQL SERVER 2008 and there it works just fine.
Error: “Invalid usage of the option NEXT in the FETCH statement.”
Query (assembled by Sequelize):
SELECT [id], [email], [password], [createdAt], [updatedAt] FROM [users] AS [users] WHERE [users].[email] = N'someemail@gmail.com' OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
From what I gather looking at the MSSQL Docs is that the OFFSET should always be used with an ORDER BY clause but sequelize is not adding said clause in the pagination
I’m posting my entire dependencies just in case:
"dependencies": {
"axios": "0.15.3",
"body-parser": "^1.17.1",
"compression": "^1.6.2",
"cors": "^2.8.1",
"feathers": "^2.1.1",
"feathers-authentication": "^0.7.12",
"feathers-client": "1.9.0",
"feathers-configuration": "^0.3.3",
"feathers-errors": "^2.6.2",
"feathers-hooks": "^1.8.1",
"feathers-rest": "^1.7.1",
"feathers-sequelize": "^1.4.5",
"feathers-socketio": "^1.5.2",
"immutable": "3.8.1",
"lodash": "4.17.4",
"passport": "^0.3.2",
"react": "^15.4.2",
"react-dimensions": "1.3.0",
"react-dom": "15.4.2",
"react-redux": "5.0.2",
"react-router": "3.0.2",
"react-router-redux": "4.0.7",
"redux": "3.6.0",
"redux-actions": "1.2.1",
"redux-logger": "2.8.0",
"redux-saga": "0.14.3",
"semantic-ui-css": "2.2.4",
"semantic-ui-react": "0.64.7",
"sequelize": "^3.30.3",
"serve-favicon": "^2.4.2",
"socket.io-client": "1.7.2",
"tedious": "^1.15.0",
"winston": "^2.3.1"
}
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Comments: 17 (2 by maintainers)
Commits related to this issue
- Proposed fix for #7447 - order by missing https://github.com/sequelize/sequelize/issues/7447 — committed to realdubb/sequelize by realdubb 6 years ago
Still an issue 🤕
Using Sequelize 4.3.1 and Tedious 2.1.1
Sorry for the double post, but I wanted to mention a fix I saw on Stack Overflow for this issue. Looks like a simple change maybe. https://stackoverflow.com/questions/30632779/tedious-or-sequelize-uses-the-wrong-syntax-for-findone . The answer suggests modyfing the
query-generater.js
file in the mssql dialect folder by adding one line around line 900. Hopefully this will aid you in pushing this fix. Thanks so much for all your work on this project!