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

​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