Lucid: SQL Server error with pagination

[juhguu]

Using SQL Server, I have this message when I try to get results after the first page.

"select * from [model] offset @p0 rows fetch next @p1 rows only - Invalid usage of the option next in the FETCH statement."

MSSQL npm package version

4.1.0

Package version

Lucid: 6.1.2

Node.js and npm version

node: 10.6.0, npm: 6.1.0

Sample Code (to reproduce the issue)

await Model.query().paginate(2)

[juhguu]

Apparently this is a KnexJS bug with SQL Server. Paging only works correctly when we include an orderBy clause in the query.

Example: await Model.query().orderBy('created_at', 'desc').paginate(request.get().page || 1, 20)

[thetutlage]

Can you link to any issue created on Knexjs for same?

[juhguu]

The only issue related to this bug is this: https://github.com/tgriesser/knex/issues/1527

[thetutlage]

I believe the issue is pretty clear that older versions of MSSQL are not supported by knex and their is unfortunately no simple way to fix this is Lucid

[juhguu]

I have this error using SQL Server 2017 on Linux. xD https://hub.docker.com/r/microsoft/mssql-server-linux/

[thetutlage]

@juhguu I suggest opening a parallel issue with Knex team too. Since this is where it will be fixed.

However, when I will find time, I will post my findings on the same issue

[thetutlage]

Closing, since the issue comes directly from knex and unfortunately there is nothing much we can do.