NestJS如何将数组列返回到数据库中的视图

Question

我想在数据库中检索每个平台的平台列表，检索这个平台的项目。

见图片

在这里输入图像描述

我使用View实体来计算每个平台的进度。我的问题是，我们有它留给对象的每个平台项目。

在这里输入图像描述

如何从包含所有项目的平台返回对象？

Answer 1

我是NestJ的新手，我遇到了从后端返回响应实体的问题。我使用视图实体，因为我在复杂的SQL计算中作为一个平台高级计算。

当使用视图时，当包含多个项目和成员时，它会返回对象的重复。

​

​

如何返回包含项目和成员对象数组的平台对象？

我想要这样的：

{
  "data": {
    "id": 1,
    "name": "Dev factory",
    "projects": [
      {
         "createdAt": "2022-01-11T10:29:42.000Z",
         "updatedAt": "2022-01-11T10:29:42.000Z",
         "deletedAt": null,
         "id": 2,
         "name": "API",
         "description": "efqzf",
        },{
         "createdAt": "2022-01-11T10:29:42.000Z",
         "updatedAt": "2022-01-11T10:29:42.000Z",
         "deletedAt": null,
         "id": 1,
         "name": "Web",
         "description": "efqsszf",
         }],
   "advancement": 50,
   "dueDate": null,
   "status": "In prograss"
}

下面的代码。

platform.entity.ts

import { Project } from '@routes/projects/entities/project.entity';
import { User } from '@routes/users/entities/user.entity';
import { ViewColumn, ViewEntity } from 'typeorm';

@ViewEntity({
  expression: `
SELECT 
      "platforms"."id" AS "id", 
      "platforms"."name" AS "name",
      "platforms"."releaseDate" AS "dueDate",
      "users"."id" AS "memberId",
      "users"."firstName" AS "memberFirstName",
      "users"."lastName" AS "memberLastName",
      ((cast(("done_tickets"."tickets_statusId") as float) / count ("tickets"."id")) * 100 ) AS "advancement" , 
      ("done_tickets"."tickets_statusId") AS "tickets_done", 
      count ("tickets"."id") AS  "tickets_count",
      CASE
        WHEN ((cast(("done_tickets"."tickets_statusId") as float) / count ("tickets"."id")) * 100 )>0 
            AND ((cast(("done_tickets"."tickets_statusId") as float) / count ("tickets"."id")) * 100 )<100 THEN 'In Progress'
        WHEN ((cast(("done_tickets"."tickets_statusId") as float) / count ("tickets"."id")) * 100 ) = 100 THEN  'Done'
        ELSE 'To Do'
      END AS status
FROM 
      "platforms" "platforms" 
LEFT JOIN 
        "users" AS "users" ON "platforms"."adminUserId" = "users"."id"
LEFT JOIN 
      "projects" "projects" ON "projects"."platformId"="platforms"."id" 
LEFT JOIN 
      "tickets" "tickets" ON "tickets"."projectId"="projects"."id" 
LEFT JOIN ( 
      SELECT "platforms"."id" AS "platformId" , 
          count ("tickets"."statusId") as "tickets_statusId" 
      FROM "platforms" "platforms" 
      LEFT JOIN "projects" "projects" ON "projects"."platformId"="platforms"."id" 
      LEFT JOIN "tickets" "tickets" ON "tickets"."projectId"="projects"."id" 
      LEFT JOIN "ticket_statuses" "status" ON  "status"."id"="tickets"."statusId" 
      WHERE "tickets"."statusId" = 5 
      GROUP BY "platforms"."id" ) "done_tickets" ON  "platforms"."id"="done_tickets"."platformId" 
LEFT JOIN "ticket_statuses" "status" ON "status"."id"="tickets"."statusId"  
GROUP BY "platforms"."id" , "done_tickets"."tickets_statusId" , "users"."id"
`,
})
export class ProgressPlatform {
  @ViewColumn()
  id!: number;

  @ViewColumn()
  name!: string;

  @ViewColumn()
  advancement!: Float64Array;

  @ViewColumn()
  dueDate!: Date;

  @ViewColumn()
  projects!: Project[]; <-- here is the problem

  @ViewColumn()
  members!: User[]; <-- here is the problem

  @ViewColumn()
  status!: string;
}

platform.service.ts

async findAll(
    options: PaginationParamsInterface,
  ): Promise<PaginatedInterface<ProgressPlatform>> {
    const [platforms, totalCount] = await Promise.all([
      this.platformsProgressRepository.find({
        skip: paginationUtils.getSkipCount(options.page, options.limit),
        take: paginationUtils.getLimitCount(options.limit),
      }),
      this.platformsProgressRepository.count(),
    ]);

    return { paginatedResult: platforms, totalCount };
  }

platform.controller.ts

  @ApiUnauthorizedResponse({ type: () => MessageResponseDto })
  @Version('1')
  @Get('all-platforms')
  @Serialize(AllPlatformsResponseDto)
  @ApiExtraModels(AllPlatformsResponseDto)
  @ApiQuery({ name: 'page', required: false })
  @ApiQuery({ name: 'limit', required: false })
  async findAll(@Query('page') page?: number, @Query('limit') limit?: number) {
    const paginatedTickets: PaginatedInterface<ProgressPlatform> =
      await this.platformsService.findAll({ page, limit });
    return responseUtils.success(
      'platforms',
      paginatedTickets.paginatedResult,
      {
        totalCount: paginatedTickets.totalCount,
      },
    );
  }