TypeORM关联查询：过滤主表时如何保留关联表全部数据

问题原因 #

你当前的查询把c_Docks.name = 'dock 1'放在了全局WHERE条件中，会过滤掉所有关联dock不符合该条件的行，相当于直接把LEFT JOIN的其他dock记录全部排除，所以只能拿到名称为dock 1的结果。

方案1：使用EXISTS子查询（推荐，性能更优） #

通过子查询判断当前marine是否存在至少一个名称为dock 1的dock，不会影响外层关联返回的所有dock记录。

修改后SQL #

SELECT `c`.`id`                AS `c_id`,
       `c`.`name`              AS `c_name`,
       `c`.`description`       AS `c_description`,
       `c`.`FkId`              AS `c_FkId`,
       `c`.`FkModel`           AS `c_FkModel`,
       `c`.`isActive`          AS `c_isActive`,
       `c`.`createdAt`         AS `c_createdAt`,
       `c`.`updatedAt`         AS `c_updatedAt`,
       `c`.`phones`            AS `c_phones`,
       `c`.`emails`            AS `c_emails`,
       `c`.`mainImage`         AS `c_mainImage`,
       `c`.`galleryImages`     AS `c_galleryImages`,
       `c`.`addressDetailsId`  AS `c_addressDetailsId`,
       `c_Docks`.`id`          AS `c_Docks_id`,
       `c_Docks`.`name`        AS `c_Docks_name`,
       `c_Docks`.`description` AS `c_Docks_description`,
       `c_Docks`.`FkId`        AS `c_Docks_FkId`,
       `c_Docks`.`FkModel`     AS `c_Docks_FkModel`,
       `c_Docks`.`isActive`    AS `c_Docks_isActive`,
       `c_Docks`.`createdAt`   AS `c_Docks_createdAt`,
       `c_Docks`.`updatedAt`   AS `c_Docks_updatedAt`,
       `c_Docks`.`MarineId`    AS `c_Docks_MarineId`
FROM `marine` `c`
LEFT JOIN `dock` `c_Docks` ON `c_Docks`.`MarineId` = `c`.`id`
WHERE `c`.`isActive` = true
  AND EXISTS (
    SELECT 1 FROM `dock` 
    WHERE `dock`.`MarineId` = `c`.`id` 
      AND `dock`.`name` = 'dock 1'
  )
# ORDER BY c_id DESC

对应TypeORM QueryBuilder写法（typeorm@0.2.37适用） #

import { getConnection } from 'typeorm';
import { Marine } from './entity/Marine'; // 替换为你自己的Marine实体路径
import { Dock } from './entity/Dock'; // 替换为你自己的Dock实体路径

const result = await getConnection()
  .createQueryBuilder(Marine, 'c')
  // 假设Marine实体中关联Dock的属性名为docks，根据你的实际实体配置修改
  .leftJoinAndSelect('c.docks', 'c_Docks')
  .where('c.isActive = :isActive', { isActive: true })
  .andWhere((qb) => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Dock, 'dock')
      .where('dock.MarineId = c.id')
      .andWhere('dock.name = :dockName', { dockName: 'dock 1' })
      .getQuery();
    return `EXISTS ${subQuery}`;
  })
  // .orderBy('c.id', 'DESC')
  .getMany();

方案2：先查符合条件的Marine ID再关联查询 #

逻辑更直观，适合数据量不大的场景。

对应TypeORM QueryBuilder写法 #

import { getConnection } from 'typeorm';
import { Marine } from './entity/Marine';
import { Dock } from './entity/Dock';

// 第一步：查询所有存在dock 1的marine id
const validMarineIds = await getConnection()
  .createQueryBuilder(Dock, 'dock')
  .select('DISTINCT dock.MarineId', 'id')
  .where('dock.name = :dockName', { dockName: 'dock 1' })
  .getRawMany<{ id: number }>()
  .then(res => res.map(item => item.id));

// 第二步：查询这些marine的所有关联dock
const result = await getConnection()
  .createQueryBuilder(Marine, 'c')
  .leftJoinAndSelect('c.docks', 'c_Docks')
  .where('c.isActive = :isActive', { isActive: true })
  .andWhere('c.id IN (:...ids)', { ids: validMarineIds })
  // .orderBy('c.id', 'DESC')
  .getMany();

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