Pagination

Overview

Pagination divides large result sets into manageable pages. Core APIs use offset-based pagination with $num_rows (page size) and $offset (offset) parameters.

Benefits:

Reduces response size and transfer time
Improves perceived performance
Enables progressive loading
Prevents browser/client memory issues

Basic Pagination

$num_rows Parameter

Limits number of results returned:

GET /api/v4/core/PRD?source=productList&\
  $num_rows=25

Response: First 25 records only.

Default: If $num_rows not specified, default is 100 records.

Maximum: 1000 records per request (enforced by Core APIs).

$offset Parameter

Skips first N results:

GET /api/v4/core/PRD?source=productList&\
  $num_rows=25&\
  $offset=50

Response: Records 51-75 (skips first 50, returns next 25).

Pagination Formula

Calculate $offset from page number:

$offset = (pageNumber - 1) * pageSize

Examples:

Page	Page Size	$offset	$num_rows	Records Returned
1	25	0	25	1-25
2	25	25	25	26-50
3	25	50	25	51-75
10	25	225	25	226-250

Complete Pagination Example

Request Sequence

Page 1:

GET /api/v4/core/PRD?source=productList&\
  $num_rows=25&\
  $offset=0&\
  $order=XPRD01 ASC

Page 2:

GET /api/v4/core/PRD?source=productList&\
  $num_rows=25&\
  $offset=25&\
  $order=XPRD01 ASC

Page 3:

GET /api/v4/core/PRD?source=productList&\
  $num_rows=25&\
  $offset=50&\
  $order=XPRD01 ASC

Important: Always include same $order to ensure consistent pagination.

Getting Total Count

Count Total Records

Use $count parameter:

GET /api/v4/core/PRD?source=productList&\
  $count=true&\
  $num_rows=25

Response includes count:

{
  "data": [...],
  "count": 1250
}

Calculate total pages:

const totalPages = Math.ceil(count / pageSize);
// 1250 records / 25 per page = 50 pages

JavaScript Implementation

Paginator Class

class Paginator {
  constructor(apiBase, token, dimension, source) {
    this.apiBase = apiBase;
    this.token = token;
    this.dimension = dimension;
    this.source = source;
    this.pageSize = 25;
    this.currentPage = 1;
    this.totalCount = null;
  }

  async fetchPage(pageNumber, options = {}) {
    const skip = (pageNumber - 1) * this.pageSize;

    const url = new URL(`${this.apiBase}/api/v4/core/${this.dimension}`);
    url.searchParams.append('source', this.source);
    url.searchParams.append('$num_rows', this.pageSize);
    url.searchParams.append('$offset', skip);

    // Include count on first page
    if (pageNumber === 1) {
      url.searchParams.append('$count', 'true');
    }

    // Add sorting
    if (options.$order) {
      url.searchParams.append('$order', options.$order);
    }

    // Add filtering
    if (options.$filter) {
      url.searchParams.append('$filter', options.$filter);
    }

    // Add field selection
    if (options.$select) {
      url.searchParams.append('$select', options.$select);
    }

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    if (!response.ok) {
      throw new Error('Pagination request failed');
    }

    const result = await response.json();

    // Store total count from first page
    if (result.count !== undefined) {
      this.totalCount = result.count;
    }

    this.currentPage = pageNumber;

    return {
      data: result.data,
      page: pageNumber,
      pageSize: this.pageSize,
      totalCount: this.totalCount,
      totalPages: this.totalCount ? Math.ceil(this.totalCount / this.pageSize) : null,
      hasNext: this.totalCount ? (pageNumber * this.pageSize) < this.totalCount : true,
      hasPrevious: pageNumber > 1
    };
  }

  async nextPage(options = {}) {
    return this.fetchPage(this.currentPage + 1, options);
  }

  async previousPage(options = {}) {
    if (this.currentPage > 1) {
      return this.fetchPage(this.currentPage - 1, options);
    }
    throw new Error('Already on first page');
  }

  async firstPage(options = {}) {
    return this.fetchPage(1, options);
  }

  async lastPage(options = {}) {
    if (!this.totalCount) {
      throw new Error('Total count not available');
    }
    const lastPage = Math.ceil(this.totalCount / this.pageSize);
    return this.fetchPage(lastPage, options);
  }
}

// Usage
const paginator = new Paginator(apiBase, token, 'PRD', 'productList');

// Fetch first page
const page1 = await paginator.fetchPage(1, {
  $order: 'XPRD01 ASC',
  $select: 'PRD_ID,XPRD01,XPRD02'
});

console.log(`Page 1 of ${page1.totalPages}`);
console.log(`Showing records 1-${page1.data.length} of ${page1.totalCount}`);

// Fetch next page
const page2 = await paginator.nextPage({
  $order: 'XPRD01 ASC',
  $select: 'PRD_ID,XPRD01,XPRD02'
});

React Pagination Component

function ProductList() {
  const [products, setProducts] = useState([]);
  const [currentPage, setCurrentPage] = useState(1);
  const [totalPages, setTotalPages] = useState(0);
  const [loading, setLoading] = useState(false);
  const pageSize = 25;

  async function loadPage(pageNumber) {
    setLoading(true);
    try {
      const skip = (pageNumber - 1) * pageSize;
      const url = new URL(`${apiBase}/api/v4/core/PRD`);
      url.searchParams.append('source', 'productList');
      url.searchParams.append('$num_rows', pageSize);
      url.searchParams.append('$offset', skip);
      url.searchParams.append('$order', 'XPRD01 ASC');
      url.searchParams.append('$select', 'PRD_ID,XPRD01,XPRD02');

      if (pageNumber === 1) {
        url.searchParams.append('$count', 'true');
      }

      const response = await fetch(url, {
        headers: { 'Authorization': `Bearer ${token}` }
      });

      const result = await response.json();
      setProducts(result.data);
      setCurrentPage(pageNumber);

      if (result.count) {
        setTotalPages(Math.ceil(result.count / pageSize));
      }
    } finally {
      setLoading(false);
    }
  }

  useEffect(() => {
    loadPage(1);
  }, []);

  return (
    <div>
      {loading ? (
        <div>Loading...</div>
      ) : (
        <>
          <ul>
            {products.map(p => (
              <li key={p.PRD_ID}>{p.XPRD01} - ${p.XPRD02}</li>
            ))}
          </ul>

          <div className="pagination">
            <button
              disabled={currentPage === 1}
              onClick={() => loadPage(currentPage - 1)}
            >
              Previous
            </button>

            <span>Page {currentPage} of {totalPages}</span>

            <button
              disabled={currentPage === totalPages}
              onClick={() => loadPage(currentPage + 1)}
            >
              Next
            </button>
          </div>
        </>
      )}
    </div>
  );
}

Performance Considerations

Page Size Selection

Page Size	Use Case	Performance
10-25	Mobile apps, autocomplete	Fast, low bandwidth
25-50	Desktop list views	Good balance
50-100	Admin tables, data grids	Acceptable
100-500	Bulk operations, exports	Slower, use carefully
500-1000	Background processing only	Slow, avoid in UI

Deep Pagination Problem

Performance degrades with high $offset values:

# Fast - skipping 0 records
$offset=0&$num_rows=25

# Slower - skipping 10,000 records
$offset=10000&$num_rows=25

Why? Database must scan and skip 10,000 rows before returning results.

Solution: Limit maximum page number or use cursor-based pagination.

Consistent Sorting

Always specify $order for pagination:

# ✅ Good - consistent order
$num_rows=25&$offset=0&$order=XPRD03 ASC
$num_rows=25&$offset=25&$order=XPRD03 ASC

# ❌ Bad - unpredictable order
$num_rows=25&$offset=0  # No $order
$num_rows=25&$offset=25 # Results may duplicate or skip

Cursor-Based Pagination (Alternative)

Using ID Cursor

More efficient for deep pagination:

# Page 1 - get first 25
GET /api/v4/core/PRD?source=productList&\
  $num_rows=25&\
  $order=PRD_ID ASC

# Page 2 - get next 25 after last ID
GET /api/v4/core/PRD?source=productList&\
  $filter=PRD_ID gt 25&\
  $num_rows=25&\
  $order=PRD_ID ASC

Advantages:

Consistent results even if data changes
Better performance for deep pagination
No duplicate/missing records

Disadvantages:

Cannot jump to arbitrary page
More complex implementation

Best Practices

✅ DO:

Always use pagination for lists:

# ✅ Good
$num_rows=25&$offset=0

Include $order for consistency:

# ✅ Good
$num_rows=25&$offset=0&$order=XPRD01 ASC

Use $count on first page only:

// ✅ Good
if (page === 1) {
  url.searchParams.append('$count', 'true');
}

Limit page size for mobile:

# ✅ Good for mobile
$num_rows=20

Cache total count:

// ✅ Good - don't fetch count every page
if (!this.totalCount) {
  url.searchParams.append('$count', 'true');
}

❌ DON'T:

Don't fetch all records without pagination:

# ❌ Bad - fetches 10,000 records
GET /api/v4/core/PRD?source=productList

# ✅ Good
GET /api/v4/core/PRD?source=productList&$num_rows=25

Don't paginate without sorting:

# ❌ Bad - inconsistent results
$num_rows=25&$offset=25

# ✅ Good
$num_rows=25&$offset=25&$order=PRD_ID ASC

Don't use huge page sizes:

# ❌ Bad - slow, timeout risk
$num_rows=5000

# ✅ Good
$num_rows=100

Don't request count on every page:

# ❌ Bad - unnecessary overhead
$count=true  # on every page

# ✅ Good
$count=true  # only on page 1

Common Mistakes

Mistake 1: Forgetting to Calculate $offset

// ❌ Wrong - always $offset=0
function loadPage(page) {
  return fetch(`${apiBase}/api/v4/core/PRD?$num_rows=25&$offset=0`);
}

// ✅ Correct
function loadPage(page) {
  const skip = (page - 1) * 25;
  return fetch(`${apiBase}/api/v4/core/PRD?$num_rows=25&$offset=${skip}`);
}

Mistake 2: Inconsistent Sorting

// ❌ Wrong - different sort on each page
page1: $order=XPRD01 ASC
page2: $order=XPRD02 DESC  // Changed sort!

// ✅ Correct - same sort on all pages
page1: $order=XPRD01 ASC
page2: $order=XPRD01 ASC

Mistake 3: Not Handling Empty Pages

// ❌ Wrong - no empty check
const page = await fetchPage(100);
renderProducts(page.data); // Might be empty array

// ✅ Correct
const page = await fetchPage(100);
if (page.data.length === 0) {
  showEmptyState();
} else {
  renderProducts(page.data);
}

Summary

Pagination is essential for performance:

✅ Use $num_rows to limit page size (default 100, max 1000)
✅ Use $offset to offset results ($offset = (page - 1) * pageSize)
✅ Always include $order for consistent results
✅ Use $count=true on first page only
✅ Choose appropriate page size (25-50 for UI)
✅ Cache total count to avoid repeated queries

Key Takeaways:

Never fetch large datasets without pagination
Always sort for consistent page results
Calculate $offset correctly from page number
Limit page size for mobile apps (10-20)
Performance degrades with deep pagination (high $offset)

Next: Sorting Patterns → to learn advanced sorting strategies.

Basic Queries - Query fundamentals
Field Projection - Reducing response size
Query Performance - Performance optimization

Pagination

Overview​

Basic Pagination​

$num_rows Parameter​

$offset Parameter​

Pagination Formula​

Complete Pagination Example​

Request Sequence​

Getting Total Count​

Count Total Records​

JavaScript Implementation​

Paginator Class​

React Pagination Component​

Performance Considerations​

Page Size Selection​

Deep Pagination Problem​

Consistent Sorting​

Cursor-Based Pagination (Alternative)​

Using ID Cursor​

Best Practices​

✅ DO:​

❌ DON'T:​

Common Mistakes​

Mistake 1: Forgetting to Calculate $offset​

Mistake 2: Inconsistent Sorting​

Mistake 3: Not Handling Empty Pages​

Summary​

Related Concepts​