无法根据 TypeOrm 中的外键过滤数据 [英] Can't filter data according foreign key in TypeOrm

问题描述

我使用 Nest Js、PostgresQl 和 Typeorm.我在 typeorm 中有这 2 个实体:

I use Nest Js, PostgresQl and Typeorm. I have these 2 entities in typeorm:

export class Meta {
    @PrimaryGeneratedColumn({name: "metaId"})
    metaId: number;

    @Column({nullable: true})
    name: string;

    @OneToMany(() => TablesEntity, table => table.metaId, {eager: true, cascade: true})
    metaTables: TablesEntity[];
}
//
export class TablesEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(() => Meta, meta => meta.metaTables)
    @JoinColumn({name: "metaId"})
    metaId: Meta;
}

现在我想根据过滤值获取所有数据:

And now i want to get all data according to a filtered value:

const meta = await this.metaRepository.findOne({
  where: {
    metaId: metaId,
    metaTables: [{
        status: Not('white')
    }]
  },
  relations: ["metaTables"]
});

所以我想获取所有没有状态的数据:'white'，但我收到一个错误:没有实体列metaTables"；找到了.
为什么会出现这个错误以及如何解决?

So i want to take all data that don't have the status: 'white', but i get an error: No entity column "metaTables" was found.
Why this error appear and how to solve it?

推荐答案

不幸的是，typeorm 无法处理这些方法(findOne、findMany、update 等...)内的嵌套查询，尝试使用上面.然而，有几种不同的解决方案，但它们都使用了更复杂的方法.

Unfortunately, typeorm cannot handle nested queries within these methods (findOne, findMany, update etc...), what you've tried to use above. There are several different solutions however, but they all use a bit more complex approach.

最相似的解决方案是，如果您使用 find 方法，但使用查询构建器的所有参数对其进行配置:

The most similar solution is if you use the find method, but configure it with all the parameters of a query-builder:

await connection.getRepository(Meta).findOne({
  where: (qb: SelectQueryBuilder<Meta>) => {
    qb.where({
      metaId: metaId,
    }).andWhere("metaTables.status != :status", { status: 'white' });
  },
  join: {
    alias: "meta",
    innerJoin: {
      metaTables: "meta.metaTables",
    },
  },
});

另一种方法是，如果您只是使用查询构建器并构造以下查询:

The other approach is if you simply use the query-builder and construct the following query:

await connection
  .getRepository(Meta)
  .createQueryBuilder("meta")
  .innerJoin("meta.metaTables", "metaTables")
  .where({ metaId: metaId })
  .andWhere("metaTables.status != :status", { status: "white" })
  .getOne();

然而，还有第三种解决方案，但它仅适用于 <=0.2.24.此解决方案以这种方式构造 where 对象，每个关系过滤器都显示为单个字符串，其中列与点连接.下面是一个例子:

There is a third solution however, but it works only <=0.2.24. This solution constructs the where object in that way, that each relation filter appears as a single string where the columns are concatenated with dots. Here is an example:

await connection.getRepository(Meta).findOne({
  where: {
    metaId: metaId,
    "metaTables.status": Not("white"),
  },
  relations: ["metaTables"],
});

这篇关于无法根据 TypeOrm 中的外键过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！