typeorm 多表关联查询

多表关联查询

定义实体类

User 实体类

// 文件目录: src/entities/user.entity.ts
import { Column, Entity, PrimaryColumn } from "typeorm";

@Entity('user')
export class User {
  @PrimaryColumn()
  id: string;

  @Column()
  nickname: string;

  @Column()
  username: string;

  @Column()
  password: string;

  @Column()
  avator: string;

  @Column()
  email: string;
}

Article 实体类

// 文件目录: src/entities/article.entity.ts
import { Column, Entity, PrimaryColumn } from "typeorm";

@Entity('article')
export class Article {
  @PrimaryColumn()
  id: string;

  @Column()
  title: string;

  @Column()
  link: string;

  @Column()
  fileId: string;

  @Column('text')
  content: string;

  @Column()
  categoryId: string;

  @Column()
  formatId: number;

  @Column()
  originId: number;

  @Column()
  createBy: string;
}

Attachment 实体类

// 文件目录: src/entities/attachment.entity.ts
import { Column, Entity, PrimaryColumn } from "typeorm";

@Entity('attachment')
export class Attachment {
  @PrimaryColumn()
  id: string;

  @Column()
  originName: string;

  @Column()
  size: number;

  @Column()
  filePath: string;

  @Column()
  createBy: string;
}

关联关系

user.id === article.createBy && user.id === attachment.createBy

UserModule 模块文件

import { Module } from "@nestjs/common";
import { TypeOrmModule } from "@nestjs/typeorm";
import { UserController } from "./user.controller";
import { User } from "../../entities/user.entity";
import { UserService } from "./user.service";
import { Article } from "src/entities/article.entity";
import { Attachment } from "src/entities/attachment.entity";

@Module({
  imports: [TypeOrmModule.forFeature([User, Article, Attachment])],
  controllers: [UserController],
  providers: [UserService]
})
export class UserModule {}

注意：

1. 这里一定要在 imports 中导入使用的实体类，否则 nestjs 框架无法通过 new 进行实例化相应类型的实例对象

   @Module({
     imports: [TypeOrmModule.forFeature([User, Article, Attachment])],
     ...
   })
   

UserService 文件

import { Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import { Article } from "../../entities/article.entity";
import { Attachment } from "../../entities/attachment.entity";
import { Repository } from "typeorm";
import { User } from "../../entities/user.entity";

@Injectable()
export class UserService {
  constructor(@InjectRepository(User) private readonly userRepository: Repository<User>){}
   
  // 三张表关联查询
  getAttachment(): Promise<any> {
    return this.userRepository.createQueryBuilder()
      .leftJoinAndSelect(Article, 'article', 'user.id = article.createBy')
      .leftJoinAndSelect(Attachment, 'attachment', 'user.id = attachment.createBy')
      .select(`
        article.id as id,
        article.title as title,
        article.content as content,
        user.id as userId,
        user.nickname as nickname,
        user.username as usernmae,
        user.avator as avator,
        attachment.id as attachmentId,
        attachment.originName as fileName,
        attachment.size as fileSize,
        attachment.filePath as filePath
      `)
      .getRawMany();
  }
}

测试返回的数据如下

[
    {
        "id": "0fcb8310-9c4a-11ea-9427-017d0539b705",
        "title": "fe'f",
        "content": "<p>微任务</p>",
        "userId": "16ffe4f0-98d0-11ea-adcb-cd4aa44d4464",
        "nickname": "李云龙",
        "usernmae": "wanghailong",
        "avator": "http://192.168.1.101:8765/avator/f360e610-9d80-11ea-9008-019523360f5b.jpg",
        "attachmentId": "03eba231-9bed-11ea-8495-bd633b2536d8",
        "fileName": "附件四 健康承诺书.jpg",
        "fileSize": 139981,
        "filePath": "E:\\Practices\\workspace-koa2\\whl-blog\\admin\\attachment\\article\\03eba230-9bed-11ea-8495-bd633b2536d8.jpg"
    },
    ...
]