28. リレーション — テーブル間の関連

// N+1問題をインメモリの簡易リポジトリで再現し、クエリ回数を数える
type Author = { id: number; authorName: string };
type Book = { id: number; title: string; authorId: number };

const authors: Author[] = [
  { id: 1, authorName: "Alice" },
  { id: 2, authorName: "Bob" },
];
const books: Book[] = [
  { id: 1, title: "TS入門", authorId: 1 },
  { id: 2, title: "DB設計", authorId: 2 },
  { id: 3, title: "API設計", authorId: 1 },
];

// クエリを1回投げるたびにカウントする(DBアクセス回数を可視化)
let queryCount = 0;

function findAllBooks(): Book[] {
  queryCount += 1; // 本の一覧を1クエリで取得
  return books.slice();
}
function findAuthorById(id: number): Author | undefined {
  queryCount += 1; // 著者を1件ずつ取得
  return authors.find((a) => a.id === id);
}
function findAuthorsByIds(ids: number[]): Author[] {
  queryCount += 1; // IN句で一括取得 = 1クエリ
  return authors.filter((a) => ids.includes(a.id));
}

// N+1パターン: 本(1クエリ)+ 本ごとに著者を1件ずつ(N=3クエリ)
queryCount = 0;
const naive = findAllBooks().map((book) => {
  const author = findAuthorById(book.authorId);
  return author ? book.title + " / " + author.authorName : "?";
});
console.log("N+1版の結果: " + naive.join(", "));
console.log("N+1版のクエリ回数: " + queryCount); // 1 + 3 = 4

// 解決パターン: 著者をまとめて1回で取得(本1回 + 著者1回 = 2クエリ)
queryCount = 0;
const targetBooks = findAllBooks();
const authorMap = new Map<number, Author>();
findAuthorsByIds(targetBooks.map((b) => b.authorId)).forEach((a) => authorMap.set(a.id, a));
const batched = targetBooks.map((book) => {
  const author = authorMap.get(book.authorId);
  return author ? book.title + " / " + author.authorName : "?";
});
console.log("一括取得版の結果: " + batched.join(", "));
console.log("一括取得版のクエリ回数: " + queryCount); // 1 + 1 = 2