Pagination

hypequery provides robust support for cursor-based pagination, allowing you to efficiently navigate through large datasets without loading all records at once.

Cursor-Based Pagination

Unlike traditional offset-based pagination, cursor-based pagination uses a cursor (a pointer to a specific record) to determine where to start fetching the next set of results. This approach is more efficient and reliable, especially for large datasets.

import { createQueryBuilder } from '@hypequery/clickhouse';

const builder = createQueryBuilder(schema).table('users');

// Get the first page
const firstPage = await builder.paginate({
  pageSize: 10,                                  // Number of records per page
  orderBy: [{ column: 'id', direction: 'ASC' }] // Must specify an order
});

// Get the next page using the end cursor from the first page
const nextPage = await builder.paginate({
  pageSize: 10,
  after: firstPage.pageInfo.endCursor,           // Use the end cursor to fetch next page
  orderBy: [{ column: 'id', direction: 'ASC' }]
});

// Get the previous page
const prevPage = await builder.paginate({
  pageSize: 10,
  before: firstPage.pageInfo.startCursor,        // Use the start cursor to fetch previous page
  orderBy: [{ column: 'id', direction: 'ASC' }]
});

Pagination Methods

hypequery provides several methods for pagination:

paginate()

The main pagination method that returns a page of results along with pagination metadata:

const page = await builder
  .table('products')
  .select(['id', 'name', 'price'])
  .where('active', 'eq', 1)
  .paginate({
    pageSize: 20,
    orderBy: [{ column: 'created_at', direction: 'DESC' }]
  });

// Access the page data
page.data.forEach(product => {
  console.log(product.name, product.price);
});

// Access pagination metadata
console.log(`Has next page: ${page.pageInfo.hasNextPage}`);
console.log(`Has previous page: ${page.pageInfo.hasPreviousPage}`);

firstPage()

A convenience method to get the first page with a specified page size:

const firstPage = await builder
  .table('orders')
  .select(['id', 'total', 'status'])
  .firstPage(20);

iteratePages()

An async generator that allows you to iterate through all pages efficiently:

const iterator = builder
  .table('events')
  .select(['id', 'event_type', 'timestamp'])
  .iteratePages(100);

// Process each page
for await (const page of iterator) {
  console.log(`Processing page with ${page.data.length} events`);
  // Process the page data
  page.data.forEach(event => {
    // Handle each event
  });
}

Return Types

PaginatedResult

The pagination methods return a PaginatedResult object with the following structure:

interface PaginatedResult<T> {
  data: T[];                // The actual data for the current page
  pageInfo: PageInfo;       // Pagination metadata
}

interface PageInfo {
  startCursor: string;      // Cursor pointing to the first item in the current page
  endCursor: string;        // Cursor pointing to the last item in the current page
  hasNextPage: boolean;     // Whether there are more pages after the current page
  hasPreviousPage: boolean; // Whether there are pages before the current page
}

Pagination Options

When calling the paginate() method, you can provide the following options:

interface PaginationOptions<T> {
  pageSize: number;                            // Number of records per page
  after?: string;                              // Cursor for forward pagination
  before?: string;                             // Cursor for backward pagination
  orderBy?: Array<{                            // Required for cursor-based pagination
    column: keyof T;                           // The column to order by
    direction: 'ASC' | 'DESC';                 // The sort direction
  }>;
}

Best Practices

1. Always specify orderBy: Cursor-based pagination requires a consistent ordering to work properly.

2. Store cursors for navigation: Save the startCursor and endCursor values to allow users to navigate back and forth.

3. Use iteratePages() for batch processing: When you need to process all records, use the iteratePages() method for efficient iteration.

4. Limit page size: Keep page sizes reasonable (20-100 items) for optimal performance.

5. Use appropriate order direction: For newest-first listings, use DESC ordering on timestamp or ID fields.

Limitations

1. Order is required: You must specify at least one ordering column for cursor-based pagination.

2. Changing order affects cursors: Cursors are tied to specific ordering, changing the order will invalidate existing cursors.

3. Consistent schema: The columns used for ordering should be consistent across all records.

Example: User Interface Pagination

Here’s a complete example of implementing pagination in a user interface:

import { useState } from 'react';
import { createQueryBuilder } from '@hypequery/clickhouse';

function UserList() {
  const [currentPage, setCurrentPage] = useState(null);
  const [isLoading, setIsLoading] = useState(false);
  
  const loadFirstPage = async () => {
    setIsLoading(true);
    try {
      const builder = createQueryBuilder(schema).table('users');
      const page = await builder
        .select(['id', 'name', 'email'])
        .where('active', 'eq', 1)
        .firstPage(20);
      setCurrentPage(page);
    } finally {
      setIsLoading(false);
    }
  };
  
  const loadNextPage = async () => {
    if (!currentPage || !currentPage.pageInfo.hasNextPage) return;
    
    setIsLoading(true);
    try {
      const builder = createQueryBuilder(schema).table('users');
      const page = await builder
        .select(['id', 'name', 'email'])
        .where('active', 'eq', 1)
        .paginate({
          pageSize: 20,
          after: currentPage.pageInfo.endCursor,
          orderBy: [{ column: 'id', direction: 'ASC' }]
        });
      setCurrentPage(page);
    } finally {
      setIsLoading(false);
    }
  };
  
  const loadPreviousPage = async () => {
    if (!currentPage || !currentPage.pageInfo.hasPreviousPage) return;
    
    setIsLoading(true);
    try {
      const builder = createQueryBuilder(schema).table('users');
      const page = await builder
        .select(['id', 'name', 'email'])
        .where('active', 'eq', 1)
        .paginate({
          pageSize: 20,
          before: currentPage.pageInfo.startCursor,
          orderBy: [{ column: 'id', direction: 'ASC' }]
        });
      setCurrentPage(page);
    } finally {
      setIsLoading(false);
    }
  };
  
  // Rest of component...
}

See Also

• Streaming
• Query Building
• Filtering

Advanced Pagination Patterns

For more complex pagination scenarios, you can combine multiple techniques:

import { createQueryBuilder } from '@hypequery/clickhouse';

// ... existing code ...