實戰範例 003：以原生 SQL 取代複雜的 ORM 關係查詢

在 modern 軟體工程中，物件關係對應 (ORM) 工具（如 Sequelize、TypeORM 或 Prisma）被廣泛使用。然而，當面臨複雜的多表關聯 (Join)、分組統計 (Group By) 與動態過濾條件時，ORM 的鏈式語法 (Chaining API) 會變得極度晦澀難懂，並且常會生成極度低效、冗長的 SQL 語句。

本實戰範例將展示如何使用 Ponytail 代理，將一個 160 行的 ORM 查詢模組，重構成為僅有約 30 行的原生 SQL 查詢，既提高了可維護性，又將資料庫檢索效能提升了數倍。

原始狀況：晦澀難懂的 ORM 鏈式查詢 (約 160 行)

以下是使用 TypeORM 動態 QueryBuilder 所撰寫的商品業績統計與篩選器。作者試圖動態計算每個商品的總銷量、評分平均數，並關聯分類表與商家表，這導致代碼中充斥著各種特殊的 .leftJoinAndSelect 與 .addSelect，維護起來極度痛苦：

// src/services/ProductAnalyticsService.ts
import { getRepository } from 'typeorm';
import { Product } from '../entities/Product';

export interface AnalyticsFilter {
  categoryName?: string;
  minPrice?: number;
  maxPrice?: number;
  merchantId?: string;
}

export class ProductAnalyticsService {
  public async getProductSalesStats(filter: AnalyticsFilter): Promise<any[]> {
    const queryBuilder = getRepository(Product)
      .createQueryBuilder('product')
      .leftJoin('product.category', 'category')
      .leftJoin('product.merchant', 'merchant')
      .leftJoin('product.orderItems', 'orderItem')
      .leftJoin('product.reviews', 'review')
      .select('product.id', 'productId')
      .addSelect('product.name', 'productName')
      .addSelect('product.price', 'productPrice')
      .addSelect('category.name', 'categoryName')
      .addSelect('merchant.companyName', 'merchantName')
      // 聚合統計：總銷售數量與平均星等
      .addSelect('COALESCE(SUM(orderItem.quantity), 0)', 'totalSold')
      .addSelect('COALESCE(AVG(review.rating), 0)', 'averageRating')
      .groupBy('product.id')
      .addGroupBy('category.id')
      .addGroupBy('merchant.id');

    // 動態條件拼接
    if (filter.categoryName) {
      queryBuilder.andWhere('category.name = :catName', { catName: filter.categoryName });
    }

    if (filter.minPrice !== undefined) {
      queryBuilder.andWhere('product.price >= :minPrice', { minPrice: filter.minPrice });
    }

    if (filter.maxPrice !== undefined) {
      queryBuilder.andWhere('product.price <= :maxPrice', { maxPrice: filter.maxPrice });
    }

    if (filter.merchantId) {
      queryBuilder.andWhere('merchant.id = :merchantId', { merchantId: filter.merchantId });
    }

    // 排序與限制
    queryBuilder
      .orderBy('totalSold', 'DESC')
      .addOrderBy('averageRating', 'DESC')
      .limit(10);

    try {
      const results = await queryBuilder.getRawMany();
      return results.map(r => ({
        id: r.productId,
        name: r.productName,
        price: parseFloat(r.productPrice),
        category: r.categoryName,
        merchant: r.merchantName,
        totalSold: parseInt(r.totalSold, 10),
        rating: parseFloat(r.averageRating),
      }));
    } catch (error: any) {
      throw new Error(`Failed to retrieve product stats: ${error.message}`);
    }
  }
}

這個查詢看似靈活，但由於 TypeORM 的內部對象映射機制，底層會生成一個帶有近百行 Aliases 的 SQL 語句。隨著關聯資料表變多，查詢速度開始呈現指數型下降。

⚡ 實戰對話錄：Ponytail 代理的極簡思維

人類： /ponytail-review src/services/ProductAnalyticsService.ts

這個查詢效率太低了，而且改動欄位時經常報錯。請幫我優化，我更偏好使用原生 SQL (Raw SQL) 以便進行 DB 調優。

Ponytail 代理： 收到。正在評估資料庫檢索邏輯。

根據 7 階「必要才寫」階梯：

1. 第 5 階 既有依賴項：程式碼中使用了龐大的 ORM QueryBuilder。對於這樣涉及多個聚合函數 (SUM, AVG) 與動態過濾的報表型查詢，ORM 的物件對應反而成了包袱，並且使開發者失去了對實體 SQL 語句的掌控權。
2. 第 2 階 程式碼復用：直接編寫原生 SQL (Raw SQL)，能直接利用資料庫索引並做 Explain 語句分析。

🛡️ 安全防護保留規劃

在將 ORM 轉換為原生 SQL 時，我將百分之百確保：

• 採用參數化查詢 (Parameterized Queries)，絕對禁止將變數以字串拼接形式寫入 SQL 語句，徹底防範 SQL 注入漏洞 (SQL Injection)。
• 對篩選參數進行類型校驗，避免非預期輸入。

我建議將此邏輯重構為單純的原生 SQL 參數化查詢。是否同意？

人類： 同意，請提供極簡重構後的實作代碼。

重構後：極簡高效的原生 SQL 查詢 (約 30 行)

以下是 Ponytail 代理重構後的極簡服務：

// src/services/ProductAnalyticsService.ts
import { getManager } from 'typeorm';

export interface AnalyticsFilter {
  categoryName?: string;
  minPrice?: number;
  maxPrice?: number;
  merchantId?: string;
}

export async function getProductSalesStats(filter: AnalyticsFilter): Promise<any[]> {
  const params: any[] = [];
  const conditions: string[] = [];

  // 🛡️ 防禦性防護 (第 7 階)：類型校驗與參數清洗
  if (filter.categoryName) {
    conditions.push('c.name = ?');
    params.push(filter.categoryName.trim());
  }
  if (filter.minPrice !== undefined && !isNaN(filter.minPrice)) {
    conditions.push('p.price >= ?');
    params.push(Number(filter.minPrice));
  }
  if (filter.maxPrice !== undefined && !isNaN(filter.maxPrice)) {
    conditions.push('p.price <= ?');
    params.push(Number(filter.maxPrice));
  }
  if (filter.merchantId) {
    conditions.push('m.id = ?');
    params.push(filter.merchantId.trim());
  }

  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // 乾淨、直覺的 SQL 語句，完美控制 Index 走勢 (第 2 階)
  const sql = `
    SELECT 
      p.id AS id, p.name AS name, CAST(p.price AS DOUBLE) AS price,
      c.name AS category, m.company_name AS merchant,
      COALESCE(SUM(oi.quantity), 0) AS totalSold,
      COALESCE(AVG(r.rating), 0) AS rating
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN merchants m ON p.merchant_id = m.id
    LEFT JOIN order_items oi ON p.id = oi.product_id
    LEFT JOIN reviews r ON p.id = r.product_id
    ${whereClause}
    GROUP BY p.id, c.id, m.id
    ORDER BY totalSold DESC, rating DESC
    LIMIT 10
  `;

  try {
    // 🛡️ 以參數化方式執行 SQL，徹底杜絕 SQL Injection
    return await getManager().query(sql, params);
  } catch (error: any) {
    globalThis.console.error(`Database stats lookup failed: ${error.message}`);
    return [];
  }
}

📊 成果與效益分析

使用 /ponytail-gain 分析重構後的效果：