Enabling paging (with total count) for tables with custom read scripts in the node.js runtime

  • Article

Quick post today. When we want to display a large number of rows from a table to the user, it’s often impractical to load all of the data at once. Loading it all may strain the device memory, or it may not even be possible to request all of the rows at once (the node.js runtime has a hard limit of 1000 rows that can be requested at a time). In those scenarios, paging is the solution, where the client asks for a certain range of rows and as the user performs some action (such as scrolling) it can load additional rows that can be displayed. We have many tutorials for all supported client platforms which introduce the concept of paging in mobile services. The tutorials also mention that the client can also request, in the response, the total number of items (total count) which would have been returned in case the paging parameters had not being specified – it’s very useful when we want to display some sort of progress bar (or scroll bar) that display the position of the “window” of the data in relation to all the data the client can see.

  1. var table = MobileService.GetTable<TodoItem>();
  2. var items = await table
  3.     .Take(PageSize)
  4.     .Skip(this.currentIndex)
  5.     .IncludeTotalCount()
  6.     .ToListAsync();
  7. this.lstItems.ItemsSource = items;
  8. int totalCount = (int)((ITotalCountProvider)items).TotalCount;
  9. this.lblStatus.Text = string.Format("Showing items from position {0} to {1} (of {2})", currentIndex, currentIndex + PageSize - 1, totalCount);

This works great when we’re talking to simple tables, but in scenarios where we want to customize the read script, there are cases where the “total count” information is lost, as a user already hit this issue in StackOverflow. For example, if I implemented the read script as shown below, the ‘totalCount’ variable in the client code shown above would not be set (i.e., it would have a value of –1).

  1. function read(query, user, request) {
  2.     var table = tables.current;
  3.     var queryComponents = query.getComponents();
  4.     var take = queryComponents.take;
  5.     var skip = queryComponents.skip;
  6.     var sql = 'SELECT id, text, complete FROM ' + table.getTableName();
  7.     sql = sql + ' ORDER BY text';
  8.     sql = sql + ' OFFSET ' + skip + ' ROWS ';
  9.     sql = sql + ' FETCH NEXT ' + take + ' ROWS ONLY';
  10.     console.log('sql: ' + sql);
  11.     mssql.query(sql, {
  12.         success: function (results) {
  13.             request.respond(200, results);
  14.         }
  15.     });
  16. }

The problem is that since we’re taking over the response, we’re not sending it in the format the client expects to retrieve the total count information. The best way to find out what the client expects is to look into a network capture tool such as Fiddler and see what is going on the wire. For those of you who don’t want to go there, here’s what’s going on: for responses without total count information, the server simply returns the results as an array:

 [
    {"id":"first id","text":"Buy bread","complete":false},
    {"id":"second id","text":"Buy milk","complete":true},
    {"id":"third id","text":"Buy chocolate","complete":false},
]

But if total count is requested, the server wraps the actual response in an object which contains the total count in addition to the results themselves:

 {
    "count":500,
    "results":[
        {"id":"first id","text":"Buy bread","complete":false},
        {"id":"second id","text":"Buy milk","complete":true},
        {"id":"third id","text":"Buy chocolate","complete":false},
    ]
}

But our custom script is calling ‘request.respond’, which takes over the complete response. What it needs to do then is to send the response in the format expected by the client. We can do that by tweaking our read script a little – first by adding an explicit call to retrieve the total count (which we didn’t have before), then changing the success handler of the mssql.query call to account for the two responses.

  1. function read(query, user, request) {
  2.     var table = tables.current;
  3.     var queryComponents = query.getComponents();
  4.     var take = queryComponents.take;
  5.     var skip = queryComponents.skip;
  6.     var sql = 'SELECT id, text, complete FROM ' + table.getTableName();
  7.     sql = sql + ' ORDER BY text';
  8.     sql = sql + ' OFFSET ' + skip + ' ROWS ';
  9.     sql = sql + ' FETCH NEXT ' + take + ' ROWS ONLY';
  10.  
  11.     // Adding the total count
  12.     sql = sql + '; SELECT COUNT(*) as [count] FROM ' + table.getTableName();
  13.  
  14.     console.log('sql: ' + sql);
  15.  
  16.     // Notice that there are two statements in the sql; that means that the
  17.     // callback to mssql.query will be called twice. Let's use some captured
  18.     // variables to identify when all responses have arrived so that we avoid
  19.     // calling 'request.respond' more than once.
  20.     var resultWithTotalCount = {};
  21.     var mssqlCallbackCount = 0;
  22.     mssql.query(sql, {
  23.         success: function (results) {
  24.             console.log('result: ', results);
  25.             if (++mssqlCallbackCount == 1) {
  26.                 // Result of first select
  27.                 resultWithTotalCount.results = results;
  28.             } else {
  29.                 // Result of the 'SELECT COUNT(*)'
  30.                 resultWithTotalCount.count = results[0].count;
  31.                 request.respond(200, resultWithTotalCount);
  32.             }
  33.         }
  34.     });
  35. }

Now the client can correctly retrieve the total count for the custom query.

A few more notes on this post:

Enjoy!