跳转到主要内容

查看实体

什么是视图实体？​

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

@ViewEntity()接受以下选项：

  • name - 视图名称。如果未指定，则视图名称将根据实体类名生成。
  • database - 选定的数据库服务器中的数据库名称。
  • 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" })  // <-- 这是错误的
        .where("category.name = 'Cars'")                   // <-- 这是正确的
})

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

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

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

视图实体列​

为了将视图数据映射到正确的实体列中，您必须使用@ViewColumn()装饰器标记实体列，并将这些列指定为选择语句的别名。

使用字符串表达式定义的示例：

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
}

视图列选项​

视图列选项定义视图实体列的附加选项，类似于常规实体的column options。

您可以在@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 "./entity/Category"
import { Post } from "./entity/Post"
import { PostCategory } from "./entity/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' }