如何在TypeORM中使用LEFT JOIN LATERAL语法

原始SQL参考 #

SELECT * FROM blocked_times bt 
 LEFT JOIN LATERAL (
      SELECT * FROM bookings bk WHERE bt."startTime" < bk."endTime"
 ) bk ON bk."clinicId" = bt."clinicId"

TypeORM 实现代码 #

以下基于TypeORM 0.2.x/0.3.x版本均可运行，适配PostgreSQL数据库（LATERAL为PostgreSQL专属语法）：

// 0.2.x 版本写法
import { getRepository } from 'typeorm';
import { BlockedTime } from './entities/BlockedTime';
import { Booking } from './entities/Booking';

const blockedTimesWithBookings = await getRepository(BlockedTime)
  .createQueryBuilder('bt')
  .leftJoinLateral(
    (subQuery) => subQuery
      .select('*')
      .from(Booking, 'bk')
      .where('bt.startTime < bk.endTime'),
    'bk',
    'bk.clinicId = bt.clinicId'
  )
  .select('*')
  .getRawMany();

如果你使用的是0.3.x版本，仅需调整实例获取方式即可：

// 0.3.x 版本写法
import { AppDataSource } from './data-source'; // 替换为你的数据源初始化文件路径
import { BlockedTime } from './entities/BlockedTime';
import { Booking } from './entities/Booking';

const blockedTimesWithBookings = await AppDataSource.getRepository(BlockedTime)
  .createQueryBuilder('bt')
  .leftJoinLateral(
    (subQuery) => subQuery
      .select('*')
      .from(Booking, 'bk')
      .where('bt.startTime < bk.endTime'),
    'bk',
    'bk.clinicId = bt.clinicId'
  )
  .select('*')
  .getRawMany();

补充说明 #

• 若不需要返回全量字段，可替换.select('*')为指定字段列表，减少数据传输开销
• 若需要返回映射后的实体对象，可提前在BlockedTime实体中定义与Booking的关联关系，替换getRawMany()为getMany()即可
• 该语法仅支持PostgreSQL数据库，MySQL等其他数据库不支持LATERAL JOIN，无法使用该实现

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