Sorting and Paging Tutorial

Onyx Database is capable of sorting and paging. This tutorial demonstrates how to order your results, limit the number of results, and specify the starting index of your search. As an example, we will retrieve a list of all Player entities in the database, order the results by lastName and firstName, and retrieve the first and second pages of results.

Steps to Implement Sorting and Paging (Onyx Cloud Database)

1

Create a Query with Sorting and Paging

Use the QueryBuilder syntax to create a query, specify the sort order, and set paging parameters.
1const query = db.select('lastName', 'firstName')
2    .from('Player')
3    .orderBy(asc('lastName'), asc('firstName'))
4    .limit(10);
2

Query for the First Page

Execute the query to retrieve the first page of results.
1const page1 = await query.list();
3

Print the First Page Results

Iterate over the results and print them out.
1console.log("\nPage 1:");
2
3for (const player of page1) {
4    console.log(`${player.lastName}, ${player.firstName}`);
5}

The output will look like this:

Page 1: 
Abdullah, Ameer Ajayi, Jay Allen, Javorius Allen, Kamar
Allen, Keenan Amendola, Danny Anderson, C.J.
Andrews, Antonio Austin, Tavon Baldwin, Doug
4

Set Paging to Retrieve the Second Page

Update the query to retrieve the next set of results.
1const nextPageToken = page1.nextPage;
2query.nextPage(nextPageToken);
5

Query for the Second Page

Execute the query to retrieve the second page of results.
1const page2 = await query.list();
6

Print the Second Page Results

Iterate over the results and print them out.
1console.log("\nPage 2:");
2
3for (const player of page2) {
4    console.log(`${player.lastName}, ${player.firstName}`);
5}

The output will look like this:

Page 2: Beasley, Cole Beckham Jr., Odell Bell, Joique
Bell, Le'Veon Benjamin, Travis Bernard, Giovani
Blount, LeGarrette Blue, Alfred Boldin, Anquan
Bortles, Blake
Next Tutorial: Lazy Query

Important Notes

  • The firstRow property is zero-indexed. Setting it to 0 retrieves the first row.
  • The maxResults property limits the number of results returned in the query.
  • Use the QueryOrder class to specify the sort order of your results.
  • For better performance, consider using indexed attributes for sorting.

Troubleshooting

  • No Results Returned: Ensure that your query criteria are correct and that there are entities in the database matching your query.
  • Incorrect Sorting: Verify that the attributes you are sorting by exist and are correctly specified in your QueryOrder objects.
  • Paging Issues: Double-check your firstRow and maxResults settings to ensure they correctly represent the desired page.

Next Steps

Now that you know how to implement sorting and paging, you can explore more advanced querying techniques: