在SQL 2008上使用Sequelize.js进行分页(不支持FETCH) [英] Paginate with Sequelize.js at SQL 2008 (that does not support FETCH)

问题描述

因此，我将sequelize.js与tedious一起用于mssql支持. MSSQL = SQL Server 2008

嗯，所以有一个名为Clientes的大表.我必须从中获取数据，但是它非常庞大，所以我必须对它们进行分页

我尝试了正确的方法"，如文档中所示:

app.use('/clientes', function(req, res){
      var page = req.params.page || 0;
      sequelize.Clientes.findAll({limit: 30, offset: 5  }) 
       .then(function(result) {
      res.json({result: result});
    });
});

它应该可以工作，但是它使用类似这样的查询

SELECT .......... from [Clientes] as [Clientes] ORDER BY [CNPJ] OFFSET 5 ROWS FETCH NEXT 30 ROWS ONLY;和SQL Server返回可怕的Invalid usage of the option NEXT in the FETCH statement！

因此，经过大量搜索，我发现显然FETCH不能与SQL 2008一起使用.尽管如此(很可悲)，我试图将TDS版本强制为7_3_A.我使用

dialectOptions: {
      tdsVersion: '7_3_A'
},

但是查询sintax并没有改变:(

我不确定该怎么做....我什至会使用另一种方式来代替分页...我发现了一个很好的SQL分页(涉及到row_number())的sintax，但是我却无法执行将其作为对sequelyze的原始查询. myModel.query('SQL QUERY HERE')返回了未定义的函数oO'

任何帮助将不胜感激

解决方案

好，所以经过大量搜索和调试，我发现了两件事.

1)我的续集实例是sequelize.sequelize，这是由于module.exports带有lodash所致.我的糟糕，我不记得了，甚至在问题中都没有提及.对此我感到抱歉.

2)offset和limit确实使用了fetch，因此会为SQL 2008生成无效的SQL语法.

我必须使用sequelize.sequelize.query()执行原始查询(因为我的实例是sequelize.sequelize).

在在此答案中找到了用于mssql 2008中分页的查询:

这是我的完整代码，包括查询的明确路由和丑陋格式:

var express = require('express')
var app = express();

app.use('/tableName', function(req, res){
    var page = req.params.page || 2;
    var rowsPerPage = req.params.perpage || 30;

    if(rowsPerPage > 100){ 
        rowsPerPage = 100; //this limits how many per page
    }

    var theQuery = 'declare @rowsPerPage as bigint; '+
        'declare @pageNum as bigint;'+
        'set @rowsPerPage='+rowsPerPage+'; '+
        'set @pageNum='+page+';   '+
        'With SQLPaging As   ( '+
        'Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) '+
        'as resultNum, * '+
        'FROM tableName )'+
        'select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage);';


    sequelize.sequelize.query(theQuery) 
     .spread(function(result) {
        res.json({result: result});
      });
});

如果您(和我一样)想知道为什么sequelize.sequelize而不是仅sequelize?

好吧，这是因为var sequelize是具有以下module.exports的文件中的require():

 module.exports = lodash.extend({
  sequelize: sequelize, //reffers to new Sequelize(db,user,pass,{});
  Sequelize: Sequelize
 }, db)

所以这就是为什么只返回sequelize.query()到undefined并且sequelize.sequelize.query()可以正常工作的原因，因为实际上我的sequelize变量是一个对象，其sequelize属性是真正的sequelize连接实例.

So, I'm using sequelize.js with tediousfor mssql support. MSSQL = SQL Server 2008

Well, So there is this big table named Clientes. I have to fetch data from it, but it is quite huge so I'll have to paginate trhough

I tried the 'right way', as in the docs:

app.use('/clientes', function(req, res){
      var page = req.params.page || 0;
      sequelize.Clientes.findAll({limit: 30, offset: 5  }) 
       .then(function(result) {
      res.json({result: result});
    });
});

It should work, BUT it uses a query that is something like

SELECT .......... from [Clientes] as [Clientes] ORDER BY [CNPJ] OFFSET 5 ROWS FETCH NEXT 30 ROWS ONLY; and the SQL Server return a terrible Invalid usage of the option NEXT in the FETCH statement!

So, after lots of search, I got to a point that apparently FETCH will not work with SQL 2008. That being said (and sad) I tried to force TDS version to 7_3_A. I did this using

dialectOptions: {
      tdsVersion: '7_3_A'
},

but the query sintax did not change with this :(

I'm not sure what to do anymore.... I even will use another way instead of paginate... I found a nice sintax to SQL pagination (involving row_number()) , but I was not able to perform it as a raw query on sequelyze. myModel.query('SQL QUERY HERE') returned undefined function oO'

Any helps will be appreciated

解决方案

Ok, so after lots of searching and debugging, I found 2 things.

1) My sequelize instance was sequelize.sequelize, due to a module.exports with lodash. My bad, I did not remember that, and did not even mention it in the question. I'm sorry about this.

2) The offset and limit do use fetch and thus generates invalid SQL sintax for SQL 2008.

I had to perform a raw query, using sequelize.sequelize.query() (since my instance was sequelize.sequelize).

The query used for pagination in mssql 2008 was found In this answer:

here's my full code, including express routing and ugly format for the query:

var express = require('express')
var app = express();

app.use('/tableName', function(req, res){
    var page = req.params.page || 2;
    var rowsPerPage = req.params.perpage || 30;

    if(rowsPerPage > 100){ 
        rowsPerPage = 100; //this limits how many per page
    }

    var theQuery = 'declare @rowsPerPage as bigint; '+
        'declare @pageNum as bigint;'+
        'set @rowsPerPage='+rowsPerPage+'; '+
        'set @pageNum='+page+';   '+
        'With SQLPaging As   ( '+
        'Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) '+
        'as resultNum, * '+
        'FROM tableName )'+
        'select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage);';


    sequelize.sequelize.query(theQuery) 
     .spread(function(result) {
        res.json({result: result});
      });
});

And if you (as I did) wonder why sequelize.sequelize instead of only sequelize?

Well, this is because var sequelize is a require() from a file with the following module.exports:

 module.exports = lodash.extend({
  sequelize: sequelize, //reffers to new Sequelize(db,user,pass,{});
  Sequelize: Sequelize
 }, db)

So this is why just sequelize.query() was returned undefined, and sequelize.sequelize.query() works fine, because in true my sequelize variable is an Object with the sequelize property being the real sequelize connection instance.

这篇关于在SQL 2008上使用Sequelize.js进行分页(不支持FETCH)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！