Skip to main content
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")
  .many();

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