多表關聯(lián)查詢

定義實體類

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;
}

關聯(lián)關系

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>){}
   
  // 三張表關聯(lián)查詢
  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();
  }
}

測試返回的數(shù)據(jù)如下

[
    {
        "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"
    },
    ...
]