视图实体

什么是视图实体?

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

@ViewEntity() 接收以下参数：

  • name - 视图名称。 如果未指定，则从实体类名生成视图名称。
  • database - 所选DB服务器中的数据库名称。
  • schema - 架构名称。
  • expression - 视图定义。 必需参数。

expression可以是带有正确转义的列和表的字符串，取决于所使用的数据库（示例中为postgres）：

  1. @ViewEntity({ 
  2.     expression: `
  3.         SELECT "post"."id" "id", "post"."name" AS "name", "category"."name" AS "categoryName"
  4.         FROM "post" "post"
  5.         LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
  6.     `
  7. })

或者是QueryBuilder的一个实例

  1. @ViewEntity({ 
  2.     expression: (connection: Connection) => connection.createQueryBuilder()
  3.         .select("post.id", "id")
  4.         .addSelect("post.name", "name")
  5.         .addSelect("category.name", "categoryName")
  6.         .from(Post, "post")
  7.         .leftJoin(Category, "category", "category.id = post.categoryId")
  8. })

注意: 由于驱动程序的限制，不支持参数绑定。请改用文字参数。

  1. @ViewEntity({ 
  2.     expression: (connection: Connection) => connection.createQueryBuilder()
  3.         .select("post.id", "id")
  4.         .addSelect("post.name", "name")
  5.         .addSelect("category.name", "categoryName")
  6.         .from(Post, "post")
  7.         .leftJoin(Category, "category", "category.id = post.categoryId")
  8.         .where("category.name = :name", { name: "Cars" })  // <-- 这是错的
  9.         .where("category.name = 'Cars'")                   // <-- 这是对的
  10. })

每个视图实体都必须在连接选项中注册：

  1. import {createConnection, Connection} from "typeorm";
  2. import {UserView} from "./entity/UserView";
  4. const connection: Connection = await createConnection({
  5.     type: "mysql",
  6.     host: "localhost",
  7.     port: 3306,
  8.     username: "test",
  9.     password: "test",
  10.     database: "test",
  11.     entities: [UserView]
  12. });

或者你可以指定包含所有实体的整个目录 - 所有实体都将被加载：

  1. import {createConnection, Connection} from "typeorm";
  3. const connection: Connection = await createConnection({
  4.     type: "mysql",
  5.     host: "localhost",
  6.     port: 3306,
  7.     username: "test",
  8.     password: "test",
  9.     database: "test",
  10.     entities: ["entity/*.js"]
  11. });

视图实体列

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

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

  1. import {ViewEntity, ViewColumn} from "typeorm";
  3. @ViewEntity({ 
  4.     expression: `
  5.         SELECT "post"."id" AS "id", "post"."name" AS "name", "category"."name" AS "categoryName"
  6.         FROM "post" "post"
  7.         LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
  8.     `
  9. })
  10. export class PostCategory {
  12.     @ViewColumn()
  13.     id: number;
  15.     @ViewColumn()
  16.     name: string;
  18.     @ViewColumn()
  19.     categoryName: string;
  21. }

使用QueryBuilder的示例：

  1. import {ViewEntity, ViewColumn} from "typeorm";
  3. @ViewEntity({ 
  4.     expression: (connection: Connection) => connection.createQueryBuilder()
  5.         .select("post.id", "id")
  6.         .addSelect("post.name", "name")
  7.         .addSelect("category.name", "categoryName")
  8.         .from(Post, "post")
  9.         .leftJoin(Category, "category", "category.id = post.categoryId")
  10. })
  11. export class PostCategory {
  13.     @ViewColumn()
  14.     id: number;
  16.     @ViewColumn()
  17.     name: string;
  19.     @ViewColumn()
  20.     categoryName: string;
  22. }

完整示例

让我们创建两个实体和一个包含来自这些实体的聚合数据的视图：

  1. import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";
  3. @Entity()
  4. export class Category {
  6.     @PrimaryGeneratedColumn()
  7.     id: number;
  9.     @Column()
  10.     name: string;
  12. }
  1. import {Entity, PrimaryGeneratedColumn, Column, ManyToOne, JoinColumn} from "typeorm";
  2. import {Category} from "./Category";
  4. @Entity()
  5. export class Post {
  7.     @PrimaryGeneratedColumn()
  8.     id: number;
  10.     @Column()
  11.     name: string;
  13.     @Column()
  14.     categoryId: number;
  16.     @ManyToOne(() => Category)
  17.     @JoinColumn({ name: "categoryId" })
  18.     category: Category;
  20. }
  1. import {ViewEntity, ViewColumn} from "typeorm";
  3. @ViewEntity({ 
  4.     expression: (connection: Connection) => connection.createQueryBuilder()
  5.         .select("post.id", "id")
  6.         .addSelect("post.name", "name")
  7.         .addSelect("category.name", "categoryName")
  8.         .from(Post, "post")
  9.         .leftJoin(Category, "category", "category.id = post.categoryId")
  10. })
  11. export class PostCategory {
  13.     @ViewColumn()
  14.     id: number;
  16.     @ViewColumn()
  17.     name: string;
  19.     @ViewColumn()
  20.     categoryName: string;
  22. }

然后用数据填充这些表并从PostCategory视图请求所有数据：

  1. import {getManager} from "typeorm";
  2. import {Category} from "./entity/Category";
  3. import {Post} from "./entity/Post";
  4. import {PostCategory} from "./entity/PostCategory";
  6. const entityManager = getManager();
  8. const category1 = new Category();
  9. category1.name = "Cars";
  10. await entityManager.save(category1);
  12. const category2 = new Category();
  13. category2.name = "Airplanes";
  14. await entityManager.save(category2);
  16. const post1 = new Post();
  17. post1.name = "About BMW";
  18. post1.categoryId = category1.id;
  19. await entityManager.save(post1);
  21. const post2 = new Post();
  22. post2.name = "About Boeing";
  23. post2.categoryId = category2.id;
  24. await entityManager.save(post2);
  26. const postCategories = await entityManager.find(PostCategory);
  27. const postCategory = await entityManager.findOne(PostCategory, { id: 1 });

postCategories的结果将是：

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

在postCategory中：

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