??TypeORM 官方給的文檔中姜胖，多表查詢時(shí)使用的是通過實(shí)體關(guān)系進(jìn)行l(wèi)eftjoin查詢誉帅，我們知道TypeORM實(shí)體關(guān)系實(shí)際上是通過mysql的外鍵實(shí)現(xiàn)的。在實(shí)際開發(fā)中右莱，外鍵因?yàn)橛兄T多限制不被推薦使用蚜锨，大部分的都是無關(guān)系的表連接。經(jīng)過幾天的查找資料和摸索慢蜓，找到了兩種查詢的方法亚再，總結(jié)一下，如果大家有其他好的方法晨抡，歡迎留言討論氛悬。
定義實(shí)體類
User 實(shí)體
import { Entity, Column, PrimaryColumn } from 'typeorm';
@Entity()
export class User {
@PrimaryColumn()
id: string;
@Column()
nickname: string;
@Column()
gender: number;
@Column()
avatarUrl: string;
@Column()
language: string;
@Column()
country: string;
@Column()
province: string;
@Column()
city: string;
}
Role 實(shí)體類
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class Role {
@PrimaryGeneratedColumn()
id: number;
@Column()
roleId: number;
@Column()
userId: string;
}
Role_dic 實(shí)體類
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class Role_dic {
@PrimaryGeneratedColumn()
id: number;
@Column()
roleId: number;
@Column()
roleName: string;
}
表關(guān)系
user.id = role.userId role.roleId = role_dic.roleId
User service查詢的兩種方式
方法一
// 查詢語句
let queryBuilder = this.connection.createQueryBuilder(User, 'user');
const user = await queryBuilder
.leftJoinAndSelect(Role, 'role', 'role.userId = user.id')
.leftJoinAndSelect(Role_dic, 'role_dic', 'role_dic.roleId = role.roleId')
.where("user.id = :id", { id: userId })
.select(`
user.id as id,
user.nickname as nickname,
user.gender as gender,
user.avatarUrl as avatarUrl,
user.language as language,
user.country as country,
user.province as province,
user.city as city,
role.roleId as roleId,
role_dic.roleName as roleName
`)
.getRawOne();
// 返回結(jié)果
{
"id": "1630404672291",
"nickname": "xiaoditian",
"gender": 1,
"avatarUrl": "mytouxiang",
"language": "cn",
"country": "china",
"province": "henan",
"city": "zhengzhou",
"roleId": 1,
"roleName": "管理員"
}
??這種方法使用getRawOne獲取到sql查詢后的原始數(shù)據(jù)则剃，因?yàn)門ypeORM會(huì)用別名，所以這里用select對(duì)字段進(jìn)行了重命名如捅，這個(gè)寫法需要對(duì)每個(gè)需要的字段名進(jìn)行重命名棍现，否則返回的字段名稱會(huì)帶上表名。
方法二
//查詢語句
let queryBuilder = this.connection.createQueryBuilder(User, 'user');
const user = await queryBuilder
.leftJoinAndMapOne('user.role', Role, 'role', 'role.userId = user.id')
.leftJoinAndMapOne('role.roleDic', Role_dic, 'role_dic', 'role_dic.roleId = role.roleId')
.where("user.id = :id", { id: userId })
.getOne();
//返回結(jié)果
{
"id": "1630404672291",
"nickname": "xiaoditian",
"gender": 1,
"avatarUrl": "mytouxiang",
"language": "cn",
"country": "china",
"province": "henan",
"city": "zhengzhou",
"role": {
"id": 1,
"roleId": 1,
"userId": "1630404672291",
"roleDic": {
"id": 1,
"roleId": 1,
"roleName": "管理員"
}
}
}
??第二種方法使用leftJoinAndMapOne作字段映射镜遣，如果一對(duì)多可以使用leftJoinAndMapMany己肮。這樣就不用挨個(gè)對(duì)字段重命名，但是可能存在嵌套較深的問題悲关。
總結(jié)
??以上是常用的兩種表連接的方法谎僻，每個(gè)都有其優(yōu)缺點(diǎn)，可以根據(jù)情況選擇使用寓辱。兩外附上這兩種方法的出處：
1艘绍、https://juejin.cn/post/6916483483095449608
2、https://www.cnblogs.com/zzk96/p/11397223.html
