Equivalent sequelize operation for given sql server query

Question

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER ( ORDER BY No_ ) AS RowNum
      FROM Item) DerivedTable
WHERE RowNum >= 501 AND RowNum <= 501 + ( 5 - 1 );

I think the older sql server versions do no support FETCH ROWS and NEXT ROWS which is equivalent to OFFSET and LIMIT in mysql, the above query seems the only way to apply that logic.

How can sequelize implement the above query, which creates a virtual table "DerivedTable" with a column "RowNum" that is used in the WHERE clause.

Is there any other method to do this in sequelize, maybe including raw query or anything else?

Sorry for the lack of information. I've added some more description. — Sujan, May 19 '16 at 09:22

score -1 · Answer 1 · edited May 23 '17 at 11:52

It seems you are not alone with this issue. With SQL Server 2012, you can just use:

Model
  .findAndCountAll({
     where: {
        title: {
          $like: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })
  .then(function(result) {
    console.log(result.count);
    console.log(result.rows);
  });

However since you are on an earlier version it seems you are stick with having to hand write the query.

Something like this:

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 myTableName)'+
        'select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage);';
sequelize.query(theQuery) 
 .spread(function(result) {
         console.log("Good old paginated results: ", result);
    });
});

see this and this

Equivalent sequelize operation for given sql server query

1 Answers1