TypeOrm CheatSheet

link：数据库、TypeOrm、MySql

分页模糊查询

const { pageNo, pageSize, brandName } = query;
const [list, total] = await this.brandRepository.findAndCount({
  take: pageSize,
  skip: (pageNo - 1) * pageSize,
  where: [
	{
	  ...(brandName ? { brandName: Like(`%${brandName}%`) } : {}),
	},
  ],
});

分页模糊查询 QueryBuilder

const { pageNo, pageSize, brandName } = query;
 
const [list, total] = await this.brandRepository
	.createQueryBuilder('brand')
	.where('brand.brandName like :name', { name: `%${brandName || ''}%` })
	// .leftJoinAndMapMany('color.detail', DetailEntity, 'detail', 'color.id = detail.color_id')
	.skip((pageNo - 1) * pageSize)
	.take(pageSize)
	.getManyAndCount();

多表关联查询

typeorm查询两个没有关联关系的实体 - 码上快乐

    /*
     * @Description:颜色
     * @Author: ManLou
     * @Date: 2021-07-03 15:32:05
     */
    
    import { Entity, PrimaryGeneratedColumn, Column, UpdateDateColumn, CreateDateColumn, Generated } from 'typeorm';
    
    @Entity({ name: 'color' })
    export class ColorEntity {
      @PrimaryGeneratedColumn()
      id: number;
    
      @Column({ name: 'color_name', comment: '颜色名称' })
      colorName: string;
    
      @Column({ name: 'color_number', comment: '色号', default: '' })
      colorNumber: string;
    
      @Column({ name: 'brand_id', comment: '所属品牌 ID' })
      brandId: number;
    
      @Column({ name: 'model_id', comment: '所属车型 ID' })
      modelId: number;
    
      // @Column({ name: 'order_id', comment: '编号' })
      // @Generated()
      // orderId: number;
    
      @Column('decimal', { precision: 15, scale: 2, default: 0 })
      a: number;
    
      @Column('decimal', { precision: 15, scale: 2, default: 0 })
      b: number;
    
      @Column('decimal', { precision: 15, scale: 2, default: 0 })
      l: number;
    
      @Column({ name: 'operator_name', comment: '操作人昵称', nullable: true })
      operatorName: string;
    
      @Column({ name: 'operator_id', comment: '操作人 ID' })
      operatorId: number;
    
      @Column({ comment: '备注信息', default: '' })
      memo?: string;
    
      @Column()
      @Generated('uuid')
      uuid?: string;
    
      @Column({ name: 'is_enable', comment: '是否启用1启用 2 否', default: 1 })
      isEnable?: 1 | 2;
    
      @Column({ comment: '图片地址', nullable: true })
      path?: string;
    
      @Column('text', { name: 'spray_memo', comment: '喷涂备注', nullable: true })
      sprayMemo?: string;
    
      @Column({ comment: '不知道干啥的', nullable: true })
      delta?: number;
    
      @CreateDateColumn({ name: 'created_date' })
      createdDate: Date;
    
      @UpdateDateColumn({ name: 'updated_date' })
      updatedDate: Date;
    }

    import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm';
    
    @Entity('detail')
    export class DetailEntity {
      @PrimaryGeneratedColumn()
      id: number;
    
      @Column({ name: 'color_id' })
      colorId: number;
    
      @Column({ name: 'color_masterbatch_name', length: 100, comment: '名称', nullable: true })
      colorMasterBatchName: string;
    
      @Column({ name: 'color_masterbatch_type', length: 40, comment: '色母型号', nullable: true })
      colorMasterBatchType: string;
    
      @Column('decimal', { precision: 15, scale: 1, default: 0, nullable: true, comment: '重量' })
      amount: number;
    
      @Column({ comment: '备注' })
      memo: string;
    
      @CreateDateColumn({ name: 'created_date' })
      createdDate: Date;
    
      @UpdateDateColumn({ name: 'updated_date' })
      updatedDate: Date;
    }

Color Detail 实体
查询单个颜色下的颜色详情

await this.colorRepository
	  .createQueryBuilder('color')
	  .where('color.id = :colorId', { colorId })
	  .leftJoinAndMapMany('color.detail', DetailEntity, 'postExtend', 'color.id=postExtend.color_id')
	  .getOne();

返回结果 → leftJoinAndMapMany 返回的是数组，使用 leftJoinAndMapOne 返回的是单个详情数据

    {
      "id": 15,
      "colorName": "好家伙",
      "colorNumber": "COS",
      "brandId": 1,
      "modelId": 1,
      "a": "-1.98",
      "b": "2.36",
      "l": "-7.25",
      "operatorName": "admin",
      "operatorId": 1,
      "memo": "这是备注信息",
      "uuid": "874629a7-ab70-4420-a7b7-674a252b7d1f",
      "isEnable": 2,
      "path": "<https://baidu.com>",
      "sprayMemo": "喷三遍",
      "delta": null,
      "createdDate": "2021-07-03T15:16:30.563Z",
      "updatedDate": "2021-07-03T15:16:30.563Z",
      "detail": [
        {
          "id": 1,
          "colorId": 15,
          "colorMasterBatchName": "柠檬黄",
          "colorMasterBatchType": "APS-1233",
          "amount": "39.2",
          "memo": "调白色漆用，带蓝相，遮盖力好，性能忧异。",
          "createdDate": "2021-07-03T15:16:37.611Z",
          "updatedDate": "2021-07-03T15:16:37.611Z"
        },
        {
          "id": 2,
          "colorId": 15,
          "colorMasterBatchName": "细蓝珍珠Plus黄",
          "colorMasterBatchType": "JC-M13",
          "amount": "39.2",
          "memo": "鲜艳的红色母，略带蓝相，颜色纯。",
          "createdDate": "2021-07-03T15:16:37.611Z",
          "updatedDate": "2021-07-03T15:16:37.611Z"
        }
      ]
    }

排序、多表关联+条件

const [list, total] = await this.colorRepository
	  .createQueryBuilder('color')
	  .where('color.colorName like :name', { name: `%${colorName || ''}%` })
	  .andWhere(brandIdWhere, { brandId: brandId || 0 })
	  .skip((pageNo - 1) * pageSize)
	  .take(pageSize)
	  .orderBy('color.createdDate', 'DESC')
	  .leftJoinAndMapOne('color.brand', BrandEntity, 'brand', 'color.brandId = brand.id')
	  .leftJoinAndMapOne('color.model', ModelEntity, 'model', 'color.modelId = model.id')
	  .getManyAndCount();

Entity transformer 转换

对某个字段存、取的时候做数据转换操作

/// ColumnNumericTransformer
export class ColumnNumericTransformer {
 to(data: number): number {
   return data;
 }
 from(data: string): number {
   return parseFloat(data);
 }
}

@Column('numeric', {
	precision: 7,
	scale: 2,
	transformer: new ColumnNumericTransformer(),
  })
  public myNumericColumn: number;