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

# Using Subsets

> Using Subsets in Model

Subsets are used through Model methods.

## Model Methods and Subsets

### findById - Single Record Query

```typescript theme={null}
// Single Subset
const user = await UserModel.findById(1, ["P"]);
// Type: UserSubsetMapping["P"]

// Multiple Subsets (Union)
const user = await UserModel.findById(1, ["A", "P"]);
// Type: UserSubsetMapping["A"] | UserSubsetMapping["P"]
```

### findOne - Single Record by Condition

```typescript theme={null}
const user = await UserModel.findOne({
  where: [["email", "john@test.com"]],
  subsetKey: "P",
});
// Type: UserSubsetMapping["P"] | undefined
```

### findMany - Multiple Records Query

```typescript theme={null}
const users = await UserModel.findMany({
  listParams: {
    page: 1,
    pageSize: 20,
  },
  subsetKey: "L",
});
// Type: UserSubsetMapping["L"][]
```

## Starting Subset Queries with getSubsetQueries

Use `getSubsetQueries` to start a Subset-based query:

```typescript theme={null}
// Get query builder for Subset P
const { qb, onSubset } = UserModel.getSubsetQueries("P");

// Apply additional conditions
qb.where("users.role", "admin")
  .where("users.is_verified", true)
  .orderBy("users.created_at", "desc");

// Execute with executeSubsetQuery
const result = await UserModel.executeSubsetQuery({
  subset: "P",
  qb,
  params: { num: 20, page: 1 },
});
// Type: ListResult<{ num: number; page: number }, UserSubsetMapping["P"]>
```

<Info>
  `getSubsetQueries("P")` returns a query builder (`qb`) with the JOIN and SELECT defined in Subset
  P automatically applied.
</Info>

## Extending Subset Queries

### Adding WHERE Conditions

```typescript theme={null}
const { qb } = UserModel.getSubsetQueries("L");

qb.where("users.role", "admin").where("users.is_verified", true);

const result = await UserModel.executeSubsetQuery({
  subset: "L",
  qb,
  params: { num: 20, page: 1 },
});
// Subset L base fields + WHERE conditions
```

### ORDER BY Sorting

```typescript theme={null}
const { qb } = UserModel.getSubsetQueries("L");

qb.orderBy("users.created_at", "desc");

const result = await UserModel.executeSubsetQuery({
  subset: "L",
  qb,
  params: { num: 10, page: 1 },
});
```

### Complex Conditions

```typescript theme={null}
const { qb, onSubset } = UserModel.getSubsetQueries("P");

qb.where("users.role", "normal").orderBy("employee.salary", "desc");

// Access Subset-specific tables with onSubset
onSubset("P").where("employee__department.name", "Engineering");
onSubset("P").where("employee.salary", ">", "60000");

// Subset P includes employee.department.name,
// so JOIN is automatically configured
```

## Subset Type Safety

### Compile-Time Validation

```typescript theme={null}
// ✅ OK: Subset P includes employee__department table
const { qb, onSubset } = UserModel.getSubsetQueries("P");
onSubset("P").where("employee__department.name", "Engineering");

// ❌ Type Error: Subset L doesn't include employee table
const { qb: qbL, onSubset: onSubsetL } = UserModel.getSubsetQueries("L");
onSubsetL("L").where("employee__department.name", "Engineering");
//             ^^^^^^^^^^^^^^^^^^^^^^^^^ Property does not exist
```

### Automatic Type Inference

```typescript theme={null}
const user = await UserModel.findById(1, ["P"]);

// TypeScript automatically infers types
user.id; // number
user.username; // string
user.employee; // { salary: string; department: { name: string } } | null

// ❌ Compile Error
user.password; // Property 'password' does not exist
```

## Practical Examples

### User List API

```typescript theme={null}
// Model method (recommended pattern)
class UserModelClass extends BaseModelClass<...> {
  @api({ httpMethod: "GET", clients: ["axios", "tanstack-query"] })
  async findMany<T extends UserSubsetKey>(
    subset: T,
    rawParams?: UserListParams
  ): Promise<ListResult<UserListParams, UserSubsetMapping[T]>> {
    const params = { num: 24, page: 1, ...rawParams };
    const { qb, onSubset } = this.getSubsetQueries(subset);

    // Role filter
    if (params.role) {
      qb.where("users.role", params.role);
    }

    // Search
    if (params.keyword) {
      qb.where("users.username", "like", `%${params.keyword}%`);
    }

    // Sorting
    qb.orderBy("users.created_at", "desc");

    return this.executeSubsetQuery({ subset, qb, params });
  }
}
```

### Profile Query API

```typescript theme={null}
@api({ httpMethod: "GET", clients: ["axios", "tanstack-query"] })
async findById<T extends UserSubsetKey>(
  subset: T,
  id: string
): Promise<UserSubsetMapping[T]> {
  const { rows } = await this.findMany(subset, { id, num: 1, page: 1 });
  if (!rows[0]) {
    throw new NotFoundException("User not found");
  }
  return rows[0];
}

// Usage example
const user = await UserModel.findById("P", "123");
// user.employee?.department?.name is accessible
```

### Complex Search API

```typescript theme={null}
async searchUsers(params: {
  role?: UserRole;
  departmentName?: string;
  minSalary?: number;
}) {
  const { qb, onSubset } = this.getSubsetQueries("P");

  if (params.role) {
    qb.where("users.role", params.role);
  }

  if (params.departmentName) {
    // Access Subset P's tables with type safety using onSubset
    onSubset("P").where(
      "employee__department.name",
      "like",
      `%${params.departmentName}%`
    );
  }

  if (params.minSalary) {
    onSubset("P").where("employee.salary", ">=", String(params.minSalary));
  }

  qb.orderBy("users.username", "asc");

  return this.executeSubsetQuery({
    subset: "P",
    qb,
    params: { num: 100, page: 1 },
  });
}
```

## Combining Subsets

### Using Multiple Subsets (Union)

```typescript theme={null}
// Use different Subsets based on situation
async getUser(userId: number, detailed: boolean) {
  if (detailed) {
    return await UserModel.findById(userId, ["P"]);
    // Type: UserSubsetMapping["P"] | undefined
  } else {
    return await UserModel.findById(userId, ["SS"]);
    // Type: UserSubsetMapping["SS"] | undefined
  }
}
```

### Conditional Subset Selection

```typescript theme={null}
async getUserData(userId: number, role: UserRole) {
  const subsets = role === "admin" ? ["A"] : ["P"];

  return await UserModel.findById(userId, subsets);
  // Type: UserSubsetMapping["A"] | UserSubsetMapping["P"] | undefined
}
```

## Performance Optimization

### Use Only Needed Subsets

```typescript theme={null}
// ❌ Bad: Using Subset A for lists (excessive data)
const users = await UserModel.findMany({
  listParams: { page: 1, pageSize: 20 },
  subsetKey: "A", // Loading all fields
});

// ✅ Good: Use Subset L for lists
const users = await UserModel.findMany({
  listParams: { page: 1, pageSize: 20 },
  subsetKey: "L", // Only needed fields
});
```

### Minimize JOINs

```typescript theme={null}
// ❌ Bad: Unnecessary JOINs (Subset P)
// Subset P also JOINs employee, department tables
const { qb } = UserModel.getSubsetQueries("P");
const result = await UserModel.executeSubsetQuery({
  subset: "P",
  qb,
  params: { num: 100, page: 1 },
});

// ✅ Good: Subset without JOINs (SS)
// Subset SS only queries users table
const { qb: qbSS } = UserModel.getSubsetQueries("SS");
const resultSS = await UserModel.executeSubsetQuery({
  subset: "SS",
  qb: qbSS,
  params: { num: 100, page: 1 },
});
```

## Subset vs Raw Puri Comparison

### Using Subset

```typescript theme={null}
// ✅ Subset - type safe, concise
const { qb, onSubset } = UserModel.getSubsetQueries("P");
qb.where("users.role", "admin");

const result = await UserModel.executeSubsetQuery({
  subset: "P",
  qb,
  params: { num: 20, page: 1 },
});
// Type: ListResult<..., UserSubsetMapping["P"]>
// JOIN auto-configured
// SELECT auto-configured
```

### Using Raw Puri

```typescript theme={null}
// ⚠️ Raw Puri - manual configuration
const puri = UserModel.getPuri("r");
const users = await puri
  .table("users")
  .leftJoin("employees", "users.id", "employees.user_id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    id: "users.id",
    username: "users.username",
    employee__salary: "employees.salary",
    employee__department__name: "departments.name",
  })
  .where("users.role", "admin");

// Type: { id: number; username: string; ... }[]
// JOIN manual configuration
// SELECT manual configuration
// Hydrate manual application required
```

<Tip>
  **Use Subsets!** - Guaranteed type safety - Improved code reusability - Easy maintenance - Query
  consistency
</Tip>

## Next Steps

<CardGroup cols={2}>
  <Card title="Nested Relations" icon="diagram-project" href="/en/database/subsets/nested-relations">
    Load 1:N, N:M with LoaderQuery
  </Card>

  <Card title="Defining Subsets" icon="pen" href="/en/database/subsets/defining-subsets">
    Create Subsets in Entity
  </Card>

  <Card title="Model Methods" icon="cube" href="/en/core-concepts/model/model-methods">
    Model CRUD methods
  </Card>

  <Card title="Puri Queries" icon="database" href="/en/database/puri/basic-queries">
    Puri query builder
  </Card>
</CardGroup>