findMany - 🌲 Sonamu

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

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.

Type Signature

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:

// 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:

getSubsetQueries(): Gets subset query builder from BaseModelClass method
Add conditions: Add WHERE, search, and sorting conditions
createEnhancers(): Creates virtual field calculation functions from BaseModelClass method
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")

// 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)

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

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

Return Value

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

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

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

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.

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

// 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

// 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

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

// 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

// 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:

// 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

// 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

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

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

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

// total is undefined

Use queryMode: "list" for performance improvement when COUNT query is heavy.

queryMode: “count”

Returns total count only (skips SELECT query).

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

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

Basic Usage

Simple List

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

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

  return { rows, total };
}

Search + Sorting

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

  return rows;
}

Practical Examples

Infinite Scroll
Data Table
Statistics & Count
Export

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>
  );
}

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>
  );
}

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)
    };
  }
}

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")
    };
  }
}

Using BaseModelClass Methods

findMany internally uses the following BaseModelClass methods:

getSubsetQueries()

Gets subset query builder.

const { qb } = this.getSubsetQueries(subset);

createEnhancers()

Creates virtual field calculation functions.

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

executeSubsetQuery()

Executes query and returns results.

return this.executeSubsetQuery({
  subset,
  qb,
  params,
  enhancers,
  debug: false,
  optimizeCountQuery: false,
});

Type Safety

Subset Type Inference

// 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

// 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

// 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:

// ❌ 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:

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:

// ❌ 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:

// ❌ 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.

// ❌ 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

// ❌ 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

// ❌ 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

findById

Retrieve single record by ID

save

Save and update records

Subset

Understanding subsets

Puri Query Builder

Advanced query building

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Type Signature

​Auto-Generated Code

​Parameters

​subset

​params

​Default Values

​Return Value

​rows

​total

​Pagination

​num (Items per page)

​page (Page number)

​Filtering

​ID Filter

​Custom Filters

​Search

​search + keyword

​Implementation Example

​Sorting

​orderBy

​Implementation Example

​Query Mode

​queryMode: “both” (default)

​queryMode: “list”

​queryMode: “count”

​Basic Usage

​Simple List

​Filtering + Pagination

​Search + Sorting

​Practical Examples

​Using BaseModelClass Methods

​getSubsetQueries()

​createEnhancers()

​executeSubsetQuery()

​Type Safety

​Subset Type Inference

​ListParams Type

​queryMode Type Inference

​Performance Optimization

​1. Use queryMode: “list”

​2. Use Indexes

​3. Minimize Subsets

​4. Limit num

​Cautions

​1. Caution with num: 0

​2. page Starts from 1

​3. Subset Selection

​Next Steps

findById

save

Subset

Puri Query Builder

Type Signature

Auto-Generated Code

Parameters

subset

params

Default Values

Return Value

rows

total

Pagination

num (Items per page)

page (Page number)

Filtering

ID Filter

Custom Filters

Search

search + keyword

Implementation Example

Sorting

orderBy

Implementation Example

Query Mode

queryMode: “both” (default)

queryMode: “list”

queryMode: “count”

Basic Usage

Simple List

Filtering + Pagination

Search + Sorting

Practical Examples

Using BaseModelClass Methods

getSubsetQueries()

createEnhancers()

executeSubsetQuery()

Type Safety

Subset Type Inference

ListParams Type

queryMode Type Inference

Performance Optimization

1. Use queryMode: “list”

2. Use Indexes

3. Minimize Subsets

4. Limit num

Cautions

1. Caution with num: 0

2. page Starts from 1

3. Subset Selection

Next Steps