视图实体
编辑教程视图实体
什么是视图实体?
视图实体是一个映射到数据库视图的类。
你可以通过定义一个新类来创建一个视图实体,并用@ViewEntity()
来标记:
@ViewEntity()
接收以下参数:
name
- 视图名称。 如果未指定,则从实体类名生成视图名称。database
- 所选DB服务器中的数据库名称。schema
- 架构名称。expression
- 视图定义。 必需参数。
expression
可以是带有正确转义的列和表的字符串,取决于所使用的数据库(示例中为postgres):
@ViewEntity({
expression: `
SELECT "post"."id" "id", "post"."name" AS "name", "category"."name" AS "categoryName"
FROM "post" "post"
LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
`
})
或者是QueryBuilder的一个实例
@ViewEntity({
expression: (connection: Connection) => connection.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: (connection: Connection) => connection.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 {createConnection, Connection} from "typeorm";
import {UserView} from "./entity/UserView";
const connection: Connection = await createConnection({
type: "mysql",
host: "localhost",
port: 3306,
username: "test",
password: "test",
database: "test",
entities: [UserView]
});
或者你可以指定包含所有实体的整个目录 - 所有实体都将被加载:
import {createConnection, Connection} from "typeorm";
const connection: Connection = await createConnection({
type: "mysql",
host: "localhost",
port: 3306,
username: "test",
password: "test",
database: "test",
entities: ["entity/*.js"]
});
视图实体列
要将视图中的数据映射到正确的实体列,必须使用@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: (connection: Connection) => connection.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;
}
完整示例
让我们创建两个实体和一个包含来自这些实体的聚合数据的视图:
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} from "typeorm";
@ViewEntity({
expression: (connection: Connection) => connection.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 {getManager} from "typeorm";
import {Category} from "./entity/Category";
import {Post} from "./entity/Post";
import {PostCategory} from "./entity/PostCategory";
const entityManager = getManager();
const category1 = new Category();
category1.name = "Cars";
await entityManager.save(category1);
const category2 = new Category();
category2.name = "Airplanes";
await entityManager.save(category2);
const post1 = new Post();
post1.name = "About BMW";
post1.categoryId = category1.id;
await entityManager.save(post1);
const post2 = new Post();
post2.name = "About Boeing";
post2.categoryId = category2.id;
await entityManager.save(post2);
const postCategories = await entityManager.find(PostCategory);
const postCategory = await entityManager.findOne(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' }
Mos固件,小电视必刷固件
ES6 教程
Vue.js 教程
JSON 教程
jQuery 教程
HTML 教程
HTML 5 教程
CSS 教程
CSS3 教程
JavaScript 教程
DHTML 教程
JSON在线格式化工具
JS在线运行
JSON解析格式化
jsfiddle中国国内版本
JS代码在线运行
PHP代码在线运行
Java代码在线运行
C语言代码在线运行
C++代码在线运行
Python代码在线运行
Go语言代码在线运行
C#代码在线运行
JSRUN闪电教程系统是国内最先开创的教程维护系统, 所有工程师都可以参与共同维护的闪电教程,让知识的积累变得统一完整、自成体系。
大家可以一起参与进共编,让零散的知识点帮助更多的人。
X
选择支付方式:
立即支付
¥
9.99
无法付款,请点击这里
金额: 0 元
备注:
转账时请填写正确的金额和备注信息,到账由人工处理,可能需要较长时间
备注:
转账时请填写正确的金额和备注信息,到账由人工处理,可能需要较长时间
如有疑问请联系QQ:565830900
正在生成二维码, 此过程可能需要15秒钟