跳至主内容区

视图实体

非官方测试版翻译

本页面由 PageTurner AI 翻译（测试版）。未经项目官方认可。 发现错误？ 报告问题 →

什么是视图实体？​

视图实体是映射到数据库视图的类。 可以通过定义新类并用 @ViewEntity() 装饰它来创建视图实体：

@ViewEntity() 接受以下选项：

  • name - 视图名称。如果未指定，则根据实体类名生成。

  • database - 所选 DB 服务器中的数据库名称。

  • schema - 模式名称。

  • expression - 视图定义。必填参数。

  • dependsOn - 当前视图所依赖的其他视图列表。如果视图定义中使用了其他视图，可在此添加，以确保迁移按正确顺序生成。

expression 可以是正确转义列名和表名的字符串（以 postgres 为例）：

@ViewEntity({
    expression: `
        SELECT "post"."id" AS "id", "post"."name" AS "name", "category"."name" AS "categoryName"
        FROM "post" "post"
        LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
    `
})

或是 QueryBuilder 的实例

@ViewEntity({
    expression: (dataSource: DataSource) => dataSource
        .createQueryBuilder()
        .select("post.id", "id")
        .addSelect("post.name", "name")
        .addSelect("category.name", "categoryName")
        .from(Post, "post")
        .leftJoin(Category, "category", "category.id = post.categoryId")
})

注意： 由于驱动程序限制，不支持参数绑定。请改用字面量参数。

@ViewEntity({
    expression: (dataSource: DataSource) => dataSource
        .createQueryBuilder()
        .select("post.id", "id")
        .addSelect("post.name", "name")
        .addSelect("category.name", "categoryName")
        .from(Post, "post")
        .leftJoin(Category, "category", "category.id = post.categoryId")
        .where("category.name = :name", { name: "Cars" })  // <-- this is wrong
        .where("category.name = 'Cars'")                   // <-- and this is right
})

每个视图实体都必须在数据源选项中注册：

import { DataSource } from "typeorm"
import { UserView } from "./entities/UserView"

const dataSource = new DataSource({
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "test",
    password: "test",
    database: "test",
    entities: [UserView],
})

视图实体列​

要将视图数据正确映射到实体列，需用 @ViewColumn() 装饰器标记实体列，并将这些列指定为 SELECT 语句别名。

字符串表达式定义示例：

import { ViewEntity, ViewColumn } from "typeorm"

@ViewEntity({
    expression: `
        SELECT "post"."id" AS "id", "post"."name" AS "name", "category"."name" AS "categoryName"
        FROM "post" "post"
        LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
    `,
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

使用 QueryBuilder 的示例：

import { ViewEntity, ViewColumn } from "typeorm"

@ViewEntity({
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

视图列选项​

视图列选项为视图实体列提供额外配置，类似于常规实体的列选项。

可在 @ViewColumn 中指定视图列选项：

@ViewColumn({
    name: "postName",
    // ...
})
name: string;

ViewColumnOptions 可用选项列表：

  • name: string - 数据库视图中的列名。

  • transformer: { from(value: DatabaseType): EntityType, to(value: EntityType): DatabaseType } - 用于将数据库支持的任意类型 DatabaseType 解组为 EntityType 类型。支持转换器数组，读取时按反向顺序应用。注意：由于数据库视图是只读的，transformer.to(value) 永远不会被使用。

物化视图索引​

使用 PostgreSQL 时支持为物化视图创建索引。

@ViewEntity({
    materialized: true,
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @Index()
    @ViewColumn()
    name: string

    @Index("catname-idx")
    @ViewColumn()
    categoryName: string
}

但当前物化视图索引仅支持 unique 选项，其他索引选项将被忽略。

@Index("name-idx", { unique: true })
@ViewColumn()
name: string

完整示例​

创建两个实体及包含其聚合数据的视图：

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"

@Entity()
export class Category {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string
}
import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToOne,
    JoinColumn,
} from "typeorm"
import { Category } from "./Category"

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @Column()
    categoryId: number

    @ManyToOne(() => Category)
    @JoinColumn({ name: "categoryId" })
    category: Category
}
import { ViewEntity, ViewColumn, DataSource } from "typeorm"

@ViewEntity({
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

填充数据表后查询 PostCategory 视图所有数据：

import { Category } from "./entities/Category"
import { Post } from "./entities/Post"
import { PostCategory } from "./entities/PostCategory"

const category1 = new Category()
category1.name = "Cars"
await dataSource.manager.save(category1)

const category2 = new Category()
category2.name = "Airplanes"
await dataSource.manager.save(category2)

const post1 = new Post()
post1.name = "About BMW"
post1.categoryId = category1.id
await dataSource.manager.save(post1)

const post2 = new Post()
post2.name = "About Boeing"
post2.categoryId = category2.id
await dataSource.manager.save(post2)

const postCategories = await dataSource.manager.find(PostCategory)
const postCategory = await dataSource.manager.findOneBy(PostCategory, { id: 1 })

postCategories 中的结果将是：

[ PostCategory { id: 1, name: 'About BMW', categoryName: 'Cars' },
  PostCategory { id: 2, name: 'About Boeing', categoryName: 'Airplanes' } ]

而 postCategory 中的结果是：

PostCategory { id: 1, name: 'About BMW', categoryName: 'Cars' }