Querying: Paging

Paging, or pagination, is the process of splitting a dataset into pages, reading one page at a time. This is useful for optimizing bandwidth traffic and hardware usage or simply because no user can handle huge amounts of data at once.

Example I - No Paging

The queries below will return all the results available.

const results = await session
    .query({ indexName: "Products/ByUnitsInStock" })
    .whereGreaterThan("UnitsInStock", 10)
    .all();

class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Products.Select(product => new {    
            UnitsInStock = product.UnitsInStock
        })`;
    }
}

Example II - Basic Paging

Let's assume that our page size is 10, and we want to retrieve the 3rd page. To do this, we need to issue following query:

const results = await session
    .query({ indexName: "Products_ByUnitsInStock" })
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20) // skip 2 pages worth of products
    .take(10) // take up to 10 products
    .all(); // execute query

class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Products.Select(product => new {    
            UnitsInStock = product.UnitsInStock
        })`;
    }
}

Finding the Total Results Count When Paging

While paging, you sometimes need to know the exact number of results returned from the query. The Client API supports this explicitly:

let stats;

const results = await session
    .query({ indexName: "Products/ByUnitsInStock" })
    .statistics(s => stats = s)
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20)
    .take(10)
    .all();

let totalResults = stats.totalResults;

class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Products.Select(product => new {    
            UnitsInStock = product.UnitsInStock
        })`;
    }
}

While the query will return with just 10 results, totalResults will hold the total number of matching documents.

Paging Through Tampered Results

For some queries, the server will skip over some results internally and invalidate the totalResults value. When executing a distinct query or index producing multiple index entries per document (a fanout index), then totalResults will contain the total count of matching documents found, but it will not take into account results that were skipped as a result of the distinct operator.

Whenever skippedResults is greater than 0 and a query involved some non-stored fields, it implies that we skipped over some results in the index.

In order to do proper paging in those scenarios, you should use skippedResults when telling RavenDB how many documents to skip. For each page, the starting point should be .skip((currentPage * pageSize) + skippedResults).

For example, let's page through all the results:

const PAGE_SIZE = 10;
let pageNumber = 0;
let skippedResults = 0;
let results;
let stats;

do {
    results = await session
        .query({ indexName: "Products/ByUnitsInStock" })
        .statistics(s => stats = s)
        .skip((pageNumber * PAGE_SIZE) + skippedResults)
        .take(PAGE_SIZE)
        .whereGreaterThan("UnitsInStock", 10)
        .distinct()
        .all();

    skippedResults += stats.skippedResults;
    pageNumber++;
} while (results.length > 0);

class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Products.Select(product => new {    
            UnitsInStock = product.UnitsInStock
        })`;
    }
}

const PAGE_SIZE = 10;
let pageNumber = 0;
let skippedResults = 0;
let results;
let stats;

do {
    results = await session
        .query({ indexName: "Order/ByOrderLines/ProductName" })
        .statistics(s => stats = s)
        .skip((pageNumber * PAGE_SIZE) + skippedResults)
        .take(PAGE_SIZE)
        .all();

    skippedResults += stats.skippedResults;
    pageNumber++;
} while (results.length > 0);

class Orders_ByOrderLines_ProductName extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Orders
            .SelectMany(
                order => order.Lines, 
                (order, line) => new {    
                    Product = line.ProductName 
                })`;
    }
}

The situation would be different if a distinct query and a projection applied to stored fields only. To get the correct results here, you shouldn't include skippedResults into the paging formula. Let's take a look at the example (note the usage of store method in the index definition):

const PAGE_SIZE = 10;
let pageNumber = 0;
let results;

do {
    results = await session
        .query({ indexName: "Orders/ByStoredProductName" })
        .selectFields("Product")
        .skip(pageNumber * PAGE_SIZE)
        .take(PAGE_SIZE)
        .distinct()
        .all();

    pageNumber++;
} while (results.length > 0);

class Orders_ByStoredProductName extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Orders.SelectMany(
            order => order.Lines, 
            (order, line) => new {    
                Product = line.ProductName
            })`;

        this.store("Product", "Yes");
    }
}