CODEWITHSUNDEEP
sqlsql-servernode.jssequelize.js
Jamie
Jamie

Reputation: 3071

Sequelize use ROW_NUMBER() instead of OFFSET and FETCH

I'm noticing a different behaviour with using (offset, fetch) and rownumber.

When I use row number it returns the result I expect.

So my sequelize query looks like this:

 ClanModel.findAll({
    order: [["Wins", "DESC"]],
    offset: page * 5,
    limit: 5
 })

SQL query starting from the the first row

SELECT 
    [clid] AS [ClanId], [Name], [Wins], [Losses], 
    [totalPoint] AS [TotalPoints] 
FROM 
    [Clan] AS [Clan] 
ORDER BY 
    [Clan].[Wins] DESC 
    OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

Results in:

[ { Name: 'Test6'},
  { Name: 'Test5'},
  { Name: 'Test4'},
  { Name: 'Test3'},
  { Name: 'Test2'} ]

SQL query after the 5th result:

SELECT 
    [clid] AS [ClanId], [Name], [Wins], [Losses],
    [totalPoint] AS [TotalPoints] 
FROM 
    [Clan] AS [Clan] 
ORDER BY 
    [Clan].[Wins] DESC 
    OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Results in:

[ { Name: 'Test5'},
  { Name: 'Test4'},
  { Name: 'Test3'},
  { Name: 'Test2'},
  { Name: 'Test1'} ]

Row number query:

 SELECT 
     [Name], [Wins], [Losses], [TotalPoints] AS [TotalPoints]
 FROM 
     (SELECT
          [clid] AS [ClanId], [Name], [Wins], [Losses],
          [totalPoint] AS [TotalPoints], 
          ROW_NUMBER() OVER (ORDER BY [Wins]) AS RowNum
      FROM 
          [Clan]) as Clan
 WHERE 
     Clan.RowNum BETWEEN 5 AND 10

Returns the results I expect:

Test5
Test6
Test7
Test8
Test9
Test10

My database records:

Test1
Test2
Test3
Test4
Test5
Test6
Test7
Test8
Test9
Test10

I'd like to use my sequelize model to get my data instead of using a plain sql query. But i can't find a way to use row number in sequelize.

Is there a way to still use sequelize to get my data?

Upvotes: 1

Views: 3954

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You are not showing the complete data. So, this is a bit of speculation: you have ties in the wins column. The sorting for order by is not stable and not even consistent, meaning that ties can come back in any order. So, fix this by including the name:

SELECT [clid] AS [ClanId], [Name], [Wins], [Losses], [totalPoint] AS [TotalPoints]
FROM [Clan] AS [Clan]
ORDER BY [Clan].[Wins] DESC, Clan.Name
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Assuming Name is unique, this will make the sort consistent. If this works in SQL, then it should be easy to implement in sequelize.

Upvotes: 1

Related Questions