如何使用TypeORM实现复杂SQL查询 解决商品拉取接口性能问题

方案1：原生SQL查询（推荐） #

该方案适配你仅需使用这一个查询的业务场景，完全复用你已优化完成的SQL语句性能，和直接查询数据库耗时一致，开发成本最低。

import { DataSource } from 'typeorm';

// 需提前初始化好TypeORM的DataSource实例并连接数据库
async function fetchFullProductData(dataSource: DataSource) {
  // 参数化查询避免SQL注入风险
  const querySql = `
SELECT 
  a.Disabled, 
  a.recordnumber ItemID, 
  CASE WHEN c.ParentID IS NOT NULL THEN 'IS Parent' ELSE 'IS NOT Parent' END AS ParentItem, 
  a.SKU, 
  b.ProductName, 
  b.Description, 
  a.Image MainImage, 
  b.LanguageCode, 
  a.CategoryID, 
  a.SortOrder, 
  a.Weight, 
  a.Height, 
  a.Length, 
  a.Width, 
  a.last_modified, 
  a.FlagCancer, 
  a.FlagBirthDefects,
  d.recordnumber DiscountID, 
  d.last_modified DiscountIDUpdated, 
  e.AssociateTypeID PriceGroupID, 
  f.OrderTypeID, 
  g.RegionID, 
  h.StoreID, 
  d.Start DiscountStart, 
  d.[End] DiscountEnd, 
  d.Price, 
  d.PriceCurrency, 
  d.CV, 
  d.QV, 
  d.RewardPointsEarned RewardPoints
FROM INV_Inventory a
INNER JOIN INV_LanguageValues b ON a.recordnumber = b.ItemID
LEFT JOIN (
  SELECT a.ItemID ParentID
  FROM INV_OptionItemsKeys a
  GROUP BY a.ItemID
) c ON a.recordnumber = c.ParentID
INNER JOIN INV_Discounts d ON a.recordnumber = d.ItemID
INNER JOIN INV_Discount_ATypes e ON d.recordnumber = e.DiscountID
INNER JOIN INV_Discount_OrderTypes f ON d.recordnumber = f.DiscountID
INNER JOIN INV_Discount_Regions g ON d.recordnumber = g.DiscountID
INNER JOIN INV_Discount_StoreMap h ON d.recordnumber = h.DiscountID
WHERE c.ParentID IS NOT NULL AND b.LanguageCode = ? AND g.RegionID = ?
  `;
  const result = await dataSource.query(querySql, ['en', 1]);
  return result;
}

该方案优势：

• 无需额外编写实体配置、关联映射逻辑，接入成本极低
• 完全保留原生SQL的1秒查询性能，远高于调用第三方API的效率
• 参数化查询符合安全规范，不存在SQL注入风险

方案2：QueryBuilder构建查询（适合后续动态扩展场景） #

如果后续需要新增动态筛选、分页、排序等逻辑，可以使用TypeORM的QueryBuilder实现该查询：

import { DataSource } from 'typeorm';

// 注意：需提前创建好对应数据表的基础实体类，仅需配置表名和主键字段即可正常使用
async function fetchFullProductDataByQB(dataSource: DataSource) {
  return await dataSource.createQueryBuilder()
    .select([
      'a.Disabled AS Disabled',
      'a.recordnumber AS ItemID',
      "CASE WHEN c.ParentID IS NOT NULL THEN 'IS Parent' ELSE 'IS NOT Parent' END AS ParentItem",
      'a.SKU AS SKU',
      'b.ProductName AS ProductName',
      'b.Description AS Description',
      'a.Image AS MainImage',
      'b.LanguageCode AS LanguageCode',
      'a.CategoryID AS CategoryID',
      'a.SortOrder AS SortOrder',
      'a.Weight AS Weight',
      'a.Height AS Height',
      'a.Length AS Length',
      'a.Width AS Width',
      'a.last_modified AS last_modified',
      'a.FlagCancer AS FlagCancer',
      'a.FlagBirthDefects AS FlagBirthDefects',
      'd.recordnumber AS DiscountID',
      'd.last_modified AS DiscountIDUpdated',
      'e.AssociateTypeID AS PriceGroupID',
      'f.OrderTypeID AS OrderTypeID',
      'g.RegionID AS RegionID',
      'h.StoreID AS StoreID',
      'd.Start AS DiscountStart',
      'd.[End] AS DiscountEnd',
      'd.Price AS Price',
      'd.PriceCurrency AS PriceCurrency',
      'd.CV AS CV',
      'd.QV AS QV',
      'd.RewardPointsEarned AS RewardPoints'
    ])
    .from('INV_Inventory', 'a')
    .innerJoin('INV_LanguageValues', 'b', 'a.recordnumber = b.ItemID')
    .leftJoin(
      subQb => subQb.select('itemKey.ItemID', 'ParentID')
        .from('INV_OptionItemsKeys', 'itemKey')
        .groupBy('itemKey.ItemID'),
      'c',
      'a.recordnumber = c.ParentID'
    )
    .innerJoin('INV_Discounts', 'd', 'a.recordnumber = d.ItemID')
    .innerJoin('INV_Discount_ATypes', 'e', 'd.recordnumber = e.DiscountID')
    .innerJoin('INV_Discount_OrderTypes', 'f', 'd.recordnumber = f.DiscountID')
    .innerJoin('INV_Discount_Regions', 'g', 'd.recordnumber = g.DiscountID')
    .innerJoin('INV_Discount_StoreMap', 'h', 'd.recordnumber = h.DiscountID')
    .where('c.ParentID IS NOT NULL')
    .andWhere('b.LanguageCode = :lang', { lang: 'en' })
    .andWhere('g.RegionID = :regionId', { regionId: 1 })
    .getRawMany();
}

内容的提问来源于stack exchange，提问作者BerryDev