NestJS+TypeORM报错：Cannot query across one-to-many for property SEARCHTABLE

错误信息 #

Error: Cannot query across one-to-many for property SEARCHTABLE

表结构 #

#TABLE1
TABLE1{
    id: primary key int,
    title : varchar(255),
}
;
#TABLE2
TABLE2{
    id: primary key int,
    title : varchar(255),
    table1Id : number FK
}
;
#SEARCHTABLE
SEARCHTABLE{
    id: primary key int,
    searchKeyword : varchar(255),
    table1Id : number FK,
    table2Id : number FK
}
;

关联关系 #

• TABLE1与TABLE2为一对多
• TABLE1、TABLE2分别与SEARCHTABLE为一对多

原实体代码（存在错误） #

#TABLE1.entity.ts
@ManyToOne(() => TABLE2, (t2) => t2.table1s, {
    onDelete: 'CASCADE',
})
table2: TABLE2

#TABLE2.entity.ts
@OneToMany(() => TABLE1, (t1) => t1.table2)
table1s: TABLE1[]

#TABLE1, TABLE2
@OneToMany(() => SEARCHTABLE, (s) => s.table1 or s.table2)
searchT: SEARCHTABLE; // 错误：一对多必须是数组类型，且关联写法错误

#SEARCHTABLE.entity.ts
@ManyToOne(() => TABLE1 or TABLE2, (t1 or t2) => t1.searchT or t2.searchT, { onDelete: 'CASCADE' })
@JoinColumn({
    referencedColumnName: 'id',
    name: 'table1Id',
})
table1 or table2: TABLE1 or TABLE2; // 错误：需分别定义两个关联

原触发报错的查询代码 #

constructor(
    ...
    @InjectRepository(TABLE1)
    private readonly table1Repository: Repository<TABLE1>
){}

...

await this.table1Repository.find({
   relations:['table2','table2.searchtable'], // 错误：属性名与实体不一致
   table2:{ // 错误：缺少where关键字，查询条件需放在where下
     searchtable:{
       searchKeyword : ${searchKeyword}
     }
   }
})

解决方案 #

1. 修正实体关联代码 #

首先修复TypeORM实体的关联定义错误，这是导致报错的核心原因：

TABLE1.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany } from "typeorm";
import { TABLE2 } from "./TABLE2.entity";
import { SEARCHTABLE } from "./SEARCHTABLE.entity";

@Entity()
export class TABLE1 {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @ManyToOne(() => TABLE2, (t2) => t2.table1s, { onDelete: 'CASCADE' })
  table2: TABLE2;

  @OneToMany(() => SEARCHTABLE, (s) => s.table1)
  searchT: SEARCHTABLE[]; // 一对多关系必须是数组类型
}

TABLE2.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm";
import { TABLE1 } from "./TABLE1.entity";
import { SEARCHTABLE } from "./SEARCHTABLE.entity";

@Entity()
export class TABLE2 {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @OneToMany(() => TABLE1, (t1) => t1.table2)
  table1s: TABLE1[];

  @OneToMany(() => SEARCHTABLE, (s) => s.table2)
  searchT: SEARCHTABLE[]; // 一对多关系必须是数组类型
}

SEARCHTABLE.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, JoinColumn } from "typeorm";
import { TABLE1 } from "./TABLE1.entity";
import { TABLE2 } from "./TABLE2.entity";

@Entity()
export class SEARCHTABLE {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  searchKeyword: string;

  @ManyToOne(() => TABLE1, (t1) => t1.searchT, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'table1Id' })
  table1: TABLE1;

  @ManyToOne(() => TABLE2, (t2) => t2.searchT, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'table2Id' })
  table2: TABLE2;
}

2. 修正查询代码 #

修复查询代码的结构错误，确保查询条件放在where字段下，且关联名称与实体一致：

await this.table1Repository.find({
  relations: ['table2', 'table2.searchT'], // 与实体中的属性名一致
  where: { // 查询条件必须包裹在where中
    table2: {
      searchT: {
        searchKeyword: searchKeyword
      }
    }
  }
})

3. 子表过滤的补充说明 #

如果需要确保返回的searchT数组只包含匹配searchKeyword的记录（而不是过滤掉没有匹配子表的主记录），上述find方法无法实现，此时需要用QueryBuilder，但仍能保持你需要的返回结构：

await this.table1Repository.createQueryBuilder('table1')
  .leftJoinAndSelect('table1.table2', 'table2')
  .leftJoinAndSelect('table2.searchT', 'searchT', 'searchT.searchKeyword = :keyword', { keyword: searchKeyword })
  .getMany();

这个写法会返回{ ..., table2: { ..., searchT: [...] } }结构，其中searchT仅包含符合关键字条件的记录。

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