基于TypeORM+MySQL的闸口车辆到港列表查询逻辑问题咨询

背景 #

我是MySQL新手，当前使用TypeORM、MySQL和Express开发系统，遇到业务逻辑实现问题，特向您咨询：

业务逻辑 #

系统中有带licenseNo（车牌号）和driverPhone（司机手机号）的车辆，车辆抵达各闸口后，对应闸口管理员需记录车辆到港信息。

需求规则 #

• 若车辆已抵达某闸口并被该闸口管理员记录，则该闸口的到港列表UI中不再显示此车辆；
• 其他未记录该车辆到港的闸口，其到港列表UI需显示此车辆；
• 若某闸口管理员标记该车辆的本次到港为isLastGate（最后闸口），则所有闸口的到港列表均不再显示此车辆；
• 所有规则仅当日有效（当日从6点开始计算）。

现有实体字段 #

ProcessingGates实体包含以下字段：

• licensePlate: string
• driverPhone: string
• isLastGate: boolean
• gateId: string

接口请求会传入gateId参数。

我尝试的查询（未达到预期） #

查询1 #

const values = await ProcessingGatesRepository.createQueryBuilder()
      .select('ar.licenseNo, MAX(ar.createdAt) AS latestTimestamp')
      .from(ProcessingGate, 'ar')
      .where('ar.gateId != :gateId', { gateId })
      .andWhere(
        'NOT EXISTS (SELECT 1 FROM processing_gate ar2 WHERE ar2.licenseNo = ar.licenseNo AND ar2.gateId = :gateId)'
      )
      .groupBy('ar.licenseNo')
      .getRawMany();

console.log(values);

查询2 #

const now = new Date();
const startOfToday = new Date(now);
startOfToday.setHours(6, 0, 0, 0);

const whereClauses: FindOptionsWhere<ProcessingGate> = {};
if (!!searchText) {
  whereClauses['licenseNo'] = ILike(`%${searchText}%`);
}
whereClauses['gateId'] = Not(Equal(gateId));
whereClauses['isLastGate'] = Equal(false);
whereClauses['createdAt'] = MoreThanOrEqual(startOfToday);
const options: FindManyOptions<ProcessingGate> = {
  where: whereClauses,
  relations: ['gate'],
  order: { createdAt: 'DESC' },
};
if (limit != undefined && offset != undefined) {
  options['skip'] = offset;
  options['take'] = limit;
}
return await ProcessingGateRepository.findAndCount(options);

正确查询方案 #

核心思路 #

要满足所有规则，需同时判断三个核心条件：

1. 记录仅包含当日（6点起）的车辆数据；
2. 当前闸口未记录过该车辆的到港信息；
3. 该车辆未被任何闸口标记为isLastGate。
   同时需保证每个车牌号仅返回一条最新记录。

QueryBuilder实现方案（推荐，灵活可控） #

const now = new Date();
const startOfToday = new Date(now);
startOfToday.setHours(6, 0, 0, 0);

const result = await ProcessingGatesRepository.createQueryBuilder('pg')
  // 限定当日6点后的有效记录
  .where('pg.createdAt >= :startOfToday', { startOfToday })
  // 排除当前闸口已记录的车辆
  .andWhere(`NOT EXISTS (
    SELECT 1 FROM processing_gate pg_current
    WHERE pg_current.licensePlate = pg.licensePlate
    AND pg_current.gateId = :gateId
    AND pg_current.createdAt >= :startOfToday
  )`, { gateId, startOfToday })
  // 排除被标记为最后闸口的车辆
  .andWhere(`NOT EXISTS (
    SELECT 1 FROM processing_gate pg_last
    WHERE pg_last.licensePlate = pg.licensePlate
    AND pg_last.isLastGate = true
    AND pg_last.createdAt >= :startOfToday
  )`, { startOfToday })
  // 按车牌号分组，取最新的到港记录
  .groupBy('pg.licensePlate')
  .select(`
    pg.licensePlate, 
    pg.driverPhone, 
    MAX(pg.createdAt) AS latestArrivalTime
  `)
  // 支持车牌号搜索过滤
  .andWhere(!!searchText ? 'pg.licensePlate ILIKE :searchText' : '1=1', { searchText: `%${searchText}%` })
  // 分页配置
  .skip(offset || 0)
  .take(limit || 10)
  .getRawMany();

return result;

逻辑说明 #

• 两个NOT EXISTS子查询分别过滤掉当前闸口已记录、以及被标记为最后闸口的车辆；
• createdAt >= startOfToday确保规则仅对当日6点后的记录生效；
• GROUP BY licensePlate结合MAX(createdAt)保证每个车牌号只返回最新的到港数据；
• 保留了搜索和分页功能，适配业务场景需求。

FindOptions替代方案（适合简单场景） #

如果偏好使用TypeORM的FindOptions语法，可通过子查询实现：

const now = new Date();
const startOfToday = new Date(now);
startOfToday.setHours(6, 0, 0, 0);

// 子查询：当前闸口已记录的车牌号
const currentGateCars = ProcessingGatesRepository.createQueryBuilder('pg')
  .select('pg.licensePlate')
  .where('pg.gateId = :gateId', { gateId })
  .andWhere('pg.createdAt >= :startOfToday', { startOfToday });

// 子查询：被标记为最后闸口的车牌号
const lastGateCars = ProcessingGatesRepository.createQueryBuilder('pg')
  .select('pg.licensePlate')
  .where('pg.isLastGate = true')
  .andWhere('pg.createdAt >= :startOfToday', { startOfToday });

const whereClauses: FindOptionsWhere<ProcessingGate> = {
  createdAt: MoreThanOrEqual(startOfToday),
  licensePlate: Not(In([...currentGateCars.getQuery(), ...lastGateCars.getQuery()])),
};

if (!!searchText) {
  whereClauses.licensePlate = And(whereClauses.licensePlate, ILike(`%${searchText}%`));
}

const options: FindManyOptions<ProcessingGate> = {
  where: whereClauses,
  relations: ['gate'],
  order: { createdAt: 'DESC' },
  skip: offset || 0,
  take: limit || 10,
  distinct: ['licensePlate'], // 去重确保每个车牌号仅出现一次
};

return await ProcessingGatesRepository.findAndCount(options);

注意：distinct在MySQL中需配合特定排序规则，若出现异常优先使用QueryBuilder方案。

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