> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# limit / offset

> Limit and paginate query results

`limit` and `offset` are methods for limiting the number of query results and implementing pagination. These are essential methods for performance optimization and implementing paging functionality.

## Basic Usage

### limit

Limits the maximum number of results to return.

```typescript theme={null}
const users = await puri.table("users").select({ id: "users.id", name: "users.name" }).limit(10);

// SELECT users.id, users.name FROM users LIMIT 10
```

### offset

Skips a specified number of results.

```typescript theme={null}
const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .offset(20)
  .limit(10);

// SELECT users.id, users.name FROM users LIMIT 10 OFFSET 20
```

<Info>`offset` is typically used with `limit` for pagination.</Info>

## Pagination

### Basic Pagination

```typescript theme={null}
// Page 1: Items 1-10
const page1 = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(0);

// Page 2: Items 11-20
const page2 = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(10);

// Page 3: Items 21-30
const page3 = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(20);
```

### Pagination Function

```typescript theme={null}
function paginate(page: number, perPage: number) {
  return {
    limit: perPage,
    offset: (page - 1) * perPage,
  };
}

// Get page 3 with 20 items per page
const { limit, offset } = paginate(3, 20);

const posts = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(limit)
  .offset(offset);

// Returns items 41-60
```

## Cursor-Based Pagination

Cursor-based pagination is more efficient for large datasets than offset-based pagination.

### Basic Cursor Pagination

```typescript theme={null}
// First page: Latest 10 posts
const firstPage = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .orderBy("posts.created_at", "desc")
  .limit(10);

// Get last item's cursor
const lastPost = firstPage[firstPage.length - 1];
const cursor = lastPost.created_at;

// Next page: 10 items after cursor
const nextPage = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .where("posts.created_at", "<", cursor)
  .orderBy("posts.created_at", "desc")
  .limit(10);
```

### Cursor Pagination with ID

```typescript theme={null}
// First page
const firstPage = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .orderBy("users.id", "asc")
  .limit(20);

// Next page cursor
const lastUserId = firstPage[firstPage.length - 1].id;

// Next page
const nextPage = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .where("users.id", ">", lastUserId)
  .orderBy("users.id", "asc")
  .limit(20);
```

## Practical Examples

<Tabs>
  <Tab title="Infinite Scroll" icon="arrows-down-to-line">
    ```typescript theme={null}
    // Component state
    let lastCursor: Date | null = null;
    let allPosts: Post[] = [];

    async function loadMore() {
      let query = puri.table("posts")
        .select({
          id: "posts.id",
          title: "posts.title",
          created_at: "posts.created_at"
        })
        .orderBy("posts.created_at", "desc")
        .limit(20);

      // Add cursor condition after first load
      if (lastCursor) {
        query = query.where("posts.created_at", "<", lastCursor);
      }

      const posts = await query;

      // Update state
      allPosts = [...allPosts, ...posts];
      lastCursor = posts[posts.length - 1]?.created_at || null;

      return posts;
    }

    // Load first page
    await loadMore();

    // Load on scroll
    window.addEventListener("scroll", async () => {
      if (isNearBottom()) {
        await loadMore();
      }
    });
    ```
  </Tab>

  <Tab title="Data Table" icon="table">
    ```typescript theme={null}
    interface PaginationParams {
      page: number;      // Current page (1-indexed)
      perPage: number;   // Items per page
    }

    async function getUsers(params: PaginationParams) {
      const { page, perPage } = params;
      const offset = (page - 1) * perPage;

      // Get data
      const users = await puri.table("users")
        .select({
          id: "users.id",
          name: "users.name",
          email: "users.email",
          created_at: "users.created_at"
        })
        .orderBy("users.created_at", "desc")
        .limit(perPage)
        .offset(offset);

      // Get total count
      const { count } = await puri.table("users")
        .select({ count: Puri.count() })
        .first();

      return {
        data: users,
        pagination: {
          page,
          perPage,
          total: count,
          totalPages: Math.ceil(count / perPage)
        }
      };
    }

    // Example usage
    const result = await getUsers({ page: 2, perPage: 25 });
    console.log(result);
    // {
    //   data: [...25 users...],
    //   pagination: { page: 2, perPage: 25, total: 1000, totalPages: 40 }
    // }
    ```
  </Tab>

  <Tab title="Batch Processing" icon="layer-group">
    ```typescript theme={null}
    async function processAllUsers() {
      const batchSize = 100;
      let offset = 0;
      let hasMore = true;

      while (hasMore) {
        // Fetch batch
        const users = await puri.table("users")
          .select({ id: "users.id", email: "users.email" })
          .limit(batchSize)
          .offset(offset);

        // Process batch
        for (const user of users) {
          await sendEmail(user.email);
        }

        // Check if more data exists
        hasMore = users.length === batchSize;
        offset += batchSize;

        console.log(`Processed ${offset} users`);
      }
    }

    // With cursor for better performance
    async function processAllUsersCursor() {
      const batchSize = 100;
      let lastId = 0;
      let hasMore = true;

      while (hasMore) {
        const users = await puri.table("users")
          .select({ id: "users.id", email: "users.email" })
          .where("users.id", ">", lastId)
          .orderBy("users.id", "asc")
          .limit(batchSize);

        for (const user of users) {
          await sendEmail(user.email);
        }

        hasMore = users.length === batchSize;
        lastId = users[users.length - 1]?.id || lastId;
      }
    }
    ```
  </Tab>

  <Tab title="Latest Items" icon="clock">
    ```typescript theme={null}
    // Latest 10 posts
    const latestPosts = await puri.table("posts")
      .select({
        id: "posts.id",
        title: "posts.title",
        created_at: "posts.created_at"
      })
      .orderBy("posts.created_at", "desc")
      .limit(10);

    // Top 5 bestsellers
    const topProducts = await puri.table("products")
      .select({
        id: "products.id",
        name: "products.name",
        sales: "products.sales"
      })
      .orderBy("products.sales", "desc")
      .limit(5);

    // Recently active users
    const activeUsers = await puri.table("users")
      .select({
        id: "users.id",
        name: "users.name",
        last_active: "users.last_active"
      })
      .orderBy("users.last_active", "desc")
      .limit(20);
    ```
  </Tab>
</Tabs>

## Performance Optimization

### 1. offset is Slow for Large Datasets

```typescript theme={null}
// ❌ Bad: Offset scans all skipped rows
await puri.table("posts").limit(10).offset(10000); // Scans 10,000 rows then skips them

// ✅ Good: Use cursor-based pagination
await puri.table("posts").where("posts.id", ">", lastId).limit(10); // Directly finds target rows
```

### 2. Use with orderBy

```typescript theme={null}
// ⚠️ Caution: Results may be inconsistent without ordering
await puri.table("users").limit(10).offset(20);

// ✅ Good: Guarantee consistent ordering
await puri.table("users").orderBy("users.id", "asc").limit(10).offset(20);
```

### 3. Optimize COUNT Queries

```typescript theme={null}
// ❌ Bad: Separate queries for data and count
const users = await puri.table("users").limit(10).offset(20);

const { count } = await puri.table("users").select({ count: Puri.count() }).first();

// ✅ Good: Use findMany that combines both
const { rows, total } = await UserModel.findMany("A", {
  page: 3,
  num: 10,
});
// One query returns both data and total
```

## offset vs Cursor

### offset Method

**Advantages:**

* Simple implementation
* Can jump to any page
* Suitable for numbered pages

**Disadvantages:**

* Slow for large offsets
* Inconsistent results when data changes
* Always scans from beginning

```typescript theme={null}
// offset: Suitable for page numbers
<button onClick={() => goToPage(5)}>Page 5</button>
```

### Cursor Method

**Advantages:**

* Fast regardless of position
* Consistent results
* Efficient memory usage

**Disadvantages:**

* Cannot jump to arbitrary pages
* Slightly complex implementation
* Cursor management needed

```typescript theme={null}
// Cursor: Suitable for infinite scroll
<button onClick={() => loadMore()}>Load More</button>
```

## Cautions

### 1. Missing orderBy

```typescript theme={null}
// ❌ Dangerous: Inconsistent order
await puri.table("users").limit(10);

// ✅ Safe: Explicit ordering
await puri.table("users").orderBy("users.id", "asc").limit(10);
```

### 2. Large offset

```typescript theme={null}
// ❌ Very slow
await puri.table("posts").limit(10).offset(1000000);

// ✅ Fast
await puri.table("posts").where("posts.id", ">", lastId).limit(10);
```

### 3. limit Without WHERE

```typescript theme={null}
// ⚠️ Caution: Returns ALL data without limit
const allUsers = await puri.table("users").selectAll();

// ✅ Safe: Always use limit
const users = await puri.table("users").selectAll().limit(1000);
```

### 4. Pagination and Data Changes

```typescript theme={null}
// Problem: Items may appear twice or be skipped
// when data changes between page loads

// Solution 1: Use cursor pagination
await puri.table("posts").where("posts.id", ">", lastId).limit(10);

// Solution 2: Use timestamp
await puri.table("posts").where("posts.created_at", "<=", snapshotTime).limit(10).offset(20);
```

## Type Safety

Type safety is maintained when using limit and offset.

```typescript theme={null}
const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .limit(10)
  .offset(20);

// Type: Array<{ id: number; name: string }>
users[0].id; // number
users[0].name; // string
```

## Next Steps

<CardGroup cols={2}>
  <Card title="order-by" icon="arrow-down-1-9" href="/en/api-reference/puri-methods/order-by">
    Sort results
  </Card>

  <Card title="where" icon="filter" href="/en/api-reference/puri-methods/where">
    Filter with conditions
  </Card>

  <Card title="select" icon="list-check" href="/en/api-reference/puri-methods/select">
    Select fields to query
  </Card>

  <Card title="advanced-methods" icon="wand-magic-sparkles" href="/en/api-reference/puri-methods/advanced-methods">
    Advanced query methods
  </Card>
</CardGroup>