Nested Relations - 🌲 Sonamu

LoaderQuery is a system for efficiently loading 1:N and N:M relation data.

LoaderQuery Overview

1:N Relations

Parent → Multiple children HasMany

N:M Relations

Bidirectional many-to-many ManyToMany

N+1 Solution

Efficient data loading Batch Loading

Nested Loaders

Recursive relation loading Nested Relations

LoaderQuery vs SubsetQuery

SubsetQuery (JOIN)

Suitable for OneToOne or BelongsTo relations:

// SubsetQuery: Handled with LEFT JOIN
{
  "subsets": {
    "P": [
      "id",
      "username",
      "employee.salary",           // ← OneToOne
      "employee.department.name"   // ← BelongsTo
    ]
  }
}

// SQL: LEFT JOIN employees, LEFT JOIN departments
// Result: 1 employee per row

LoaderQuery (Separate Query)

Suitable for HasMany or ManyToMany relations:

// LoaderQuery: Handled with separate query
{
  loaders: [
    {
      as: "posts",
      refId: "user_id",
      qb: (qb, fromIds) => qb.table("posts").whereIn("user_id", fromIds),
    },
  ];
}

// SQL: SELECT * FROM posts WHERE user_id IN (1, 2, 3)
// Result: N posts array per user

Defining LoaderQuery

LoaderQuery is defined in subset-loaders.ts files:

// src/application/user/user.subset-loaders.ts

import type { PuriLoaderQueries } from "sonamu";
import type { UserSubsetKey } from "../sonamu.generated";

export const UserSubsetLoaders: PuriLoaderQueries<UserSubsetKey> = {
  // Add posts loader to Subset A
  A: [
    {
      as: "posts", // Result field name
      refId: "user_id", // Reference field (posts.user_id)
      qb: (qb, fromIds) =>
        qb
          .table("posts")
          .whereIn("user_id", fromIds)
          .select({
            id: "id",
            title: "title",
            created_at: "created_at",
          })
          .orderBy("created_at", "desc"),
    },
  ],
};

LoaderQuery automatically performs Batch Loading to solve the N+1 problem.

1:N Relations (HasMany)

Department → Employees

// Department Entity
export const DepartmentSubsetLoaders: PuriLoaderQueries<DepartmentSubsetKey> = {
  P: [
    {
      as: "employees",
      refId: "department_id",
      qb: (qb, fromIds) =>
        qb
          .table("employees")
          .whereIn("department_id", fromIds)
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            employee_number: "employees.employee_number",
            username: "users.username",
            salary: "employees.salary",
          })
          .orderBy("employees.employee_number", "asc"),
    },
  ],
};

Usage Example:

const dept = await DepartmentModel.findById(1, ["P"]);

// Type:
// {
//   id: number;
//   name: string;
//   employees: Array<{
//     id: number;
//     employee_number: string;
//     username: string;
//     salary: string;
//   }>;
// }

console.log(`${dept.name} department employees: ${dept.employees.length}`);
dept.employees.forEach((emp) => {
  console.log(`- ${emp.username} (${emp.employee_number})`);
});

N:M Relations (ManyToMany)

Project ↔ Employee

// Project Entity
export const ProjectSubsetLoaders: PuriLoaderQueries<ProjectSubsetKey> = {
  P: [
    {
      as: "members",
      refId: "project_id",
      qb: (qb, fromIds) =>
        qb
          .table("projects__employees")
          .whereIn("project_id", fromIds)
          .join("employees", "projects__employees.employee_id", "employees.id")
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            username: "users.username",
            employee_number: "employees.employee_number",
            role: "projects__employees.role", // Junction table field
          }),
    },
  ],
};

Usage Example:

const project = await ProjectModel.findById(1, ["P"]);

// Type:
// {
//   id: number;
//   name: string;
//   members: Array<{
//     id: number;
//     username: string;
//     employee_number: string;
//     role: string;  // role from junction table
//   }>;
// }

console.log(`Project: ${project.name}`);
console.log(`${project.members.length} participants:`);
project.members.forEach((member) => {
  console.log(`- ${member.username} (${member.role})`);
});

Nested LoaderQuery

LoaderQuery can be nested recursively:

// Department Entity
export const DepartmentSubsetLoaders: PuriLoaderQueries<DepartmentSubsetKey> = {
  P: [
    {
      as: "employees",
      refId: "department_id",
      qb: (qb, fromIds) =>
        qb
          .table("employees")
          .whereIn("department_id", fromIds)
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            username: "users.username",
            user_id: "users.id", // ← refId for next Loader
          }),

      // Nested Loader: Employee → Projects
      loaders: [
        {
          as: "projects",
          refId: "employee_id",
          qb: (qb, fromIds) =>
            qb
              .table("projects__employees")
              .whereIn("employee_id", fromIds)
              .join("projects", "projects__employees.project_id", "projects.id")
              .select({
                id: "projects.id",
                name: "projects.name",
                status: "projects.status",
              }),
        },
      ],
    },
  ],
};

Result Structure:

{
  id: 1,
  name: "Engineering",
  employees: [
    {
      id: 1,
      username: "john",
      projects: [
        { id: 1, name: "Project A", status: "in_progress" },
        { id: 2, name: "Project B", status: "completed" }
      ]
    },
    {
      id: 2,
      username: "jane",
      projects: [
        { id: 3, name: "Project C", status: "in_progress" }
      ]
    }
  ]
}

How Batch Loading Works

N+1 Problem

// ❌ N+1 problem occurs
const users = await UserModel.findMany({ subsetKey: "L" }); // 1 query

for (const user of users) {
  const posts = await PostModel.findMany({
    where: [["user_id", user.id]],
    subsetKey: "L",
  }); // N queries (one per user)

  console.log(`${user.username}: ${posts.length} posts`);
}

// Total queries: 1 + N

Batch Loading Solution

// ✅ Solved with Batch Loading
const users = await UserModel.findMany({
  subsetKey: "A", // A Subset includes posts Loader
});

// Query 1: SELECT * FROM users
// Query 2: SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...)

users.forEach((user) => {
  console.log(`${user.username}: ${user.posts.length} posts`);
});

// Total queries: 2 (regardless of user count)

Practical Examples

Department Employee List

async getDepartmentWithEmployees(deptId: number) {
  const dept = await DepartmentModel.findById(deptId, ["P"]);

  if (!dept) {
    throw new NotFoundException("Department not found");
  }

  return {
    id: dept.id,
    name: dept.name,
    employeeCount: dept.employees.length,
    employees: dept.employees.map(emp => ({
      id: emp.id,
      name: emp.username,
      number: emp.employee_number,
      salary: emp.salary,
    })),
  };
}

Project Dashboard

async getProjectDashboard(projectId: number) {
  const project = await ProjectModel.findById(projectId, ["Dashboard"]);

  if (!project) {
    throw new NotFoundException("Project not found");
  }

  return {
    id: project.id,
    name: project.name,
    status: project.status,

    // Members from LoaderQuery
    members: project.members.map(m => ({
      id: m.id,
      name: m.username,
      role: m.role,
    })),

    // Tasks from LoaderQuery
    tasks: project.tasks.map(t => ({
      id: t.id,
      title: t.title,
      status: t.status,
      assignee: t.assignee_name,
    })),

    stats: {
      memberCount: project.members.length,
      taskCount: project.tasks.length,
      completedTasks: project.tasks.filter(t => t.status === "completed").length,
    },
  };
}

User Activity Summary

// User Subset with posts and comments
const user = await UserModel.findById(userId, ["Activity"]);

const summary = {
  user: {
    id: user.id,
    username: user.username,
    email: user.email,
  },

  activity: {
    totalPosts: user.posts.length,
    recentPosts: user.posts.slice(0, 5).map((p) => ({
      id: p.id,
      title: p.title,
      createdAt: p.created_at,
    })),

    totalComments: user.comments.length,
    recentComments: user.comments.slice(0, 5).map((c) => ({
      id: c.id,
      content: c.content,
      postTitle: c.post_title,
      createdAt: c.created_at,
    })),
  },
};

LoaderQuery Design Principles

1. SELECT Only Needed Fields

// ❌ Bad: Loading all fields
{
  as: "employees",
  refId: "department_id",
  qb: (qb, fromIds) =>
    qb.table("employees")
      .whereIn("department_id", fromIds)
      .selectAll(),  // All fields (including password, etc.)
}

// ✅ Good: Only needed fields
{
  as: "employees",
  refId: "department_id",
  qb: (qb, fromIds) =>
    qb.table("employees")
      .whereIn("department_id", fromIds)
      .select({
        id: "id",
        username: "username",
        employee_number: "employee_number",
      }),
}

2. Sorting and Limiting

// Only 5 recent posts
{
  as: "recentPosts",
  refId: "user_id",
  qb: (qb, fromIds) =>
    qb.table("posts")
      .whereIn("user_id", fromIds)
      .orderBy("created_at", "desc")
      .limit(5),
}

3. Condition Filtering

// Only completed projects
{
  as: "completedProjects",
  refId: "employee_id",
  qb: (qb, fromIds) =>
    qb.table("projects__employees")
      .whereIn("employee_id", fromIds)
      .join("projects", "projects__employees.project_id", "projects.id")
      .where("projects.status", "completed")
      .select({
        id: "projects.id",
        name: "projects.name",
      }),
}

Next Steps

Defining Subsets

Create Subsets in Entity

Using Subsets

Use Subsets in Model

Model Methods

Model CRUD methods

Puri Joins

Puri JOIN queries

​LoaderQuery Overview

1:N Relations

N:M Relations

N+1 Solution

Nested Loaders

​LoaderQuery vs SubsetQuery

​SubsetQuery (JOIN)

​LoaderQuery (Separate Query)

​Defining LoaderQuery

​1:N Relations (HasMany)

​Department → Employees

​N:M Relations (ManyToMany)

​Project ↔ Employee

​Nested LoaderQuery

​How Batch Loading Works

​N+1 Problem

​Batch Loading Solution

​Practical Examples

​Department Employee List

​Project Dashboard

​User Activity Summary

​LoaderQuery Design Principles

​1. SELECT Only Needed Fields

​2. Sorting and Limiting

​3. Condition Filtering

​Next Steps

Defining Subsets

Using Subsets

Model Methods

Puri Joins

LoaderQuery Overview

LoaderQuery vs SubsetQuery

SubsetQuery (JOIN)

LoaderQuery (Separate Query)

Defining LoaderQuery

1:N Relations (HasMany)

Department → Employees

N:M Relations (ManyToMany)

Project ↔ Employee

Nested LoaderQuery

How Batch Loading Works

N+1 Problem

Batch Loading Solution

Practical Examples

Department Employee List

Project Dashboard

User Activity Summary

LoaderQuery Design Principles

1. SELECT Only Needed Fields

2. Sorting and Limiting

3. Condition Filtering

Next Steps