如何将指定INSERT SELECT SQL转换为可用的TypeORM Query Builder格式

目标SQL查询 #

INSERT INTO  allocation (
          effort, "startDate", "endDate", "employeeId", "projectId"
        ) 
        SELECT Distinct
          request_allocation_detail.effort, 
          request_allocation_detail."startDate", 
          request_allocation_detail."endDate", 
          request_allocation_detail."employeeId", 
          request_allocation."projectId"
        From 
          public.request_allocation 
          inner join public.request_allocation_detail ON request_allocation_detail."requestAllocationId" = request_allocation.id 
        where 
          request_allocation_detail.id = '${currentRequestDetailId}'

原错误代码 #

const db= this.allocationRepository
  .createQueryBuilder('allocation')
  .insert()
  .into('allocation')
  .values(['effort', 'startDate', 'endDate', 'employeeId', 'projectId'])
  .select([
    'request_allocation_detail.effort',
    'request_allocation_detail."startDate"',
    'request_allocation_detail."endDate"',
    'request_allocation_detail."employeeId"',
    'request_allocation."projectId"',
  ])
  .distinct()
  .from('request_allocation', 'request_allocation')
  .innerJoinAndSelect(
    'request_allocation_detail',
    'request_allocation_detail',
    'request_allocation_detail.id = request_allocation.id'
  )
  .where('request_allocation_detail.id = :id', {
    id: currentRequestDetailId,
  });

修复后的代码 #

const result = await this.allocationRepository
  .createQueryBuilder()
  .insert()
  .into(Allocation) // 优先使用实体类，若需字符串表名则替换为'allocation'
  .columns(['effort', 'startDate', 'endDate', 'employeeId', 'projectId'])
  .select([
    'rad.effort',
    'rad."startDate"',
    'rad."endDate"',
    'rad."employeeId"',
    'ra."projectId"',
  ])
  .distinct(true)
  .from('request_allocation', 'ra')
  .innerJoin(
    'request_allocation_detail',
    'rad',
    'rad."requestAllocationId" = ra.id'
  )
  .where('rad.id = :id', { id: currentRequestDetailId })
  .execute();

关键修复点 #

• 替换.values()为.columns()：INSERT...SELECT 场景下，需要用.columns()指定插入的目标列，.values()仅适用于直接传入静态值的场景。
• 修正JOIN关联条件：原代码的关联条件错误，改为与目标SQL一致的rad."requestAllocationId" = ra.id。
• 简化Query Builder初始化：INSERT操作无需指定实体别名，直接调用createQueryBuilder()即可。
• 添加.execute()执行语句：Query Builder仅构建查询逻辑，必须调用.execute()才会触发数据库操作，建议添加await处理异步。
• 规范.distinct()用法：TypeORM中需传入true启用去重，原无参写法不符合API规范。
• 替换.innerJoinAndSelect为.innerJoin()：此处仅需关联查询数据用于插入，不需要返回关联实体，.innerJoin()更合适。
• 优化表别名：使用ra、rad简化别名，提升代码可读性。

内容的提问来源于stack exchange，提问作者Cường Trần