TypeORM中FindOne关联查询触发多余SQL的优化方法

背景信息 #

数据库表结构 #

• 表Appointments：id | start_time | patientId | ...其他字段
• 表Patient：id | name | last_name | ...其他字段

实体关联定义 #

在Patient实体中定义了一对多关联：

@OneToMany(() => AppointmentEntity, (appt) => appt.patient)
appointments: Relation<AppointmentEntity>[];

当前实现代码 #

要根据预约ID获取详情及患者姓名，当前代码如下：

async getAppt(apptId: any) {
    return this.apptRepo.findOne({
      relations: ['patient'],
      where: { id: apptId },
      select: {
        id: true,
        start_time: true,
        patient: {
          name: true,
        },
      },
    });
  }

问题描述 #

代码能返回预期结果，但会触发两条不必要的SQL查询，生成的SQL如下：

query: SELECT DISTINCT "distinctAlias"."AppointmentEntity_id" AS "ids_AppointmentEntity_id" FROM (SELECT "AppointmentEntity"."id" AS "AppointmentEntity_id", "AppointmentEntity"."start_time" AS "AppointmentEntity_start_time", "AppointmentEntity__AppointmentEntity_patient"."name" AS "AppointmentEntity__AppointmentEntity_patient_name" FROM "appointments" "AppointmentEntity" LEFT JOIN "patients" "AppointmentEntity__AppointmentEntity_patient" ON "AppointmentEntity__AppointmentEntity_patient"."id"="AppointmentEntity"."patientId" WHERE ("AppointmentEntity"."id" = $1)) "distinctAlias" ORDER BY "AppointmentEntity_id" ASC LIMIT 1 -- PARAMETERS: ["appt_id_xxx"]
query: SELECT "AppointmentEntity"."id" AS "AppointmentEntity_id", "AppointmentEntity"."start_time" AS "AppointmentEntity_start_time", "AppointmentEntity__AppointmentEntity_patient"."name" AS "AppointmentEntity__AppointmentEntity_patient_name" FROM "appointments" "AppointmentEntity" LEFT JOIN "patients" "AppointmentEntity__AppointmentEntity_patient" ON "AppointmentEntity__AppointmentEntity_patient"."id"="AppointmentEntity"."patientId" WHERE ( ("AppointmentEntity"."id" = $1) ) AND ( "AppointmentEntity"."id" IN ($2) ) -- PARAMETERS: ["appt_id_xxx","appt_id_xxx"]

期望生成单条SQL，类似：

select b.id, b.start_time, p.name  from appointments b
inner join patients p on p.id = b."patientId" 
where b.id = 'appt_id_xxx';

解决方案 #

方式1：使用createQueryBuilder手动构建查询 #

这是最可靠的方式，完全自定义查询逻辑，确保生成单条SQL：

async getAppt(apptId: any) {
  return this.apptRepo.createQueryBuilder('appt')
    .innerJoinAndSelect('appt.patient', 'patient')
    .select(['appt.id', 'appt.start_time', 'patient.name'])
    .where('appt.id = :id', { id: apptId })
    .getOne();
}

该代码会直接生成你期望的关联查询，只执行一次SQL请求。

方式2：调整findOne的参数配置 #

如果偏好使用findOne语法，可以尝试改用数组形式的select配置，避免嵌套对象形式导致的额外查询：

async getAppt(apptId: any) {
  return this.apptRepo.findOne({
    relations: ['patient'],
    where: { id: apptId },
    select: ['id', 'start_time', 'patient.name'],
    loadEagerRelations: false,
  });
}

注意：该方式的有效性可能依赖TypeORM版本，部分旧版本可能仍存在问题。

方式3：确认实体关联完整性 #

检查AppointmentEntity中是否正确定义了与Patient的多对一关联，这是TypeORM正确生成查询的前提：

// 在AppointmentEntity中添加
@ManyToOne(() => PatientEntity, (patient) => patient.appointments)
patient: Relation<PatientEntity>;

如果缺少该关联，可能导致TypeORM生成异常的查询逻辑。

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