> ## 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.

# findMany

> List queries and pagination

`findMany` is a method that retrieves multiple records matching conditions. It supports pagination, search, filtering, and sorting, returning results along with the total count.

<Info>
  `findMany` is not defined in BaseModelClass. It's a **standard pattern** automatically generated
  by the Syncer in each Model class when you create an Entity.
</Info>

## Type Signature

```typescript theme={null}
async findMany<T extends SubsetKey, LP extends ListParams>(
  subset: T,
  params?: LP,
): Promise<ListResult<LP, SubsetMapping[T]>>
```

## Auto-Generated Code

Sonamu automatically generates the following code based on your Entity:

```typescript theme={null}
// src/application/user/user.model.ts (auto-generated)
class UserModelClass extends BaseModelClass {
  @api({ httpMethod: "GET", clients: ["axios", "tanstack-query"], resourceName: "Users" })
  async findMany<T extends UserSubsetKey, LP extends UserListParams>(
    subset: T,
    rawParams?: LP,
  ): Promise<ListResult<LP, UserSubsetMapping[T]>> {
    // 1. Set default values
    const params = {
      num: 24,
      page: 1,
      search: "id" as const,
      orderBy: "id-desc" as const,
      ...rawParams,
    } satisfies UserListParams;

    // 2. Get subset query builder
    const { qb } = this.getSubsetQueries(subset);

    // 3. Add filtering conditions
    if (params.id) {
      qb.whereIn("users.id", asArray(params.id));
    }

    // 4. Search conditions
    if (params.search && params.keyword) {
      if (params.search === "id") {
        qb.where("users.id", Number(params.keyword));
      } else if (params.search === "email") {
        qb.where("users.email", "like", `%${params.keyword}%`);
      }
    }

    // 5. Sorting
    if (params.orderBy === "id-desc") {
      qb.orderBy("users.id", "desc");
    }

    // 6. Create enhancers
    const enhancers = this.createEnhancers({
      A: (row) => ({ ...row }),
      B: (row) => ({ ...row }),
      // ... Calculate virtual fields per subset
    });

    // 7. Execute query
    return this.executeSubsetQuery({
      subset,
      qb,
      params,
      enhancers,
      debug: false,
    });
  }
}
```

**How it works:**

1. **getSubsetQueries()**: Gets subset query builder from BaseModelClass method
2. **Add conditions**: Add WHERE, search, and sorting conditions
3. **createEnhancers()**: Creates virtual field calculation functions from BaseModelClass method
4. **executeSubsetQuery()**: Executes actual query from BaseModelClass method
   * COUNT query (total)
   * SELECT query (rows)
   * Loader execution (load relationship data)
   * Hydrate (flat → nested objects)
   * Apply enhancers (calculate virtual fields)

## Parameters

### subset

Specifies the subset of data to retrieve.

**Type:** `SubsetKey` (e.g., `"A"`, `"B"`, `"C"`)

```typescript theme={null}
// Fetch basic info only
const { rows } = await UserModel.findMany("A", { num: 10, page: 1 });

// Include relationship data
const { rows } = await UserModel.findMany("B", { num: 10, page: 1 });
```

### params

Query conditions and pagination settings.

**Type:** `ListParams` (optional)

```typescript theme={null}
type UserListParams = {
  // Pagination
  num?: number; // Items per page (default: 24)
  page?: number; // Page number (default: 1)

  // Filtering
  id?: number | number[]; // ID filter
  status?: "active" | "inactive"; // Custom filter

  // Search
  search?: "id" | "email" | "name"; // Search field
  keyword?: string; // Search keyword

  // Sorting
  orderBy?: "id-desc" | "created-desc"; // Sort order

  // Query mode
  queryMode?: "list" | "count" | "both"; // Query mode

  // Semantic search (vector search)
  semanticQuery?: Record<string, unknown>; // Semantic search parameters
};
```

#### Default Values

```typescript theme={null}
const params = {
  num: 24,
  page: 1,
  search: "id",
  orderBy: "id-desc",
  ...rawParams, // Overwrite with user input
};
```

## Return Value

**Type:** `Promise<ListResult<LP, SubsetMapping[T]>>`

```typescript theme={null}
// When semanticQuery is provided, a similarity field is added to each row.
type WithSimilarity<LP, T> = LP extends { semanticQuery: Record<string, unknown> }
  ? T & { similarity: number }
  : T;

type ListResult<LP, T> =
  // queryMode: "list"
  | { rows: WithSimilarity<LP, T>[] }
  // queryMode: "count"
  | { total: number }
  // queryMode: "both" (default)
  | { rows: WithSimilarity<LP, T>[]; total: number };
```

When `semanticQuery` is provided, a `similarity` field (a score between 0 and 1) is automatically added to each row.

```typescript theme={null}
// Normal query
const { rows } = await PostModel.findMany("A", { num: 10 });
// rows[0].id, rows[0].title  (no similarity)

// Semantic search
const { rows } = await PostModel.findMany("A", {
  num: 10,
  semanticQuery: { embedding: queryVector },
});
// rows[0].id, rows[0].title, rows[0].similarity  (similarity included)
```

### rows

Array of retrieved records.

```typescript theme={null}
const { rows } = await UserModel.findMany("A", { num: 10, page: 1 });

rows.forEach((user) => {
  console.log(user.id, user.name);
});
```

### total

Total number of records matching the conditions.

```typescript theme={null}
const { total } = await UserModel.findMany("A", { num: 10, page: 1 });

const totalPages = Math.ceil(total / 10);
console.log(`Total pages: ${totalPages}`);
```

## Pagination

### num (Items per page)

Number of records to display per page.

**Default:** `24`

```typescript theme={null}
// Fetch 10 at a time
const { rows } = await UserModel.findMany("A", { num: 10 });

// Fetch 50 at a time
const { rows } = await UserModel.findMany("A", { num: 50 });

// Fetch all (num: 0)
const { rows } = await UserModel.findMany("A", { num: 0 });
```

### page (Page number)

Page number to fetch (starts from 1).

**Default:** `1`

```typescript theme={null}
// First page
const { rows: page1 } = await UserModel.findMany("A", {
  num: 10,
  page: 1, // Records 1-10
});

// Second page
const { rows: page2 } = await UserModel.findMany("A", {
  num: 10,
  page: 2, // Records 11-20
});

// Third page
const { rows: page3 } = await UserModel.findMany("A", {
  num: 10,
  page: 3, // Records 21-30
});
```

## Filtering

### ID Filter

```typescript theme={null}
// Single ID
const { rows } = await UserModel.findMany("A", { id: 1 });

// Multiple IDs
const { rows } = await UserModel.findMany("A", { id: [1, 2, 3] });
```

### Custom Filters

Additional filters available depending on Entity definition.

```typescript theme={null}
// Status filter
const { rows } = await UserModel.findMany("A", {
  status: "active",
});

// Date range filter
const { rows } = await UserModel.findMany("A", {
  created_from: "2024-01-01",
  created_to: "2024-12-31",
});

// Combine multiple filters
const { rows } = await UserModel.findMany("A", {
  status: "active",
  role: "admin",
  verified: true,
});
```

## Search

### search + keyword

```typescript theme={null}
// Search by ID
const { rows } = await UserModel.findMany("A", {
  search: "id",
  keyword: "123",
});

// Search by email
const { rows } = await UserModel.findMany("A", {
  search: "email",
  keyword: "john",
});

// Search by name (partial match)
const { rows } = await UserModel.findMany("A", {
  search: "name",
  keyword: "Smith",
});
```

### Implementation Example

Generated Model code:

```typescript theme={null}
// search-keyword
if (params.search && params.keyword && params.keyword.length > 0) {
  if (params.search === "id") {
    qb.where("users.id", Number(params.keyword));
  } else if (params.search === "email") {
    qb.where("users.email", "like", `%${params.keyword}%`);
  } else if (params.search === "name") {
    qb.where("users.name", "like", `%${params.keyword}%`);
  } else {
    throw new BadRequestException(`Unimplemented search field ${params.search}`);
  }
}
```

## Sorting

### orderBy

```typescript theme={null}
// ID descending (default)
const { rows } = await UserModel.findMany("A", {
  orderBy: "id-desc",
});

// Created date descending
const { rows } = await UserModel.findMany("A", {
  orderBy: "created-desc",
});

// Name ascending
const { rows } = await UserModel.findMany("A", {
  orderBy: "name-asc",
});
```

### Implementation Example

```typescript theme={null}
// orderBy
if (params.orderBy) {
  if (params.orderBy === "id-desc") {
    qb.orderBy("users.id", "desc");
  } else if (params.orderBy === "created-desc") {
    qb.orderBy("users.created_at", "desc");
  } else if (params.orderBy === "name-asc") {
    qb.orderBy("users.name", "asc");
  } else {
    exhaustive(params.orderBy);
  }
}
```

## Query Mode

### queryMode: "both" (default)

Returns both list and total count.

```typescript theme={null}
const { rows, total } = await UserModel.findMany("A", {
  num: 10,
  page: 1,
  queryMode: "both", // Default value
});

console.log(`${rows.length} of ${total}`);
```

### queryMode: "list"

Returns list only (skips COUNT query).

```typescript theme={null}
const { rows } = await UserModel.findMany("A", {
  num: 10,
  page: 1,
  queryMode: "list", // No total
});

// total is undefined
```

<Info>Use `queryMode: "list"` for performance improvement when COUNT query is heavy.</Info>

### queryMode: "count"

Returns total count only (skips SELECT query).

```typescript theme={null}
const { total } = await UserModel.findMany("A", {
  status: "active",
  queryMode: "count", // No rows
});

console.log(`Total active users: ${total}`);
```

## Basic Usage

### Simple List

```typescript theme={null}
import { UserModel } from "./user/user.model";

class UserService {
  async getUsers(page: number = 1) {
    const { rows, total } = await UserModel.findMany("A", {
      num: 20,
      page,
    });

    return {
      users: rows,
      total,
      page,
      totalPages: Math.ceil(total / 20),
    };
  }
}
```

### Filtering + Pagination

```typescript theme={null}
async getActiveUsers(page: number = 1) {
  const { rows, total } = await UserModel.findMany("A", {
    status: "active",
    num: 20,
    page
  });

  return { rows, total };
}
```

### Search + Sorting

```typescript theme={null}
async searchUsers(keyword: string) {
  const { rows } = await UserModel.findMany("A", {
    search: "name",
    keyword,
    orderBy: "name-asc",
    num: 50
  });

  return rows;
}
```

## Practical Examples

<Tabs>
  <Tab title="Infinite Scroll" icon="arrows-down-to-line">
    ```typescript theme={null}
    import { UserModel } from "./user/user.model";
    import { api } from "sonamu";

    class UserFrame {
      @api({ httpMethod: "GET" })
      async getUsersInfinite(page: number = 1) {
        // Fetch list only (skip COUNT for performance)
        const { rows } = await UserModel.findMany("A", {
          num: 20,
          page,
          queryMode: "list",
          orderBy: "created-desc"
        });

        return {
          users: rows,
          hasMore: rows.length === 20,  // Has next page if 20 items
          nextPage: page + 1
        };
      }
    }

    // Usage in React
    import { useInfiniteQuery } from "@tanstack/react-query";

    function UserList() {
      const {
        data,
        fetchNextPage,
        hasNextPage,
      } = useInfiniteQuery({
        queryKey: ["users"],
        queryFn: ({ pageParam = 1 }) =>
          UserService.getUsersInfinite(pageParam),
        getNextPageParam: (lastPage) =>
          lastPage.hasMore ? lastPage.nextPage : undefined,
      });

      return (
        <div>
          {data?.pages.map((page) =>
            page.users.map((user) => (
              <UserCard key={user.id} user={user} />
            ))
          )}
          {hasNextPage && (
            <button onClick={() => fetchNextPage()}>
              Load More
            </button>
          )}
        </div>
      );
    }
    ```
  </Tab>

  <Tab title="Data Table" icon="table">
    ```typescript theme={null}
    import { UserModel } from "./user/user.model";
    import { api } from "sonamu";

    class UserFrame {
      @api({ httpMethod: "GET" })
      async getUserTable(params: {
        page: number;
        pageSize: number;
        search?: string;
        keyword?: string;
        orderBy?: string;
      }) {
        const { rows, total } = await UserModel.findMany("A", {
          num: params.pageSize,
          page: params.page,
          search: params.search as any,
          keyword: params.keyword,
          orderBy: params.orderBy as any
        });

        return {
          data: rows,
          total,
          page: params.page,
          pageSize: params.pageSize,
          totalPages: Math.ceil(total / params.pageSize)
        };
      }
    }

    // React table
    function UserTable() {
      const [page, setPage] = useState(1);
      const [pageSize, setPageSize] = useState(20);
      const [search, setSearch] = useState("name");
      const [keyword, setKeyword] = useState("");

      const { data, isLoading } = useQuery({
        queryKey: ["users", page, pageSize, search, keyword],
        queryFn: () => UserService.getUserTable({
          page,
          pageSize,
          search,
          keyword
        })
      });

      return (
        <div>
          <input
            value={keyword}
            onChange={(e) => setKeyword(e.target.value)}
            placeholder="Search users..."
          />

          <table>
            <thead>
              <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
              </tr>
            </thead>
            <tbody>
              {data?.data.map(user => (
                <tr key={user.id}>
                  <td>{user.id}</td>
                  <td>{user.name}</td>
                  <td>{user.email}</td>
                </tr>
              ))}
            </tbody>
          </table>

          <div>
            Page {data?.page} of {data?.totalPages}
            <button
              onClick={() => setPage(p => p - 1)}
              disabled={page === 1}
            >
              Previous
            </button>
            <button
              onClick={() => setPage(p => p + 1)}
              disabled={page === data?.totalPages}
            >
              Next
            </button>
          </div>
        </div>
      );
    }
    ```
  </Tab>

  <Tab title="Statistics & Count" icon="chart-bar">
    ```typescript theme={null}
    import { UserModel, OrderModel } from "./models";
    import { api } from "sonamu";

    class DashboardFrame {
      @api({ httpMethod: "GET" })
      async getDashboardStats() {
        // Fetch COUNT only (fast statistics)
        const [
          { total: totalUsers },
          { total: activeUsers },
          { total: todayOrders },
          { total: pendingOrders }
        ] = await Promise.all([
          UserModel.findMany("A", { queryMode: "count" }),
          UserModel.findMany("A", {
            status: "active",
            queryMode: "count"
          }),
          OrderModel.findMany("A", {
            created_from: new Date().toISOString().split('T')[0],
            queryMode: "count"
          }),
          OrderModel.findMany("A", {
            status: "pending",
            queryMode: "count"
          })
        ]);

        return {
          totalUsers,
          activeUsers,
          todayOrders,
          pendingOrders,
          activeUserRate: (activeUsers / totalUsers * 100).toFixed(1)
        };
      }
    }
    ```
  </Tab>

  <Tab title="Export" icon="file-export">
    ```typescript theme={null}
    import { UserModel } from "./user/user.model";
    import { api } from "sonamu";
    import { Workbook } from "@sheetkit/node";

    class ExportFrame {
      @api({ httpMethod: "GET" })
      async exportUsers(filters: {
        status?: string;
        created_from?: string;
        created_to?: string;
      }) {
        // Fetch all data (num: 0)
        const { rows } = await UserModel.findMany("Export", {
          ...filters,
          num: 0,  // All
          orderBy: "id-asc"
        });

        // Create Excel
        const wb = new Workbook();
        const sheet = wb.sheetNames[0]; // "Sheet1"

        // Set headers
        wb.setCellValue(sheet, "A1", "ID");
        wb.setCellValue(sheet, "B1", "Name");
        wb.setCellValue(sheet, "C1", "Email");
        wb.setCellValue(sheet, "D1", "Status");
        wb.setCellValue(sheet, "E1", "Created");

        // Add data rows
        rows.forEach((user, index) => {
          const rowNum = index + 2; // Start from row 2 (row 1 is header)
          wb.setCellValue(sheet, `A${rowNum}`, user.id);
          wb.setCellValue(sheet, `B${rowNum}`, user.name);
          wb.setCellValue(sheet, `C${rowNum}`, user.email);
          wb.setCellValue(sheet, `D${rowNum}`, user.status);
          wb.setCellValue(sheet, `E${rowNum}`, user.created_at);
        });

        const buffer = await wb.writeBuffer();

        return {
          filename: `users_${Date.now()}.xlsx`,
          data: buffer.toString("base64")
        };
      }
    }
    ```
  </Tab>
</Tabs>

## Using BaseModelClass Methods

`findMany` internally uses the following BaseModelClass methods:

### getSubsetQueries()

Gets subset query builder.

```typescript theme={null}
const { qb } = this.getSubsetQueries(subset);
```

### createEnhancers()

Creates virtual field calculation functions.

```typescript theme={null}
const enhancers = this.createEnhancers({
  A: (row) => ({
    ...row,
    full_name: `${row.first_name} ${row.last_name}`,
  }),
});
```

### executeSubsetQuery()

Executes query and returns results.

```typescript theme={null}
return this.executeSubsetQuery({
  subset,
  qb,
  params,
  enhancers,
  debug: false,
  optimizeCountQuery: false,
});
```

## Type Safety

### Subset Type Inference

```typescript theme={null}
// Subset A
const { rows: usersA } = await UserModel.findMany("A", {});
usersA[0].posts; // ❌ Type error

// Subset B (includes posts)
const { rows: usersB } = await UserModel.findMany("B", {});
usersB[0].posts; // ✅ Post[]
```

### ListParams Type

```typescript theme={null}
// params is limited to UserListParams type
await UserModel.findMany("A", {
  unknownField: true, // ❌ Type error
});

await UserModel.findMany("A", {
  status: "active", // ✅ Defined field
});
```

### queryMode Type Inference

```typescript theme={null}
// queryMode: "both"
const { rows, total } = await UserModel.findMany("A", {
  queryMode: "both",
});
rows; // ✅ User[]
total; // ✅ number

// queryMode: "list"
const { rows } = await UserModel.findMany("A", {
  queryMode: "list",
});
total; // ❌ Type error (no total)

// queryMode: "count"
const { total } = await UserModel.findMany("A", {
  queryMode: "count",
});
rows; // ❌ Type error (no rows)
```

## Performance Optimization

### 1. Use queryMode: "list"

When COUNT query is heavy:

```typescript theme={null}
// ❌ Slow: executes COUNT(*)
const { rows, total } = await UserModel.findMany("A", {
  num: 20,
  page: 1,
});

// ✅ Fast: skips COUNT
const { rows } = await UserModel.findMany("A", {
  num: 20,
  page: 1,
  queryMode: "list",
});
```

### 2. Use Indexes

Add indexes on frequently filtered fields:

```sql theme={null}
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_created_at ON users(created_at);
```

### 3. Minimize Subsets

Use subsets that include only necessary fields:

```typescript theme={null}
// ❌ Heavy: loads all relationships
const { rows } = await UserModel.findMany("Full", { num: 100 });

// ✅ Light: basic fields only
const { rows } = await UserModel.findMany("A", { num: 100 });
```

### 4. Limit num

Prevent fetching too much data at once:

```typescript theme={null}
// ❌ Dangerous: possible memory exhaustion
const { rows } = await UserModel.findMany("A", { num: 0 });

// ✅ Safe: appropriate page size
const { rows } = await UserModel.findMany("A", { num: 100 });
```

## Cautions

### 1. Caution with num: 0

`num: 0` fetches all records, use carefully.

```typescript theme={null}
// ❌ Dangerous: can fetch millions of records
const { rows } = await UserModel.findMany("A", { num: 0 });

// ✅ Safe: pagination
const { rows } = await UserModel.findMany("A", { num: 100 });
```

### 2. page Starts from 1

```typescript theme={null}
// ❌ Wrong: starts from 0
const { rows } = await UserModel.findMany("A", { page: 0 });

// ✅ Correct: starts from 1
const { rows } = await UserModel.findMany("A", { page: 1 });
```

### 3. Subset Selection

```typescript theme={null}
// ❌ Inefficient: loads unnecessary relationships
const { rows } = await UserModel.findMany("Full", { num: 1000 });

// ✅ Efficient: only what's needed
const { rows } = await UserModel.findMany("A", { num: 1000 });
```

## Next Steps

<CardGroup cols={2}>
  <Card title="findById" icon="magnifying-glass" href="/en/api-reference/base-model/find-by-id">
    Retrieve single record by ID
  </Card>

  <Card title="save" icon="floppy-disk" href="/en/api-reference/base-model/save">
    Save and update records
  </Card>

  <Card title="Subset" icon="layer-group" href="/en/database/subsets/what-are-subsets">
    Understanding subsets
  </Card>

  <Card title="Puri Query Builder" icon="code" href="/en/database/puri/basic-queries">
    Advanced query building
  </Card>
</CardGroup>