Using Subsets - 🌲 Sonamu

Subsets are used through Model methods.

Model Methods and Subsets

findById - Single Record Query

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

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

findMany - Multiple Records Query

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:

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

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

Extending Subset Queries

Adding WHERE Conditions

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

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

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

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

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

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

@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

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)

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

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

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

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

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

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

Use Subsets! - Guaranteed type safety - Improved code reusability - Easy maintenance - Query consistency

Next Steps

Nested Relations

Load 1:N, N:M with LoaderQuery

Defining Subsets

Create Subsets in Entity

Model Methods

Model CRUD methods

Puri Queries

Puri query builder

​Model Methods and Subsets

​findById - Single Record Query

​findOne - Single Record by Condition

​findMany - Multiple Records Query

​Starting Subset Queries with getSubsetQueries

​Extending Subset Queries

​Adding WHERE Conditions

​ORDER BY Sorting

​Complex Conditions

​Subset Type Safety

​Compile-Time Validation

​Automatic Type Inference

​Practical Examples

​User List API

​Profile Query API

​Complex Search API

​Combining Subsets

​Using Multiple Subsets (Union)

​Conditional Subset Selection

​Performance Optimization

​Use Only Needed Subsets

​Minimize JOINs

​Subset vs Raw Puri Comparison

​Using Subset

​Using Raw Puri

​Next Steps

Nested Relations

Defining Subsets

Model Methods

Puri Queries

Model Methods and Subsets

findById - Single Record Query

findOne - Single Record by Condition

findMany - Multiple Records Query

Starting Subset Queries with getSubsetQueries

Extending Subset Queries

Adding WHERE Conditions

ORDER BY Sorting

Complex Conditions

Subset Type Safety

Compile-Time Validation

Automatic Type Inference

Practical Examples

User List API

Profile Query API

Complex Search API

Combining Subsets

Using Multiple Subsets (Union)

Conditional Subset Selection

Performance Optimization

Use Only Needed Subsets

Minimize JOINs

Subset vs Raw Puri Comparison

Using Subset

Using Raw Puri

Next Steps