用户组织权限管理系统

技术栈：

前端：Vue + ElementUi + TypeScript
后端：nest.js + mysql + redis

演示地址

用户组织管理系统（演示地址）
github

功能设计

BSp-blog.png

数据库设计

用户实体

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 500 })
  name: string;

  @Column({nullable: true, type: 'text'})
  desc: string;

  @Column({
    nullable: true,
    length: 100,
    select: false,
  })
  password: string;

  @Column( {select: false} )
  email: string;

  @Column({nullable: true})
  age: string;

  @Column({nullable: true})
  address: string;

  @Column({nullable: true})
  nick: string;

  @Column({default: 0})
  status: number;

  @ManyToOne(type => Role, role => role.users)
  role: Role;

  @ManyToMany( type => Organization, orientation => orientation.users)
  organizations: Organization[];

  @Column({default: 0})
  isDelete: number;

  @Column({default: '', nullable: true })
  crateTime: string;

  @Column({default: '', nullable: true })
  updateTime: string;

  @Column({default: '', nullable: true })
  deleteTime: string;
}

角色实体

@Entity()
export class Role {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 500 })
  name: string;

  @Column('text', {nullable: true})
  desc: string;

  @Column()
  code: string;

  @ManyToMany(type => Authority, authority => authority.roles)
  @JoinTable()
  authority: Authority[];

  @OneToMany(type => User, user => user.role)
  users: User[];

  @Column({default: 0})
  isDelete: number;

  @Column({default: '', nullable: true })
  crateTime: string;

  @Column({default: '', nullable: true })
  updateTime: string;

  @Column({default: '', nullable: true })
  deleteTime: string;
}

资源实体

@Entity()
export class Authority {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 500 })
  name: string;

  @Column('text', {nullable: true})
  desc: string;

  @Column()
  path: string;

  @Column()
  value: string;

  @Column()
  parentId: number;

  @Column({default: '', nullable: true })
  parentName: string;

  @Column({nullable: true})
  icon: string;

  @Column({nullable: false})
  system: string;

  @Column()
  code: string;

  @ManyToMany(type => Role, role => role.authority)
  roles: Role[];

  @Column({default: 0})
  isDelete: number;

  @Column({default: '', nullable: true })
  crateTime: string;

  @Column({default: '', nullable: true })
  updateTime: string;

  @Column({default: '', nullable: true })
  deleteTime: string;
}

API实现

数据库配置(main.module.ts)

TypeOrmModule.forRoot(
        {
          type: 'mysql',
          host: mysqlConfig.host,
          port: 3306,
          username: mysqlConfig.userName,
          password: mysqlConfig.password,
          database: 'b_simple_user_center',
          entities: [join(__dirname, '**/**.entity{.ts,.js}')],
          synchronize: true,
        },
    ),

全局缓存

CacheModule.register({
          store: redisStore,
          host: redisCacheConfig.host,
          port: redisCacheConfig.port,
          ttl: redisCacheConfig.ttl, // seconds
          max: redisCacheConfig.max, // seconds
      }),

业务层实现

该系统中使用typeorm操作数据库，常见的typeorm操作方式包Entity Manager 和Query Builder，结合系统多条件查询场景，因此采用Query Builder方式，控制层和服务层为一一对应关系，代码内参见src/controller、src/service

采坑点

nest多条件查询

...
const queryConditionList = ['c.isDelete = :isDelete'];
            if (query.name) {
                queryConditionList.push('c.name LIKE :name');
            }
            const queryCondition = queryConditionList.join(' AND ');
            const res = await this.productBrandRepository
                .createQueryBuilder('c')
                .where(queryCondition, {
                    name: `%${query.name}%`,
                    isDelete: 0,
                })
                .orderBy('c.name', 'ASC')
                .skip((query.page - 1) * query.pageSize)
                .take(query.pageSize)
                .getManyAndCount();
...

typeorm实体间任意联查

typeorm中联查任意实体（在实体关系间没有表关联）时，getManyAndCount无法查询关联字段，必须采用getRawAndEntities，typeorm文档书写有误。

...
            const res = await this.productAttributeRepository
                .createQueryBuilder('c')
                .leftJoinAndSelect(ProductAttributeCategoryEntity, 'a', 'a.id = c.product_attribute_category_id')
                .where(queryCondition, {
                    name: `%${query.name}%`,
                    isDelete: 0,
                    productAttributeCategoryId: Number(query.cateAttrId),
                    type: Number(query.type),
                })
                .orderBy('c.name', 'ASC')
                .skip((query.page - 1) * query.pageSize)
                .take(query.pageSize)
                .getRawAndEntities();


...