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

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:

• You can download the code for this sample in my GitHub repository at https://github.com/carlosfigueira/blogsamples/tree/master/AzureMobileServices/PagingOnCustomScripts
• The read script above uses a very poorly documented feature in the query object: the getComponents method; this will be the topic on an upcoming post on the node.js runtime.

Enjoy!

Comments

• Anonymous
  February 06, 2015
  How can we get data from 1 to many relationship table in a formatted way, for example I have tables:Search (Id, title)Criteria (Id, searchId, radius, latitude, longitude)Results (Id, searched, zip-code, city, state)And I want response to look like this:[  {     "Id":"A86E8DE3-36B0-4DBE-822E-326F84AF8FEE",     "title":"Untitled",     "criteria":{        "Id":"A86E8DE3-36B0-4DBE-822E-326F84GHFUI",        "searchId":"A86E8DE3-36B0-4DBE-822E-326F84AF8FEE",        "radius":5,        "latitude":31.2379068,        "longitude":-85.456757     },     "results":[        {           "Id":"A86E8DE3-36B0-4DBE-822E-326F8HKLLKJ",           "searchId":"A86E8DE3-36B0-4DBE-822E-326F84AF8FEE",           "zipcode":"36302",           "city":"Dothan",           "state":"Alabama"        }     ]  }]One ugly way would be to get search data and then execute two queries against each search one for criteria and one for result. Any other way. Also sometime we to need to increase timeout of Custom API request depending on the complexity of it. How to sort it out?
• Anonymous
  February 10, 2015
  So with SQL, if you want data from across multiple tables, you're pretty much limited with working with a query which returns the flattened data (rows of joined data) or doing post processing to get it in this format. It seems like, given the data you have above, a document DB is a better option for you, such as MongoDB.