TypeORM无法获取关联列数据问题求助

实体代码 #

ConsigneeDetails实体 #

export class ConsigneeDetails {
  @PrimaryColumn('bigint', {name: 'consignee_id'})
  consigneeId!: string;

  @Column({type: 'varchar', name: 'company_name', length: 255})
  companyName!: string;

  @Column({type: 'varchar', name: 'email', length: 200})
  email!: string;

  @Column({type: 'varchar', name: 'address', length: 255})
  address!: string;

  @Column({type: 'varchar', name: 'postcode', length: 20})
  postcode!: string;

  @Column({type: 'varchar', name: 'city', length: 50})
  city!: string | null;

  @ManyToOne(() => Country, country => country.arrConsigneeDetails)
  @JoinColumn({
    name: 'country_uid',
    referencedColumnName: 'countryUid',
    foreignKeyConstraintName: 'fk_country_uid',
  })
  countryUid!: Country;
}

Country实体 #

export class Country {
  @PrimaryGeneratedColumn({type: 'bigint', name: 'seq_num'})
  seqNum!: string;

  @Column({type: 'character', name: 'country_uid', unique: true, length: 2})
  countryUid!: string;

  @Column({type: 'varchar', name: 'country_name', length: 50})
  countryName!: string;

  @Column({type: 'varchar', name: 'timezone', length: 9})
  timezone!: string;

  @OneToMany(() => ConsigneeDetails, consigneeDetails => consigneeDetails.countryUid)
  arrConsigneeDetails!: ConsigneeDetails[];
}

问题详情 #

使用QueryBuilder查询ConsigneeDetails数据时，尝试关联获取Country的countryUid和countryName字段，但返回结果中缺失这两个值。

预期返回结果 #

{
  "consigneeId": "1",
  "companyName": "Testing Company",
  "email": "test@gmail.com",
  "address": "Address 1",
  "postcode": "12345",
  "city": "City 1",
  "status": 1,
  "country": "TEST",
  "countryName": "Testing Country"
}

实际返回结果 #

{
  "consigneeId": "1",
  "companyName": "Testing Company",
  "email": "test@gmail.com",
  "address": "Address 1",
  "postcode": "12345",
  "city": "City 1",
  "status": 1
}

当前QueryBuilder代码 #

const builder = consigneeDetailsRepo
        .createQueryBuilder('consignee')
        .leftJoin(Country, 'country', 'consignee.country_uid = country.countryUid')
        .select([
          'consignee.consigneeId',
          'consignee.companyName',
          'consignee.email',
          'consignee.address',
          'consignee.postcode',
          'consignee.city',
        ])
        .addSelect('country.countryUid', 'country')
        .addSelect('country.countryName', 'countryName');

错误原因及修正方案 #

错误点 #

1. 关联条件字段名错误：QueryBuilder中使用实体别名consignee后，应该引用实体属性名consignee.countryUid（对应数据库字段country_uid），而非直接写数据库字段名consignee.country_uid。
2. 自定义别名字段无法自动映射到实体：用addSelect指定的自定义别名字段，TypeORM默认不会合并到返回的实体对象中，需调整查询方式或手动处理结果。

修正方案 #

方案一：获取原始查询结果

直接用getRawMany()获取包含自定义字段的原始数据：

const result = await consigneeDetailsRepo
  .createQueryBuilder('consignee')
  .leftJoin(Country, 'country', 'consignee.countryUid = country.countryUid')
  .select([
    'consignee.consigneeId',
    'consignee.companyName',
    'consignee.email',
    'consignee.address',
    'consignee.postcode',
    'consignee.city',
  ])
  .addSelect('country.countryUid', 'country')
  .addSelect('country.countryName', 'countryName')
  .getRawMany();

方案二：实体查询后手动转换格式

先查询关联实体，再手动调整结果结构匹配预期：

const consignees = await consigneeDetailsRepo
  .createQueryBuilder('consignee')
  .leftJoinAndSelect('consignee.countryUid', 'country')
  .select([
    'consignee.consigneeId',
    'consignee.companyName',
    'consignee.email',
    'consignee.address',
    'consignee.postcode',
    'consignee.city',
    'country.countryUid',
    'country.countryName'
  ])
  .getMany();

const result = consignees.map(item => ({
  ...item,
  country: item.countryUid.countryUid,
  countryName: item.countryUid.countryName,
  countryUid: undefined // 可选：移除原关联属性
}));

方案三：直接在select中指定别名

通过select语句直接构造目标字段结构：

const result = await consigneeDetailsRepo
  .createQueryBuilder('consignee')
  .leftJoin(Country, 'country', 'consignee.countryUid = country.countryUid')
  .select([
    'consignee.consigneeId',
    'consignee.companyName',
    'consignee.email',
    'consignee.address',
    'consignee.postcode',
    'consignee.city',
    'country.countryUid AS country',
    'country.countryName AS countryName'
  ])
  .getRawMany();

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