Cannot query across one-to-many for property xxx in nestjs,typeorm

Question

I'm trying to create a table for lookup and apply that.

However, the following error occurs and I am unable to solve the problem.

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

My table structure is as below.

#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 - ONE TO MANY - TABLE2

TABLE1,2 - ONE TO MANY - SEARCHTABLE

The code where the error occurred is below.

#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:{
     searchtable:{
       searchKeyword : ${searchKeyword}
     }
   }
})

I tried to change it to queryBuilder, but I need the structure of { ..., searchtable :{...}} so I want to use find.

How can I solve this?

In the above query, searchtable:{ searchKeyword : ${searchKeyword} } Excluding this part works fine.

Answer 1

typeorm version is important, make sure that it is updated. if you have this schema and your condition is on t2 maybe this approach works.

table1(t1_id) -> searchTable(t1_fk_id,t2_fk_id) <- table2(t2_id)

table1Repository.find({
            relations: ['searchTable','searchTable.table2'],
            where: {
                'searchTable.table2': { id: 2},
            },
        });

but if you find on search table, nested with level1 probably works:

table1Repository.find({
            relations: ['searchTable'],
            where: {
                'searchTable': { searchKeyword : `${searchKeyword}`},
            },
        });

I hope it is useful.