nestjs结合prisma关系模型，多对多及多对多对多

最新推荐文章于 2024-07-02 19:21:25 发布

朝秦丶

最新推荐文章于 2024-07-02 19:21:25 发布

阅读量514

点赞数 14

分类专栏： nodejs 文章标签：后端 node.js typescript

本文链接：https://blog.csdn.net/youwe1_/article/details/137896803

版权

nodejs 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

prisma关系模型

多对多

注意：id不能使用bigint！！！！！
官网的例子

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

本人项目

model Menu {
  id          Int            @id @default(autoincrement())
  // 父菜单ID
  parentId    Int            @default(0) @map("parent_id")
  // 父节点ID路径
  treePath    String?        @map("tree_path")
  // 菜单名称
  name        String
  // 菜单类型(1:菜单 2:目录 3:外链 4:按钮)
  type        Int
  // 路由路径(浏览器地址栏路径)
  path        String?
  // 组件路径(vue页面完整路径，省略.vue后缀)
  component   String?
  // 权限标识
  perm        String?
  // 显示状态(1-显示;0-隐藏)
  visible     Int?           @default(1)
  // 排序
  sort        Int?           @default(0)
  // 菜单图标
  icon        String?
  // 跳转路径
  redirect    String?
  createTime  DateTime?      @default(now()) @map("create_time")
  updateTime  DateTime?      @default(now()) @map("update_time")
  // 【目录】只有一个子路由是否始终显示(1:是 0:否)
  always_show Int?
  // 【菜单】是否开启页面缓存(1:是 0:否)
  keep_alive  Int?
  roles       RolesOnMenus[]
}

model User {
  id         Int            @id @default(autoincrement())
  //用户名
  username   String?
  //昵称
  nickname   String?
  //性别((1:男;2:女))
  gender     Int            @default(1)
  //密码
  password   String?
  //部门ID
  deptId     Int?           @map("dept_id")
  //用户头像
  avatar     String?
  //联系方式
  mobile     String?
  // 用户状态((1:正常;0:禁用))
  status     Int            @default(1)
  // 用户邮箱
  email      String?
  // 逻辑删除标识(0:未删除;1:已删除)
  deleted    Int?           @default(0)
  createTime DateTime?      @default(now()) @map("create_time")
  updateTime DateTime?      @default(now()) @map("update_time")
  roles      UsersOnRoles[]
}

model Role {
  id         Int            @id @default(autoincrement())
  // 角色名称
  name       String
  // 角色编码
  code       String?
  // 排序
  sort       Int?           @default(0)
  // 角色状态(1-正常；0-停用)
  status     Int?           @default(1)
  // 数据权限(0-所有数据；1-部门及子部门数据；2-本部门数据；3-本人数据)
  dataScope  Int            @map("data_scope")
  // 逻辑删除标识(0:未删除;1:已删除)
  deleted    Int?           @default(0)
  createTime DateTime?      @default(now()) @map("create_time")
  updateTime DateTime?      @default(now()) @map("update_time")
  menus      RolesOnMenus[]
  users      UsersOnRoles[]
}

model UsersOnRoles {
  user   User @relation(fields: [userId], references: [id])
  userId Int // relation scalar field (used in the `@relation` attribute above)
  role   Role @relation(fields: [roleId], references: [id])
  roleId Int // relation scalar field (used in the `@relation` attribute above)

  @@id([userId, roleId])
  @@map("user_role")
}

model RolesOnMenus {
  role   Role @relation(fields: [roleId], references: [id])
  roleId Int // relation scalar field (used in the `@relation` attribute above)
  menu   Menu @relation(fields: [menuId], references: [id])
  menuId Int // relation scalar field (used in the `@relation` attribute above)

  @@id([roleId, menuId])
  @@map("role_menu")
}

查询多对多用户和角色

async findOne(id: number) {
    const result = await this.prisma.user.findUnique({
      where: {
        id,
      },
      include: {
        roles: {
          select: {
            role: {
              select: {
                code: true,
              },
            },
          },
        },
      },
    });
    return result;
}

{
        "id": 2,
        "username": "admin",
        "nickname": "系统管理员",
        "gender": 1,
        "deptId": 1,
        "avatar": "https://oss.youlai.tech/youlai-boot/2023/05/16/811270ef31f548af9cffc026dfc3777b.gif",
        "mobile": "17621210366",
        "status": 1,
        "email": "",
        "deleted": 0,
        "createTime": "2019-10-10T13:41:22.000Z",
        "updateTime": "2022-07-31T12:39:30.000Z",
        "roles": [
            {
                "role": {
                    "code": "ADMIN"
                }
            }
        ]
    },

扁平化

async findOne(id: number) {  
	const result = await this.prisma.user.findUnique({
      where: {
        id,
      },
      include: {
        roles: {
          select: {
            role: true,
          },
        },
      },
    });

    const roles = result.roles.map((item) => item.role.code);

    const newUser = {
      ...result,
      roles,
    };

    return newUser;
}

{
        "id": 2,
        "username": "admin",
        "nickname": "系统管理员",
        "gender": 1,
        "deptId": 1,
        "avatar": "https://oss.youlai.tech/youlai-boot/2023/05/16/811270ef31f548af9cffc026dfc3777b.gif",
        "mobile": "17621210366",
        "status": 1,
        "email": "",
        "deleted": 0,
        "createTime": "2019-10-10T13:41:22.000Z",
        "updateTime": "2022-07-31T12:39:30.000Z",
        "roles": [
            "ADMIN"
        ]
    },

多对多对多

用户和角色多对多，角色又和菜单多对多，一次查询出来

async findOne(id: number) {
    const result = await this.prisma.user.findUnique({
      where: {
        id,
      },
      include: {
        roles: {
          select: {
            role: {
              select: {
                code: true,
                menus: {
                  select: {
                    menu: {
                      select: {
                        perm: true, // 选择你想包含的菜单字段
                      },
                    },
                  },
                },
              },
            },
          },
        },
      },
    });

    // 获取用户对应的角色数组
    const rolesCodes = result.roles.map((item) => item.role.code);

    // 获取角色对应的菜单权限数组
    const rolesMenusPerm = result.roles
      .map((item) => {
        return item.role.menus
          .filter((item2) => item2.menu.perm) // 过滤出 perm 有值的元素
          .map((item2) => item2.menu.perm);
      })
      .flat();
    // console.log(rolesMenusPerm);

    const newUser = {
      ...result,
      roles: rolesCodes,
      perm: rolesMenusPerm,
    };

    return newUser;
  }

最终结果

{
    "data": {
        "id": 2,
        "username": "admin",
        "nickname": "系统管理员",
        "gender": 1,
        "deptId": 1,
        "avatar": "https://oss.youlai.tech/youlai-boot/2023/05/16/811270ef31f548af9cffc026dfc3777b.gif",
        "mobile": "17621210366",
        "status": 1,
        "email": "",
        "deleted": 0,
        "createTime": "2019-10-10T13:41:22.000Z",
        "updateTime": "2022-07-31T12:39:30.000Z",
        "roles": [
            "ADMIN"
        ],
        "perm": [
            "sys:user:add",
            "sys:user:edit",
            "sys:user:delete",
            "sys:role:add",
            "sys:role:edit",
            "sys:role:delete",
            "sys:menu:add",
            "sys:menu:edit",
            "sys:menu:delete",
            "sys:dept:add",
            "sys:dept:edit",
            "sys:dept:delete",
            "sys:dict_type:add",
            "sys:dict_type:edit",
            "sys:dict_type:delete",
            "sys:dict:add",
            "sys:dict:edit",
            "sys:dict:delete",
            "sys:user:password_reset"
        ]
    },
    "code": "00000",
    "msg": "一切ok"
}