TypeORM中查询虚拟列slugWithUuid报错，求可行查询方案

我定义了如下Course实体，通过@AfterLoad钩子生成虚拟列slugWithUuid，该列由slug与additionalInfoJSON中的uuid拼接而成：

@Entity()
export class Course extends DefaultCredential {
  @Column({ type: 'varchar', length: 75 })
  title: string;

  @Column({ type: 'varchar', length: 50 })
  slug: string;

  @Column({ type: 'json', nullable: true })
  additionalInfoJSON: any;

  slugWithUuid: string;
  @AfterLoad()
  setCustomProperty() {
    this.slugWithUuid =
      this.slug + '-' + JSON.parse(this.additionalInfoJSON)?.uuid;
  }

  @BeforeInsert()
  insertShortUUID() {
    const shortUUID = generateUUID(6);
    const additionalInfo = {
      ...this.additionalInfoJSON,
      uuid: shortUUID,
    };
    this.additionalInfoJSON = JSON.stringify(additionalInfo);
  }
}

当我使用如下代码查询该虚拟列时：

async findBySlug(slug: string) {
    const entity = await this.repos.findOne({
      where: { slugWithUuid: slug }
    });

    if (!entity) {
      throw new NotFoundException('Entity not found with this slug');
    }
    return entity;
  }

出现报错：

[Nest] 637270 - 10/03/2024, 9:37:42 AM ERROR [ExceptionsHandler]
Property "slugWithUuid" was not found in "Course". Make sure your
query is correct. EntityPropertyNotFoundError: Property "slugWithUuid"
was not found in "Course". Make sure your query is correct.

请问是否有其他声明虚拟列的方式，使其能够支持查询过滤？

方法1：数据库原生虚拟列（推荐） #

直接在数据库层面定义虚拟列，让ORM能识别并支持查询。以MySQL为例，修改实体字段的@Column配置：

@Column({ 
  type: 'varchar', 
  length: 100,
  // MySQL语法：拼接slug与JSON中的uuid
  asExpression: "CONCAT(slug, '-', JSON_UNQUOTE(JSON_EXTRACT(additionalInfoJSON, '$.uuid')))",
  generated: 'virtual' // 可选'persisted'将值持久化到数据库，提升查询性能但占用存储空间
})
slugWithUuid: string;

注意：不同数据库的JSON处理语法不同，比如PostgreSQL用json_extract_path_text，SQL Server用JSON_VALUE，需对应调整。

方法2：查询时手动拼接匹配条件 #

不依赖虚拟列，直接在查询条件中拆分并匹配原字段：

async findBySlug(slug: string) {
  // 拆分传入的slug，分离原slug和uuid部分
  const [originalSlug, uuid] = slug.split('-');
  
  const entity = await this.repos.findOne({
    where: {
      slug: originalSlug,
      additionalInfoJSON: Raw(alias => 
        `${alias}->>'$.uuid' = :uuid`, 
        { uuid }
      )
    }
  });

  if (!entity) {
    throw new NotFoundException('Entity not found with this slug');
  }
  return entity;
}

这里用Raw函数生成原生SQL条件，匹配JSON字段中的uuid值。

方法3：使用数据库视图 #

创建包含slugWithUuid的视图，再定义对应的视图实体：

1. 创建视图SQL（MySQL示例）：

CREATE VIEW course_view AS
SELECT 
  *,
  CONCAT(slug, '-', JSON_UNQUOTE(JSON_EXTRACT(additionalInfoJSON, '$.uuid'))) AS slugWithUuid
FROM course;

2. 定义视图实体：

@Entity('course_view')
export class CourseView extends DefaultCredential {
  @Column({ type: 'varchar', length: 75 })
  title: string;

  @Column({ type: 'varchar', length: 50 })
  slug: string;

  @Column({ type: 'json', nullable: true })
  additionalInfoJSON: any;

  @Column({ type: 'varchar', length: 100 })
  slugWithUuid: string;
}

之后可通过CourseView实体直接查询slugWithUuid字段。

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