typeorm#EntityRepository TypeScript Examples

@EntityRepository(Card)
export class CardRepository extends Repository<Card> {
  async useCard(id: number): Promise<Card> {
    const card = await this.findOne(id);
    if (!card) throw new NotFoundException();
    if (card.getStatus()) throw new BadRequestException();
    const usingCard = (
      await this.find({
        where: { using: true, type: card.getType() },
      })
    ).length;
    if (usingCard >= 150 && card.getType() == 0)
      throw new BadRequestException();
    if (usingCard >= 150 && card.getType() == 1)
      throw new BadRequestException();
    card.useCard();
    await this.save(card);
    return card;
  }

  async returnCard(card: Card): Promise<void> {
    if (!card.getStatus()) throw new BadRequestException();
    card.returnCard();
    await this.save(card);
  }
}

@EntityRepository(Customer)
export class CustomerRepository extends Repository<Customer>  {

    public async TodayCustomerCount(todaydate: string): Promise<any> {

        const query: any = await this.manager.createQueryBuilder(Customer, 'customer');
        query.select([  'COUNT(customer.id) as customerCount']);
        query.where('DATE(customer.createdDate) = :todaydate', {todaydate});
        console.log(query.getQuery());
        return query.getRawOne();
    }
}

@EntityRepository(Transaction)
class TransactionsRepository extends Repository<Transaction> {
  public async getBalance(): Promise<Balance> {
    const transactions = await this.find();

    const income = transactions.reduce((incomeIncrement, transaction) => {
      if (transaction.type === 'income') {
        return incomeIncrement + transaction.value;
      }

      return incomeIncrement + 0;
    }, 0);

    const outcome = transactions.reduce((outcomeIncrement, transaction) => {
      if (transaction.type === 'outcome') {
        return outcomeIncrement + transaction.value;
      }

      return outcomeIncrement + 0;
    }, 0);

    return {
      income,
      outcome,
      total: income - outcome,
    };
  }
}

@EntityRepository(LoginLog)
export class LoginLogRepository extends Repository<LoginLog> {
    public async logList(limit: number): Promise<any> {
        const query: any = await this.manager.createQueryBuilder(LoginLog, 'LoginLog');
        query.select(['COUNT(LoginLog.id) as logcount', 'DATE(created_date) as createdDate']);
        query.groupBy('createdDate');
        query.orderBy('createdDate', 'DESC');
        query.limit(limit);
        console.log(query.getQuery());
        return query.getRawMany();
    }
}

@EntityRepository(Class)
export default class ClassRepository extends Repository<Class> {
  public async findByName(name: string): Promise<Class[]> {
    return this.find({
      where: {
        name,
      },
    });
  }
}

@EntityRepository(Discipline)
export default class DisciplineRepository extends Repository<Discipline> {
  public async findByName(name: string): Promise<Discipline[]> {
    return this.find({
      where: {
        name,
      },
    });
  }
}

@EntityRepository(User)
export class UserRepository extends Repository<User> {
  async getById(id: number): Promise<User> {
    const user = await this.findOne(id);
    if (!user) {
      throw new NotFoundException();
    }

    return user;
  }
}

@EntityRepository(Product)
export class ProductRepository extends Repository<Product> {
  private perPage: 20;

  public search(filters: ProductSearchFilters): SelectQueryBuilder<Product> {
    const query = this.createQueryBuilder()
      .where('published IS TRUE')
      .orderBy('updatedAt', 'DESC');

    if (filters.title !== undefined) {
      query.andWhere('lower(title) LIKE :title', {title: `%${filters.title}%`});
    }

    if (filters.priceMin !== undefined) {
      query.andWhere('price >= :priceMin', {priceMin: filters.priceMin});
    }

    if (filters.priceMax !== undefined) {
      query.andWhere('price <= :priceMax', {priceMax: filters.priceMax});
    }

    return query;
  }

  public paginate(
    query: SelectQueryBuilder<Product>,
    page: number | string,
  ): SelectQueryBuilder<Product> {
    if (page) {
      console.log(Number(page));
      query.offset(Number(page) * this.perPage);
    }

    return query.limit(this.perPage);
  }
}

@EntityRepository(TypeOrmUser)
export class TypeOrmUserRepositoryAdapter extends BaseRepository<TypeOrmUser> implements UserRepositoryPort {
  
  private readonly userAlias: string = 'user';
  
  private readonly excludeRemovedUserClause: string = `"${this.userAlias}"."removedAt" IS NULL`;
  
  public async findUser(by: {id?: string, email?: string}, options: RepositoryFindOptions = {}): Promise<Optional<User>> {
    let domainEntity: Optional<User>;
    
    const query: SelectQueryBuilder<TypeOrmUser> = this.buildUserQueryBuilder();
    
    this.extendQueryWithByProperties(by, query);
    
    if (!options.includeRemoved) {
      query.andWhere(this.excludeRemovedUserClause);
    }
    
    const ormEntity: Optional<TypeOrmUser> = await query.getOne();
    
    if (ormEntity) {
      domainEntity = TypeOrmUserMapper.toDomainEntity(ormEntity);
    }
    
    return domainEntity;
  }
  
  public async countUsers(by: {id?: string, email?: string}, options: RepositoryFindOptions = {}): Promise<number> {
    const query: SelectQueryBuilder<TypeOrmUser> = this.buildUserQueryBuilder();
  
    this.extendQueryWithByProperties(by, query);
  
    if (!options.includeRemoved) {
      query.andWhere(this.excludeRemovedUserClause);
    }
  
    return query.getCount();
  }
  
  public async addUser(user: User): Promise<{id: string}> {
    const ormUser: TypeOrmUser = TypeOrmUserMapper.toOrmEntity(user);
    
    const insertResult: InsertResult = await this
      .createQueryBuilder(this.userAlias)
      .insert()
      .into(TypeOrmUser)
      .values([ormUser])
      .execute();
    
    return {
      id: insertResult.identifiers[0].id
    };
  }
  
  public async updateUser(user: User): Promise<void>{
    const ormUser: TypeOrmUser = TypeOrmUserMapper.toOrmEntity(user);
    await this.update(ormUser.id, ormUser);
  }
  
  private buildUserQueryBuilder(): SelectQueryBuilder<TypeOrmUser> {
    return this
      .createQueryBuilder(this.userAlias)
      .select();
  }
  
  private extendQueryWithByProperties(by: {id?: string, email?: string}, query: SelectQueryBuilder<TypeOrmUser>): void {
    if (by.id) {
      query.andWhere(`"${this.userAlias}"."id" = :id`, {id: by.id});
    }
    if (by.email) {
      query.andWhere(`"${this.userAlias}"."email" = :email`, {email: by.email});
    }
  }
  
}

@EntityRepository(AccessToken)
export class AccessTokenRepository extends Repository<AccessToken>  {

}

@EntityRepository(User)
export class UserRepository extends Repository<User> {}

@EntityRepository(Category)
export class CategoryRepository extends Repository<Category>  {

}

@EntityRepository(Placement)
export class PlacementRepository extends Repository<Placement> {}

@EntityRepository(EmailTemplate)
export class EmailTemplateRepository extends Repository<EmailTemplate>  {

}

@EntityRepository(Role)
class RoleRepository extends Repository<Role> {}

@EntityRepository(Manufacturer)
export class ManufacturerRepository extends Repository<Manufacturer>  {

}

@EntityRepository(UserInfo)
export class UserRepository extends Repository<UserInfo> {
  async register(authCredentialsDto: AuthCredentialsDto): Promise<void> {
    const { phone, pwd } = authCredentialsDto;

    // const exists = this.findOne({ username })

    // if (exists) {
    //   // throw some error
    // }
    // const salt = await bcrypt.genSalt()
    // console.log('TCL: UserRepository -> salt', salt)

    // const user = new User()
    const user = this.create();
    user.phone = phone;
    user.salt = await bcrypt.genSalt();
    user.pwd = await this.hashPassword(pwd, user.salt);
    user.userId = uuid();

    try {
      await user.save();
    } catch (error) {
      if (error.code === '23505') {
        // duplicate username
        throw new ConflictException('Username already exists');
      } else {
        throw new InternalServerErrorException();
      }
    }
  }

  async validateuserPassword(loginData: LoginByPwdDto): Promise<any> {
    const { phone, pwd } = loginData;
    const user = await this.findOne({ phone });

    if (user && (await user.validatePassword(pwd))) {
      // 删除敏感信息再返回，用这种结构的方式可以删除 user 表里的 salt 跟 pwd
      const { salt, pwd, ...userInfo } = user;
      return userInfo;
    } else {
      return null;
    }
  }

  private async hashPassword(password: string, salt: string) {
    return bcrypt.hash(password, salt);
  }
}

@EntityRepository(Product)
class ProductRepository extends Repository<Product> {}

@EntityRepository(Log)
export class LogRepository extends Repository<Log> {}

@EntityRepository(EntityClass)
    class GenericRepository extends BaseRepository<EntityType, EntityInputType> {
        constructor() {
            super(EntityClass)
        }
    }

@EntityRepository(PostComment)
export class PostCommentRepository extends Repository<PostComment> {
  /**
   * 포스트 Id와 댓글 Id가 일치하는 댓글 정보를 조회한다.
   * @param postId 포스트 Id
   * @param commentId 댓글 Id
   */
  public async getCommentById(postId: string, commentId: string) {
    return this.createQueryBuilder("comment")
      .where("comment.id = :commentId", { commentId })
      .andWhere("comment.postId = :postId", { postId })
      .getOne();
  }

  /**
   * 포스트 Id가 일치하며 답글을 제외한 댓글들을 조회한다.
   * @param postId 포스트 Id
   */
  public async getCommentsByPostId(postId: string) {
    return this.createQueryBuilder("comment")
      .select([
        "comment.id",
        "comment.text",
        "comment.createdAt",
        "comment.postId",
        "comment.isReplies",
        "comment.isDeleted",
      ])
      .leftJoinAndSelect("comment.user", "user")
      .where("comment.postId = :postId", { postId })
      .andWhere("comment.depth = :value", { value: 0 })
      .orderBy("comment.createdAt", "ASC")
      .getMany();
  }

  /**
   * 사용자가 작성한 삭제되지 않은 댓글과 답글들을 조회한다.
   * @param userId 사용자 Id
   */
  public async getCommentsByUserId(userId: string) {
    return this.createQueryBuilder("comment")
      .select([
        "comment.id",
        "comment.text",
        "comment.createdAt",
        "comment.postId",
      ])
      .where("comment.userId = :userId", { userId })
      .andWhere("comment.isDeleted = :value", { value: false })
      .orderBy("comment.createdAt", "DESC")
      .getMany();
  }

  /**
   * 포스트 Id와 댓글 Id가 일치하는 댓글의 답글들을 조회한다.
   * @param postId 포스트 Id
   * @param commentId 댓글 Id
   */
  public async getCommentReplies(postId: string, commentId: string) {
    return this.createQueryBuilder("comment")
      .select([
        "comment.id",
        "comment.parent",
        "comment.depth",
        "comment.text",
        "comment.createdAt",
        "comment.postId",
        "comment.isDeleted",
      ])
      .leftJoinAndSelect("comment.user", "user")
      .where("comment.postId = :postId", { postId })
      .andWhere("comment.parent = :commentId", { commentId })
      .orderBy("comment.createdAt", "ASC")
      .getMany();
  }
}

@EntityRepository(User)
export class UserRepository extends Repository<User> {}

@EntityRepository(Question)
export class QuestionRepository extends Repository<Question> {}

@EntityRepository(Order)
export class OrderRepository extends Repository<Order> {}

@EntityRepository(Permission)
class PermissionRepository extends Repository<Permission> {}

@EntityRepository(User)
export class UsersRepository extends Repository<User> {}

@EntityRepository(Option)
export class OptionRepository extends Repository<Option> {}

@EntityRepository(AdvertEntity)
export class AdvertRepository extends AbstractPolymorphicRepository<AdvertEntity> {}

@EntityRepository(Order)
export class OrderRepository extends BaseRepository<Order> {

    constructor() {
        super(Order)
    }

    async getOrders(params?: TPagedParams<TOrder>): Promise<TPagedList<Order>> {
        logger.log('OrderRepository::getOrders');
        return this.getPaged(params)
    }

    async getOrderById(id: number): Promise<Order | undefined> {
        logger.log('OrderRepository::getOrderById id: ' + id);
        return this.getById(id);
    }

    async getOrderBySlug(slug: string): Promise<Order | undefined> {
        logger.log('OrderRepository::getOrderBySlug slug: ' + slug);
        return this.getBySlug(slug);
    }

    private async handleBaseOrderInput(order: Order, input: TOrderInput) {
        if (input.customerEmail && !validateEmail(input.customerEmail))
            throw new HttpException('Provided e-mail is not valid', HttpStatus.BAD_REQUEST);

        order.status = input.status;
        order.cart = Array.isArray(input.cart) ? JSON.stringify(input.cart) : input.cart;
        order.orderTotalPrice = input.orderTotalPrice;
        order.cartTotalPrice = input.cartTotalPrice;
        order.cartOldTotalPrice = input.cartOldTotalPrice;
        order.shippingPrice = input.shippingPrice;
        order.totalQnt = input.totalQnt;
        order.userId = input.userId;
        order.customerName = input.customerName;
        order.customerPhone = input.customerPhone;
        if (order.customerPhone) {
            if (typeof order.customerPhone === 'number') order.customerPhone = order.customerPhone + '';
            order.customerPhone = order.customerPhone.replace(/\W/g, '');
        }
        order.customerEmail = input.customerEmail;
        order.customerAddress = input.customerAddress;
        order.customerComment = input.customerComment ? sanitizeHtml(input.customerComment, {
            allowedTags: []
        }) : undefined;
        order.shippingMethod = input.shippingMethod;
        order.paymentMethod = input.paymentMethod;
        order.currency = input.currency;

        if (input.couponCodes) order.coupons = await getCustomRepository(CouponRepository)
            .getCouponsByCodes(input.couponCodes);

        await order.save();
        await handleCustomMetaInput(order as any, input);
    }

    async createOrder(inputData: TOrderInput, id?: number | null): Promise<Order> {
        logger.log('OrderRepository::createOrder');
        let order = new Order();
        if (id) order.id = id;

        await this.handleBaseOrderInput(order, inputData);
        order = await this.save(order);
        return order;
    }

    async updateOrder(id: number, inputData: TOrderInput): Promise<Order | undefined> {
        logger.log('OrderRepository::updateOrder id: ' + id);
        let order = await this.getById(id);

        await this.handleBaseOrderInput(order, inputData);
        order = await this.save(order);
        return order;
    }

    async deleteOrder(id: number): Promise<boolean> {
        logger.log('OrderRepository::deleteOrder; id: ' + id);

        const order = await this.getOrderById(id);
        if (!order) {
            logger.log('OrderRepository::deleteOrder failed to find Order by id');
            return false;
        }
        const res = await this.delete(id);
        return true;
    }

    applyOrderFilter(qb: SelectQueryBuilder<TOrder>, filterParams?: OrderFilterInput) {
        this.applyBaseFilter(qb as SelectQueryBuilder<TBasePageEntity>, filterParams);

        // Search by status
        if (filterParams?.status && filterParams.status !== '') {
            const query = `${this.metadata.tablePath}.status = :statusSearch`;
            qb.andWhere(query, { statusSearch: filterParams.status });
        }

        // Search by orderId
        if (filterParams?.orderId && filterParams.orderId !== '') {
            const query = `${this.metadata.tablePath}.id = :orderId`;
            qb.andWhere(query, { orderId: filterParams.orderId });
        }

        // Search by customerName
        if (filterParams?.customerName && filterParams.customerName !== '') {
            const customerNameSearch = `%${filterParams.customerName}%`;
            const query = `${this.metadata.tablePath}.${this.quote('customerName')} ${this.getSqlLike()} :customerNameSearch`;
            qb.andWhere(query, { customerNameSearch });
        }

        // Search by customerPhone
        if (filterParams?.customerPhone && filterParams.customerPhone !== '') {
            const customerPhone = filterParams.customerPhone.replace(/\W/g, '');
            const customerPhoneSearch = `%${customerPhone}%`;
            const query = `${this.metadata.tablePath}.${this.quote('customerPhone')} ${this.getSqlLike()} :customerPhoneSearch`;
            qb.andWhere(query, { customerPhoneSearch });
        }

        // Search by customerEmail
        if (filterParams?.customerEmail && filterParams.customerEmail !== '') {
            const customerEmailSearch = `%${filterParams.customerEmail}%`;
            const query = `${this.metadata.tablePath}.${this.quote('customerEmail')} ${this.getSqlLike()} :customerEmailSearch`;
            qb.andWhere(query, { customerEmailSearch });
        }

        // Search by create date
        if (filterParams?.dateFrom && filterParams.dateFrom !== '') {
            const dateFrom = new Date(Date.parse(filterParams.dateFrom));
            const dateTo = new Date(filterParams.dateTo ? Date.parse(filterParams.dateTo) : Date.now());

            const query = `${this.metadata.tablePath}.${this.quote('createDate')} BETWEEN :dateFrom AND :dateTo`;
            qb.andWhere(query, {
                dateFrom: DateUtils.mixedDateToDatetimeString(dateFrom),
                dateTo: DateUtils.mixedDateToDatetimeString(dateTo),
            });
        }
    }

    async getFilteredOrders(pagedParams?: PagedParamsInput<TOrder>, filterParams?: OrderFilterInput): Promise<TPagedList<TOrder>> {
        const qb = this.createQueryBuilder(this.metadata.tablePath);
        qb.select();
        this.applyOrderFilter(qb, filterParams);
        return await getPaged<TOrder>(qb, this.metadata.tablePath, pagedParams);
    }


    async deleteManyFilteredOrders(input: TDeleteManyInput, filterParams?: OrderFilterInput): Promise<boolean | undefined> {
        const qbSelect = this.createQueryBuilder(this.metadata.tablePath).select([`${this.metadata.tablePath}.id`]);
        this.applyOrderFilter(qbSelect, filterParams);
        this.applyDeleteMany(qbSelect, input);

        const qbDelete = this.createQueryBuilder(this.metadata.tablePath).delete()
            .where(`${this.metadata.tablePath}.id IN (${qbSelect.getQuery()})`)
            .setParameters(qbSelect.getParameters());

        await qbDelete.execute();
        return true;
    }

    async getOrdersOfUser(userId: number, pagedParams?: PagedParamsInput<TOrder>): Promise<TPagedList<TOrder>> {
        const qb = this.createQueryBuilder(this.metadata.tablePath);
        qb.select();
        qb.where(`${this.metadata.tablePath}.${this.quote('userId')} = :userId`, {
            userId,
        });
        return await getPaged<TOrder>(qb, this.metadata.tablePath, pagedParams);
    }

    async getCouponsOfOrder(id: number): Promise<Coupon[] | undefined | null> {
        return (await this.findOne(id, {
            relations: ['coupons']
        }))?.coupons;
    }
}