{T}

NestJS多表联合查询与数据排序实战

NestJS多表联合查询与数据排序实战

学习目标：掌握 Prisma 多表联合查询、嵌套 include 使用、查询结果排序、数据结构化处理。

一、多表联合查询场景

1.1 业务场景描述

code
课程推荐系统查询场景：
│
├── 页面展示需求
│   ├── 推荐内容（分类）
│   ├── 每日一课（分类）
│   ├── 精品微课（分类）
│   └── 学习计划（分类）
│
├── 每个分类包含
│   ├── 分类名称
│   ├── 课程列表
│   │   ├── 课程图片
│   │   ├── 课程标题
│   │   ├── 作者信息
│   │   ├── 价格
│   │   └── 学习人数
│   └── 标签列表
│
└── 查询需求
    ├── 查询所有分类
    ├── 每个分类包含的标签
    ├── 每个标签关联的课程
    └── 按分类顺序排列

1.2 数据表关系

code
数据表关系链路：
│
├── CourseTypes（课程分类）
│   ├── id: 分类 ID
│   ├── name: 分类名称
│   ├── order: 排序字段
│   └── 关联：tags[]
│
├── Tags（标签）
│   ├── id: 标签 ID
│   ├── name: 标签名称
│   ├── typeId: 分类 ID
│   └── 关联：courses[]
│
├── CourseTags（课程标签关联表）
│   ├── id: 关联 ID
│   ├── courseId: 课程 ID
│   ├── tagId: 标签 ID
│   └── 关联：course, tag
│
└── Courses（课程）
    ├── id: 课程 ID
    ├── title: 课程标题
    ├── authorId: 作者 ID
    └── 关联：tags[], author

1.3 查询链路分析

code
完整查询链路：
│
├── 第一步：查询分类表
│   └── SELECT * FROM course_types
│
├── 第二步：查询关联的标签
│   └── SELECT * FROM tags WHERE typeId IN (分类 IDs)
│
├── 第三步：查询标签关联的课程
│   └── SELECT * FROM course_tags WHERE tagId IN (标签 IDs)
│
├── 第四步：查询课程详情
│   └── SELECT * FROM courses WHERE id IN (课程 IDs)
│
└── 第五步：查询作者信息
    └── SELECT * FROM users WHERE id IN (作者 IDs)

二、Prisma 嵌套查询实现

2.1 第一层：查询分类和标签

typescript
// src/modules/course/course.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '@/prisma/prisma.service';

@Injectable()
export class CourseService {
  constructor(private prisma: PrismaService) {}

  // 查询课程分类（包含标签）
  async getCoursesByType() {
    return this.prisma.courseTypes.findMany({
      include: {
        tags: true,  // 包含关联的标签
      },
    });
  }
}

查询结果示例：

json
[
  {
    "id": 9,
    "name": "推荐内容",
    "order": 100,
    "tags": [
      { "id": 1, "name": "Vue3 项目实战", "typeId": 9 },
      { "id": 2, "name": "React18 新特性", "typeId": 9 }
    ]
  },
  {
    "id": 10,
    "name": "每日一课",
    "order": 200,
    "tags": [
      { "id": 3, "name": "TypeScript 进阶", "typeId": 10 }
    ]
  }
]

2.2 第二层：查询标签关联的课程

typescript
// src/modules/course/course.service.ts
async getCoursesByType() {
  return this.prisma.courseTypes.findMany({
    include: {
      tags: {
        include: {
          courses: true,  // 包含标签关联的课程
        },
      },
    },
  });
}

查询结果示例：

json
[
  {
    "id": 9,
    "name": "推荐内容",
    "tags": [
      {
        "id": 1,
        "name": "Vue3 项目实战",
        "courses": [
          { "id": 1, "courseId": 1, "tagId": 1 },
          { "id": 2, "courseId": 2, "tagId": 1 }
        ]
      }
    ]
  }
]

2.3 第三层：查询课程详情

typescript
// src/modules/course/course.service.ts
async getCoursesByType() {
  return this.prisma.courseTypes.findMany({
    include: {
      tags: {
        include: {
          courses: {
            include: {
              course: true,  // 包含课程详情
              tag: true,     // 包含标签详情（可选）
            },
          },
        },
      },
    },
  });
}

查询结果示例：

json
[
  {
    "id": 9,
    "name": "推荐内容",
    "order": 100,
    "tags": [
      {
        "id": 1,
        "name": "Vue3 项目实战",
        "typeId": 9,
        "courses": [
          {
            "id": 1,
            "courseId": 1,
            "tagId": 1,
            "course": {
              "id": 1,
              "title": "Vue3 完全指南",
              "authorId": 1,
              "price": 99,
              "students": 1000
            },
            "tag": {
              "id": 1,
              "name": "Vue3 项目实战"
            }
          }
        ]
      }
    ]
  }
]

2.4 优化后的查询（移除重复字段）

typescript
// src/modules/course/course.service.ts
async getCoursesByType() {
  return this.prisma.courseTypes.findMany({
    include: {
      tags: {
        include: {
          courses: {
            include: {
              course: true,  // 包含课程详情
              // tag: true,  //  移除，因为已经从 tag 关联过来
            },
          },
        },
      },
    },
  });
}

三、Controller 层实现

3.1 创建查询接口

typescript
// src/modules/course/course.controller.ts
import { Controller, Get } from '@nestjs/common';
import { CourseService } from './course.service';

@Controller('courses')
export class CourseController {
  constructor(private readonly courseService: CourseService) {}

  // 查询课程分类及关联的课程
  @Get()
  async getCoursesByType() {
    return this.courseService.getCoursesByType();
  }
}

3.2 完整 Controller 实现

typescript
// src/modules/course/course.controller.ts
import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { CourseService } from './course.service';
import { CreateCourseWithTagsDto } from './dto/create-course-with-tags.dto';

@Controller('courses')
export class CourseController {
  constructor(private readonly courseService: CourseService) {}

  // 查询课程分类及关联的课程
  @Get()
  async getCoursesByType() {
    return this.courseService.getCoursesByType();
  }

  // 创建课程
  @Post()
  async create(@Body() dto: CreateCourseWithTagsDto) {
    if (dto.tags && dto.tags.length > 0) {
      const tags = {
        create: dto.tags.map(tagId => ({ tagId })),
      };
      return this.courseService.createCourseWithTags({
        ...dto,
        tags,
      });
    } else {
      return this.courseService.createCourse(dto);
    }
  }

  // 查询课程详情
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return this.courseService.getCourseById(id);
  }
}

四、查询结果排序

4.1 基础排序语法

typescript
// 按 order 字段升序排列
await prisma.courseTypes.findMany({
  orderBy: {
    order: 'asc',  // 升序
  },
});

// 按 order 字段降序排列
await prisma.courseTypes.findMany({
  orderBy: {
    order: 'desc',  // 降序
  },
});

4.2 嵌套查询中的排序

typescript
// src/modules/course/course.service.ts
async getCoursesByType() {
  return this.prisma.courseTypes.findMany({
    orderBy: {
      order: 'asc',  // 按分类顺序排列
    },
    include: {
      tags: {
        include: {
          courses: {
            include: {
              course: true,
            },
          },
        },
      },
    },
  });
}

4.3 多字段排序

typescript
// 先按 order 排序，再按 name 排序
await prisma.courseTypes.findMany({
  orderBy: [
    { order: 'asc' },
    { name: 'asc' },
  ],
});

// 嵌套字段排序
await prisma.courseTypes.findMany({
  orderBy: {
    order: 'asc',
  },
  include: {
    tags: {
      orderBy: {
        name: 'asc',  // 标签按名称排序
      },
      include: {
        courses: {
          include: {
            course: {
              orderBy: {
                createdAt: 'desc',  // 课程按创建时间倒序
              },
            },
          },
        },
      },
    },
  },
});

4.4 排序验证

code
排序验证流程：
│
├── 初始数据
│   ├── 推荐内容：order = 100
│   ├── 每日一课：order = 200
│   └── 精品微课：order = 300
│
├── 查询结果（升序）
│   ├── 推荐内容（order = 100）
│   ├── 每日一课（order = 200）
│   └── 精品微课（order = 300）
│
├── 修改数据
│   └── 每日一课：order = 900
│
└── 查询结果（升序）
    ├── 推荐内容（order = 100）
    ├── 精品微课（order = 300）
    └── 每日一课（order = 900）

五、Postman 测试示例

5.1 测试一：查询分类和标签

bash
# 请求
GET /courses

# 响应
[
  {
    "id": 9,
    "name": "推荐内容",
    "order": 100,
    "tags": [
      { "id": 1, "name": "Vue3 项目实战", "typeId": 9 },
      { "id": 2, "name": "React18 新特性", "typeId": 9 }
    ]
  },
  {
    "id": 10,
    "name": "每日一课",
    "order": 200,
    "tags": [
      { "id": 3, "name": "TypeScript 进阶", "typeId": 10 }
    ]
  }
]

5.2 测试二：查询标签关联的课程

bash
# 请求
GET /courses

# 响应
[
  {
    "id": 9,
    "name": "推荐内容",
    "tags": [
      {
        "id": 1,
        "name": "Vue3 项目实战",
        "courses": [
          {
            "id": 1,
            "courseId": 1,
            "tagId": 1,
            "course": {
              "id": 1,
              "title": "Vue3 完全指南",
              "authorId": 1,
              "price": 99,
              "students": 1000
            }
          }
        ]
      }
    ]
  }
]

5.3 测试三：验证排序

bash
# 初始查询
GET /courses
# 结果：推荐内容、每日一课、精品微课

# 修改数据库：每日一课 order = 900

# 再次查询
GET /courses
# 结果：推荐内容、精品微课、每日一课

六、数据结构化处理

6.1 前端所需数据结构

typescript
// 前端期望的数据结构
interface FormattedType {
  id: number;
  name: string;
  courses: {
    id: number;
    title: string;
    author: string;
    price: number;
    students: number;
    cover: string;
  }[];
}

// 示例
[
  {
    "id": 9,
    "name": "推荐内容",
    "courses": [
      {
        "id": 1,
        "title": "Vue3 完全指南",
        "author": "张三",
        "price": 99,
        "students": 1000,
        "cover": "https://example.com/cover.jpg"
      }
    ]
  }
]

6.2 Service 层数据结构化

typescript
// src/modules/course/course.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '@/prisma/prisma.service';

@Injectable()
export class CourseService {
  constructor(private prisma: PrismaService) {}

  // 查询并结构化数据
  async getFormattedCoursesByType() {
    const types = await this.prisma.courseTypes.findMany({
      orderBy: {
        order: 'asc',
      },
      include: {
        tags: {
          include: {
            courses: {
              include: {
                course: {
                  include: {
                    author: true,  // 包含作者信息
                  },
                },
              },
            },
          },
        },
      },
    });

    // 数据结构化处理
    return types.map(type => ({
      id: type.id,
      name: type.name,
      order: type.order,
      courses: this.extractCourses(type.tags),
    }));
  }

  // 提取课程列表（去重）
  private extractCourses(tags: any[]) {
    const courseMap = new Map();
    
    tags.forEach(tag => {
      tag.courses.forEach(ct => {
        if (!courseMap.has(ct.course.id)) {
          courseMap.set(ct.course.id, {
            id: ct.course.id,
            title: ct.course.title,
            author: ct.course.author?.name || '未知作者',
            price: ct.course.price,
            students: ct.course.students,
            cover: ct.course.cover,
          });
        }
      });
    });

    return Array.from(courseMap.values());
  }
}

6.3 Controller 层调用

typescript
// src/modules/course/course.controller.ts
@Controller('courses')
export class CourseController {
  constructor(private readonly courseService: CourseService) {}

  // 查询结构化的课程数据
  @Get('formatted')
  async getFormattedCourses() {
    return this.courseService.getFormattedCoursesByType();
  }

  // 查询原始数据
  @Get()
  async getCoursesByType() {
    return this.courseService.getCoursesByType();
  }
}

七、完整实战示例

7.1 项目结构

code
src/modules/course/
├── course.module.ts
├── course.service.ts
├── course.controller.ts
└── dto/
    ├── create-course.dto.ts
    ├── create-course-with-tags.dto.ts
    └── formatted-course.dto.ts

7.2 完整 Service 实现

typescript
// src/modules/course/course.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '@/prisma/prisma.service';
import { CreateCourseDto } from './dto/create-course.dto';
import { CreateCourseWithTagsInterface } from './dto/create-course-with-tags.dto';

@Injectable()
export class CourseService {
  constructor(private prisma: PrismaService) {}

  // 创建课程
  async createCourse(dto: CreateCourseDto) {
    return this.prisma.courses.create({
      data: dto,
    });
  }

  // 创建课程并关联标签
  async createCourseWithTags(dto: CreateCourseWithTagsInterface) {
    const { tags, ...courseData } = dto;
    
    return this.prisma.courses.create({
      data: {
        ...courseData,
        tags,
      },
      include: {
        tags: {
          include: {
            tag: true,
          },
        },
      },
    });
  }

  // 查询课程详情
  async getCourseById(id: number) {
    return this.prisma.courses.findUnique({
      where: { id },
      include: {
        author: true,
        tags: {
          include: {
            tag: true,
          },
        },
      },
    });
  }

  // 查询课程分类（原始数据）
  async getCoursesByType() {
    return this.prisma.courseTypes.findMany({
      orderBy: {
        order: 'asc',
      },
      include: {
        tags: {
          include: {
            courses: {
              include: {
                course: true,
              },
            },
          },
        },
      },
    });
  }

  // 查询课程分类（结构化数据）
  async getFormattedCoursesByType() {
    const types = await this.prisma.courseTypes.findMany({
      orderBy: {
        order: 'asc',
      },
      include: {
        tags: {
          include: {
            courses: {
              include: {
                course: {
                  include: {
                    author: true,
                  },
                },
              },
            },
          },
        },
      },
    });

    return types.map(type => ({
      id: type.id,
      name: type.name,
      order: type.order,
      courses: this.extractCourses(type.tags),
    }));
  }

  // 提取课程列表
  private extractCourses(tags: any[]) {
    const courseMap = new Map();
    
    tags.forEach(tag => {
      tag.courses.forEach(ct => {
        if (!courseMap.has(ct.course.id)) {
          courseMap.set(ct.course.id, {
            id: ct.course.id,
            title: ct.course.title,
            author: ct.course.author?.name || '未知作者',
            price: ct.course.price,
            students: ct.course.students,
            cover: ct.course.cover,
            createdAt: ct.course.createdAt,
          });
        }
      });
    });

    return Array.from(courseMap.values());
  }
}

7.3 完整 Controller 实现

typescript
// src/modules/course/course.controller.ts
import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { CourseService } from './course.service';
import { CreateCourseWithTagsDto } from './dto/create-course-with-tags.dto';

@Controller('courses')
export class CourseController {
  constructor(private readonly courseService: CourseService) {}

  // 查询结构化的课程数据
  @Get('formatted')
  async getFormattedCourses() {
    return this.courseService.getFormattedCoursesByType();
  }

  // 查询原始课程数据
  @Get()
  async getCoursesByType() {
    return this.courseService.getCoursesByType();
  }

  // 查询课程详情
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return this.courseService.getCourseById(id);
  }

  // 创建课程
  @Post()
  async create(@Body() dto: CreateCourseWithTagsDto) {
    if (dto.tags && dto.tags.length > 0) {
      const tags = {
        create: dto.tags.map(tagId => ({ tagId })),
      };
      return this.courseService.createCourseWithTags({
        ...dto,
        tags,
      });
    } else {
      return this.courseService.createCourse(dto);
    }
  }
}

八、查询优化建议

8.1 查询优化策略

code
多表联合查询优化策略：
│
├── 1. 选择性查询字段
│   ├── 使用 select 代替 include
│   ├── 只查询需要的字段
│   └── 减少数据传输量
│
├── 2. 分页查询
│   ├── 使用 skip 和 take
│   ├── 避免一次性查询大量数据
│   └── 减少内存占用
│
├── 3. 索引优化
│   ├── 外键字段添加索引
│   ├── 排序字段添加索引
│   └── 频繁查询字段添加索引
│
├── 4. 缓存策略
│   ├── 使用 Redis 缓存查询结果
│   ├── 设置合理的过期时间
│   └── 数据更新时清除缓存
│
└── 5. 查询层级控制
    ├── 避免过深的嵌套查询
    ├── 深度不超过 3 层
    └── 复杂查询考虑分步处理

8.2 使用 select 优化查询

typescript
// 使用 select 只查询需要的字段
async getCoursesByTypeOptimized() {
  return this.prisma.courseTypes.findMany({
    orderBy: {
      order: 'asc',
    },
    select: {
      id: true,
      name: true,
      order: true,
      tags: {
        select: {
          id: true,
          name: true,
          courses: {
            select: {
              course: {
                select: {
                  id: true,
                  title: true,
                  price: true,
                  students: true,
                },
              },
            },
          },
        },
      },
    },
  });
}

8.3 分页查询实现

typescript
// 分页查询
async getCoursesByTypePaginated(page: number = 1, size: number = 10) {
  const skip = (page - 1) * size;
  
  const [data, total] = await this.prisma.$transaction([
    this.prisma.courseTypes.findMany({
      skip,
      take: size,
      orderBy: {
        order: 'asc',
      },
      include: {
        tags: {
          include: {
            courses: {
              include: {
                course: true,
              },
            },
          },
        },
      },
    }),
    this.prisma.courseTypes.count(),
  ]);

  return {
    data,
    total,
    page,
    size,
    totalPages: Math.ceil(total / size),
  };
}

九、常见问题与解决方案

9.1 查询结果数据冗余

问题：嵌套查询返回大量重复数据。

解决方案：

typescript
//  问题：返回重复的 tag 数据
include: {
  courses: {
    include: {
      course: true,
      tag: true,  // 重复，因为已经从 tag 关联过来
    },
  },
}

//  解决：移除重复字段
include: {
  courses: {
    include: {
      course: true,
      // tag: true,  // 移除
    },
  },
}

9.2 排序不生效

问题：orderBy 排序不生效。

原因：orderBy 位置错误。

解决方案：

typescript
//  错误：orderBy 在 include 里面
include: {
  tags: {
    orderBy: { name: 'asc' },  //  错误位置
  },
}
orderBy: { order: 'asc' }  // 正确位置

//  正确：orderBy 在 findMany 同级
await prisma.courseTypes.findMany({
  orderBy: { order: 'asc' },  //  正确位置
  include: {
    tags: true,
  },
});

9.3 数据结构化性能问题

问题：大数据量下数据结构化处理慢。

解决方案：

typescript
//  问题：使用 filter 嵌套循环
const courses = [];
tags.forEach(tag => {
  tag.courses.forEach(ct => {
    if (!courses.find(c => c.id === ct.course.id)) {
      courses.push(ct.course);
    }
  });
});

//  解决：使用 Map 去重
const courseMap = new Map();
tags.forEach(tag => {
  tag.courses.forEach(ct => {
    if (!courseMap.has(ct.course.id)) {
      courseMap.set(ct.course.id, ct.course);
    }
  });
});
const courses = Array.from(courseMap.values());

十、最佳实践总结

10.1 查询最佳实践

code
多表联合查询最佳实践：
│
├── 1. 合理使用 include
│   ├── 只包含需要的关联数据
│   ├── 避免过深的嵌套层级
│   └── 移除重复的关联字段
│
├── 2. 排序位置正确
│   ├── orderBy 在 findMany 同级
│   ├── 嵌套排序在 include 内部
│   └── 多字段排序使用数组
│
├── 3. 数据结构化
│   ├── 在 Service 层处理
│   ├── 使用 Map 去重
│   └── 返回前端友好的格式
│
├── 4. 性能优化
│   ├── 使用 select 选择字段
│   ├── 使用分页查询
│   └── 添加必要的索引
│
└── 5. 缓存策略
    ├── 查询结果缓存
    ├── 设置合理的过期时间
    └── 数据更新时清除缓存

10.2 数据结构化最佳实践

code
数据结构化最佳实践：
│
├── 1. 明确前端需求
│   ├── 了解前端期望的数据结构
│   ├── 减少前端数据处理
│   └── 提高开发效率
│
├── 2. Service 层处理
│   ├── 保持 Controller 层简洁
│   ├── 业务逻辑集中在 Service
│   └── 便于测试和维护
│
├── 3. 数据去重
│   ├── 使用 Map 数据结构
│   ├── 避免重复数据
│   └── 提高查询效率
│
└── 4. 类型安全
    ├── 定义清晰的 DTO
    ├── 使用 TypeScript 类型检查
    └── 避免运行时错误

十一、命令速查表

11.1 Prisma 查询命令速查

操作命令说明
基础查询findMany()查询多条记录
条件查询findMany({ where })条件查询
排序findMany({ orderBy })排序查询
嵌套查询findMany({ include })包含关联数据
字段选择findMany({ select })选择字段
分页查询findMany({ skip, take })分页查询

11.2 排序命令速查

排序方式命令说明
升序orderBy: { field: 'asc' }从小到大
降序orderBy: { field: 'desc' }从大到小
多字段orderBy: [{ f1: 'asc' }, { f2: 'desc' }]多字段排序
嵌套排序include: { relation: { orderBy } }关联数据排序

十二、学习要点总结

12.1 核心概念总结

code
NestJS 多表联合查询核心要点：
│
├── 查询链路
│   ├── CourseTypes → Tags → CourseTags → Courses
│   ├── 4 层关联关系
│   └── 使用 include 嵌套查询
│
├── 嵌套查询
│   ├── include 包含关联数据
│   ├── 支持多层嵌套
│   └── 俄罗斯套娃式查询
│
├── 排序功能
│   ├── orderBy 在 findMany 同级
│   ├── asc 升序、desc 降序
│   └── 支持多字段排序
│
├── 数据结构化
│   ├── Service 层处理
│   ├── Map 去重优化
│   └── 返回前端友好格式
│
└── 性能优化
    ├── 使用 select 选择字段
    ├── 使用分页查询
    └── 添加必要的索引

12.2 学习路径规划

code
学习路径规划：
│
├── 第一阶段：理解概念（1 天）
│   ├── 理解数据表关系
│   ├── 理解查询链路
│   └── 理解嵌套查询原理
│
├── 第二阶段：实践操作（2-3 天）
│   ├── 实现多表联合查询
│   ├── 实现排序功能
│   └── 实现数据结构化
│
└── 第三阶段：深入应用（持续）
    ├── 优化查询性能
    ├── 处理复杂业务场景
    └── 完善错误处理

12.3 重要提示

重要提示：多表联合查询是实际项目中的常见需求，理解数据表关系、掌握 Prisma include 嵌套查询、合理使用排序功能、优化查询性能，对实际项目开发非常重要！特别是要理解嵌套查询的层级关系，避免数据冗余，在 Service 层对数据进行结构化处理，返回前端友好的数据格式！

十三、参考资料