Explaining Pagination and Counts

Overview

When you can download a whole table at once, for example LearningPlanComponents, you might experience performance issues. An alternative is to identify the number of records, and then to page a table in the OData feed.

Find the count of the number of records in a table

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$count=true&$select=Id,ProgramId&$top=0

This returns a json where property @odata.count contains the count of records in the table.

Note that following the format specified at OData.org generates an error and returns no data: https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents/$count

Sample python code

import json
import requests

apiKey = "redacted"
url = "https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?" +
             "$count=true&$select=Id,ProgramId&$top=0"

headers = {'X-API-Token': apiKey}
response = requests.get(url, headers=headers)  
print( response.json() ) 

Sample response

{'@odata.context': 'https://clientdemo.aptem.co.uk/odata/1.0/$metadata#LearningPlanComponents(Id,ProgramId)',
 '@odata.count': 3622,
 'value': []}

Pagination of records from a table

Not all tables in Aptem's OData feed have server-side pagination enabled. However, by requesting data properly from server by providing specific parameters ('top' and 'skip'), you can access data in pages. This involves getting x number of records using $top and $skip then skipping a certain number of records and then getting the next x top records.

For example, calling the following URLs will retrieve four sets of records with indexes [1:1000, 1001:2000, 2001:3000, 3001:4000] respectively:

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=0

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=1000

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=2000

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=3000

https://demo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=4000

The client-side needs to make multiple calls changing the $top and $skip values, such that that $skip increases by the value of $top each iteration.

Sample python code:

import pandas as pd
import json
import requests 

def getOdata_by_n_records( apiKey, url = None, n = 500, verbose=False):

    # function to get odata query n records at a time, default = 500    
    # initialise counters and resulting dataframe
    i=0 ; res = True; cum_n = 0; j = 0
    
    df_collated = pd.DataFrame() # initialise dataframe
    
    while res != False:        
        if url is not None:
            urlr = url  + '&$top=' + str(n) + '&$skip=' + str(i)
        else:
            return

        headers = {'X-API-Token': apiKey}
        response = requests.get(urlr, headers=headers)        

        print(urlr)

        dfi = pd.DataFrame( response.json()['value'])         

        if  dfi.shape[0]>0:        # there is data
            # row bind new records that exist         
            df_collated = pd.concat([df_collated, dfi], axis=0)  

        if verbose:
            print('loop',  j, 'first record no.', i,  n, 'df size ', 
                   dfi.shape, 'current size df result',
                   df_collated.shape, cum_n )
        # update counters        
        cum_n = cum_n + dfi.shape[0] ; i = i + n;   j = j +1

        if dfi.shape[0] == 0:
            res = False  # this will stop loop

    return df_collated

Sample run

getOdata_by_n_records(  apiKey =k, 
                url = "https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?" + 
                       "$select=Id,ProgramId", 
                n=1000 , verbose= True) 

Sample output

https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=0
loop 0 first record no. 0 1000 df size  (1000, 2) current size df result (1000, 2) 0
https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=1000
loop 1 first record no. 1000 1000 df size  (1000, 2) current size df result (2000, 2) 1000
https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=2000
loop 2 first record no. 2000 1000 df size  (1000, 2) current size df result (3000, 2) 2000
https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=3000
loop 3 first record no. 3000 1000 df size  (622, 2) current size df result (3622, 2) 3000
https://clientdemo.aptem.co.uk/odata/1.0/LearningPlanComponents?$select=Id,ProgramId&$top=1000&$skip=4000
loop 4 first record no. 4000 1000 df size  (0, 0) current size df result (3622, 2) 3622