Skip to main content
LoaderQuery is a system for efficiently loading 1:N and N:M relation data.

​
LoaderQuery Overview

1:N Relations

Parent → Multiple childrenHasMany

N:M Relations

Bidirectional many-to-manyManyToMany

N+1 Solution

Efficient data loadingBatch Loading

Nested Loaders

Recursive relation loadingNested 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 { SubsetLoaderMap } from "sonamu";

export const UserSubsetLoaders: SubsetLoaderMap<"User"> = {
  // 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: SubsetLoaderMap<"Department"> = {
  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: SubsetLoaderMap<"Project"> = {
  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

Nested LoaderQuery structure
LoaderQuery can be nested recursively: 
// Department Entity
export const DepartmentSubsetLoaders: SubsetLoaderMap<"Department"> = {
  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 and Batch Loading solution

​
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