N+1 Problems

// N+1 problem occurring
async function getUsersWithOrders() {
  // 1. Fetch users (1 query)
  const users = await UserModel.getPuri("r").table("users").select("id", "name"); // 100 users

  // 2. Fetch orders for each user (N queries)
  for (const user of users) {
    const orders = await OrderModel.getPuri("r")
      .table("orders")
      .select("*")
      .where({ user_id: user.id });

    user.orders = orders; // 100 queries!
  }

  return users;
}

// Total queries: 1 + 100 = 101

// user.entity.json
{
  "name": "User",
  "properties": [
    {
      "name": "id",
      "type": "int",
      "isPrimary": true
    },
    {
      "name": "name",
      "type": "varchar"
    }
  ],
  "subsets": {
    "A": {
      "fields": ["id", "name"]
    },
    "WithOrders": {
      "fields": ["id", "name"],
      "relations": [
        {
          "name": "orders",
          "subset": "A"
        }
      ]
    }
  }
}

// Solving N+1 with Subset
async function getUsersWithOrders() {
  // Get query builder with getSubsetQueries
  const { qb } = UserModel.getSubsetQueries("WithOrders");

  // Execute automatically optimized query
  const result = await UserModel.executeSubsetQuery({
    subset: "WithOrders",
    qb,
    params: { num: 100, page: 1 },
  });

  // result.rows[0].orders is already loaded
  // No additional queries!

  return result.rows;
}

// Total queries: 2 (users 1 + orders 1)

// post.entity.json
{
  "name": "Post",
  "subsets": {
    "WithAuthor": {
      "fields": ["id", "title", "content"],
      "relations": [
        {
          "name": "author",
          "subset": "A"
        }
      ]
    }
  }
}

// Fetch posts with authors
const { qb } = PostModel.getSubsetQueries("WithAuthor");
const result = await PostModel.executeSubsetQuery({
  subset: "WithAuthor",
  qb,
  params: { num: 20, page: 1 },
});

result.rows.forEach((post) => {
  console.log(`${post.title} by ${post.author.name}`);
});

// order.entity.json
{
  "name": "Order",
  "subsets": {
    "Detail": {
      "fields": ["id", "total", "status"],
      "relations": [
        {
          "name": "user",
          "subset": "A"
        },
        {
          "name": "items",
          "subset": "A"
        },
        {
          "name": "payment",
          "subset": "A"
        }
      ]
    }
  }
}

// Order + user + items + payment info
const { qb } = OrderModel.getSubsetQueries("Detail");
const result = await OrderModel.executeSubsetQuery({
  subset: "Detail",
  qb: qb.where({ status: "completed" }),
  params: { num: 20, page: 1 },
});

// All relations are already loaded
result.rows.forEach((order) => {
  console.log(`Order ${order.id}`);
  console.log(`User: ${order.user.name}`);
  console.log(`Items: ${order.items.length}`);
  console.log(`Payment: ${order.payment.method}`);
});

// comment.entity.json
{
  "name": "Comment",
  "subsets": {
    "WithAll": {
      "fields": ["id", "content", "created_at"],
      "relations": [
        {
          "name": "author",
          "subset": "A"
        },
        {
          "name": "post",
          "subset": "WithAuthor" // Nested: post + post author
        }
      ]
    }
  }
}

// Comment + comment author + post + post author
const { qb } = CommentModel.getSubsetQueries("WithAll");
const result = await CommentModel.executeSubsetQuery({
  subset: "WithAll",
  qb,
  params: { num: 20, page: 1 },
});

result.rows.forEach((comment) => {
  console.log(`${comment.author.name}: ${comment.content}`);
  console.log(`  on "${comment.post.title}" by ${comment.post.author.name}`);
});

// Only 4 queries executed:
// 1. comments
// 2. comment authors
// 3. posts
// 4. post authors

// N+1 problem
async function getUsersWithOrders() {
  const users = await UserModel.getPuri("r").table("users").select("id", "name");

  for (const user of users) {
    user.orders = await OrderModel.getPuri("r")
      .table("orders")
      .select("*")
      .where({ user_id: user.id });
  }

  return users;
}

// Optimized with whereIn
async function getUsersWithOrders() {
  // 1. Fetch users
  const users = await UserModel.getPuri("r").table("users").select("id", "name");

  const userIds = users.map((u) => u.id);

  // 2. Fetch all orders at once
  const orders = await OrderModel.getPuri("r")
    .table("orders")
    .select("*")
    .whereIn("user_id", userIds);

  // 3. Map in memory
  const ordersByUserId = orders.reduce(
    (acc, order) => {
      if (!acc[order.user_id]) acc[order.user_id] = [];
      acc[order.user_id].push(order);
      return acc;
    },
    {} as Record<number, Order[]>,
  );

  users.forEach((user) => {
    user.orders = ordersByUserId[user.id] || [];
  });

  return users;
}

// 2 queries: users + orders

class OrderModelClass extends BaseModelClass {
  // Orders loader by user
  private userOrdersLoader = new DataLoader(async (userIds: number[]) => {
    const orders = await this.getPuri("r").table("orders").select("*").whereIn("user_id", userIds);

    // Group by user_id
    const grouped = userIds.map((userId) => orders.filter((o) => o.user_id === userId));

    return grouped;
  });

  async getOrdersByUserId(userId: number): Promise<Order[]> {
    return this.userOrdersLoader.load(userId);
  }
}

// Usage
async function processUsers(users: User[]) {
  for (const user of users) {
    // DataLoader automatically batches
    const orders = await OrderModel.getOrdersByUserId(user.id);
    console.log(`${user.name}: ${orders.length} orders`);
  }
}

// Internally queries all at once with whereIn

class PostModelClass extends BaseModelClass {
  // N+1 occurring
  @api({ httpMethod: "GET" })
  async listPostsSlow() {
    const posts = await this.getPuri("r").table("posts").select("id", "title", "content");

    // N+1 occurring!
    for (const post of posts) {
      post.author = await UserModel.findById(post.author_id);
      post.comments = await CommentModel.getPuri("r")
        .table("comments")
        .where({ post_id: post.id });
    }

    return posts;
  }

  // Optimized with Subset
  @api({ httpMethod: "GET" })
  async listPostsFast() {
    const { qb } = this.getSubsetQueries("WithAuthorAndComments");

    return this.executeSubsetQuery({
      subset: "WithAuthorAndComments",
      qb: qb.orderBy("created_at", "desc"),
      params: { num: 20, page: 1 },
    });
  }
}

class DashboardService {
  // N+1 occurring
  async getDashboardSlow() {
    const users = await UserModel.getPuri("r").table("users").select("id", "name");

    const stats = [];
    for (const user of users) {
      const orderCount = await OrderModel.getPuri("r")
        .table("orders")
        .select({ count: Puri.count() })
        .where({ user_id: user.id });

      const totalSpent = await OrderModel.getPuri("r")
        .table("orders")
        .select({ total: Puri.sum("total") })
        .where({ user_id: user.id });

      stats.push({
        user: user.name,
        orderCount: orderCount[0].count,
        totalSpent: totalSpent[0].total,
      });
    }

    return stats;
  }

  // Optimized with JOIN
  async getDashboardFast() {
    return UserModel.getPuri("r")
      .table("users")
      .select({
        id: "users.id",
        name: "users.name",
        order_count: Puri.count("orders.id"),
        total_spent: Puri.rawNumber("COALESCE(SUM(orders.total), 0)"),
      })
      .leftJoin("orders", "orders.user_id", "users.id")
      .groupBy("users.id");
  }
}

// sonamu.config.ts
export default {
  database: {
    defaultOptions: {
      debug: true, // Output all queries
    },
  },
} satisfies SonamuConfig;

SELECT * FROM users;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
...

test("N+1 problem check", async () => {
  // Query counter
  let queryCount = 0;

  const db = UserModel.getDB("r");
  db.on("query", () => queryCount++);

  // Test
  const { qb } = UserModel.getSubsetQueries("WithOrders");
  const result = await UserModel.executeSubsetQuery({
    subset: "WithOrders",
    qb: qb.limit(10),
    params: { num: 10, page: 1 },
  });

  // Verify query count
  expect(queryCount).toBeLessThan(5); // Would be 11+ with N+1
  expect(result.rows).toHaveLength(10);
});

Querying 100 users:
- Queries: 101
- Time: ~2000ms
- DB connections: 101 acquire/release

Querying 100 users:
- Queries: 2
- Time: ~50ms
- DB connections: 2 acquire/release

// Using Subset
const { qb } = PostModel.getSubsetQueries("WithAuthor");
const result = await PostModel.executeSubsetQuery({
  subset: "WithAuthor",
  qb,
  params: { num: 20, page: 1 },
});

// Manual loading - avoid
const posts = await PostModel.getPuri("r").table("posts").select("*");
for (const post of posts) {
  post.author = await UserModel.findById(post.author_id);
}

{
  "name": "User",
  "subsets": {
    "ForAPI": {
      "fields": ["id", "name", "email"],
      "relations": [
        {
          "name": "profile",
          "subset": "Public"
        }
      ]
    }
  }
}

// Cases where JOIN is better than Subset:
// - Aggregation functions needed
// - Complex filtering
// - Large datasets

// Query logging only in development
export default {
  database: {
    defaultOptions: {
      debug: process.env.NODE_ENV === "development",
    },
  },
} satisfies SonamuConfig;