typeorm#Repository TypeScript Examples

getCollectionData = async (
  sites: Site[],
  latestDataRepository: Repository<LatestData>,
): Promise<Record<number, CollectionDataDto>> => {
  const siteIds = sites.map((site) => site.id);

  if (!siteIds.length) {
    return {};
  }

  // Get latest data
  const latestData: LatestData[] = await latestDataRepository
    .createQueryBuilder('latest_data')
    .select('id')
    .addSelect('timestamp')
    .addSelect('value')
    .addSelect('site_id', 'siteId')
    .addSelect('survey_point_id', 'surveyPointId')
    .addSelect('metric')
    .addSelect('source')
    .where('site_id IN (:...siteIds)', { siteIds })
    .andWhere('source != :hoboSource', { hoboSource: SourceType.HOBO })
    .getRawMany();

  // Map data to each site and map each site's data to the CollectionDataDto
  return _(latestData)
    .groupBy((o) => o.siteId)
    .mapValues<CollectionDataDto>((data) =>
      data.reduce<CollectionDataDto>((acc, siteData): CollectionDataDto => {
        return {
          ...acc,
          [siteData.metric]: siteData.value,
        };
      }, {}),
    )
    .toJSON();
}

@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(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,
    };
  }
}

/**
   * Called before post insertion.
   */

  async afterInsert(event: InsertEvent<CommentEntity>) {
    const postRepo: Repository<PostEntity> = event.connection.manager.getRepository<
      PostEntity
    >('posts');
    const commentRepo: Repository<CommentEntity> = event.connection.manager.getRepository<
      CommentEntity
    >('comments');
    commentRepo
      .count({
        where: { post: { id: event.entity.post.id } },
      })
      .then((count: number) => {
        postRepo.update({ id: event.entity.post.id }, { comments_num: count });
      });
  }

public async down(queryRunner: QueryRunner): Promise<any> {
    const repo: Repository<PackageManager> = this.connection.getRepository(PackageManager);

    await repo.delete({ code: 'python2_7-pip' });

    const python3 = await repo.findOne('python3-pip');

    delete python3.metaData;
    python3.description = 'Python3 + Pip';
    await repo.save(python3);
  }

constructor(
    @InjectRepository(UserOrmEntity)
    private readonly userRepository: Repository<UserOrmEntity>,
  ) {
    super(
      userRepository,
      new UserOrmMapper(UserEntity, UserOrmEntity),
      new Logger('UserRepository'),
    );
  }

/**
   * Inject into UserService: userRepository
   *
   * @param userRepository
   */
  constructor(
    @InjectRepository(UserEntity)
    private readonly userRepository: Repository<UserEntity>,
  ) {
  }

@Service()
@EntityRepository(CashSplitSum)
export default class CashSplitSumRepository extends Repository<CashSplitSum> {
    async findCustom(parameters: TransactionDatabaseParameters): Promise<CashSplitSum[]> {
        let qb = this.createQueryBuilder('s');

        qb = QueryBuilderUtil.addDateFilter(
            qb,
            's',
            'createdDate',
            parameters.createdDateFrom,
            parameters.createdDateTo,
        );
        qb = QueryBuilderUtil.addDateFilter(
            qb,
            's',
            'updatedDate',
            parameters.updatedDateFrom,
            parameters.updatedDateTo,
        );

        if (parameters.accountIdList && parameters.accountIdList.length > 0) {
            qb = qb.andWhere(`s.accountId IN ( ${parameters.accountIdList.join(', ')} )`);
        }

        if (parameters.currencyIdList && parameters.currencyIdList.length > 0) {
            qb = qb.andWhere(`s.currencyId IN ( ${parameters.currencyIdList.join(', ')} )`);
        }

        return await qb.getMany();
    }

    async deleteAll(): Promise<DeleteResult> {
        return await this.createQueryBuilder().delete().execute();
    }
}

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

constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>,
    @InjectRepository(Purse)
    private readonly purseRepository: Repository<Purse>,
    /**
     * [PurseSavingService] injected to current service
     * and all its methods will be transactional in the transaction
     * which will be initiated from the [AppController]
     */
    private readonly purseSavingService: PurseSavingService,
    moduleRef: ModuleRef,
  ) {
    super(moduleRef);
  }

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

export async function reviveConfessions(repository: Repository<Confession>) {
  console.log(`Getting unviewed confessions...`);
  let unviewedConfessions;
  try {
    unviewedConfessions = await repository.find({ viewed: false });
  } catch (_) {
    throw `Failed to fetch unviewed confessions!`;
  }
  for (const record of unviewedConfessions) {
    console.log(`Removing old message (if any) and ignoring errors...`);
    if (record.staging_ts) {
      try {
        await web.chat.delete({
          channel: staging_channel,
          ts: record.staging_ts,
        });
      } catch (_) {
        console.log(
          `Warning: failed to delete staging message... continuing anyways`
        );
      }
    }
    const newTs = await postStagingMessage(record.id, record.text);
    console.log(`Updating record...`);
    try {
      record.staging_ts = newTs;
      await repository.save(record);
    } catch (_) {
      throw `Failed to update Postgres record!`;
    }
  }
  console.log(`Restaged all unviewed confessions!`);
}

async function getRegion(
  longitude: number,
  latitude: number,
  regionRepository: Repository<Region>,
) {
  const googleRegion = await getGoogleRegion(longitude, latitude);
  const regions = await regionRepository.find({
    where: { name: googleRegion },
  });

  if (regions.length > 0) {
    return regions[0];
  }
  return googleRegion
    ? regionRepository.save({
        name: googleRegion,
        polygon: createPoint(longitude, latitude),
      })
    : undefined;
}

private findRepository(
    entityType: Function,
  ): Repository<PolymorphicChildInterface | never> {
    const repositoryToken = this.resolveRepositoryToken(entityType);

    const repository: Repository<PolymorphicChildInterface> =
      repositoryToken !== entityType
        ? this.manager.getCustomRepository(repositoryToken)
        : this.manager.getRepository(repositoryToken);

    if (!repository) {
      throw new RepositoryNotFoundException(repositoryToken);
    }

    return repository;
  }

@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(Article)
export class ArticleRepository extends Repository<Article> {
  async getById(id: number): Promise<Article> {
    const article = await this.findOne(id);
    if (!article) {
      throw new NotFoundException();
    }

    return article;
  }
}

constructor(
    @InjectRepository(FormEntity)
    private readonly formRepository: Repository<FormEntity>,
    @InjectRepository(FormFieldEntity)
    private readonly formFieldRepository: Repository<FormFieldEntity>,
    @InjectRepository(FormHookEntity)
    private readonly formHookRepository: Repository<FormHookEntity>,
    private readonly idService: IdService,
    private readonly pageService: FormPageUpdateService,
  ) {
  }

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

public async buildSitemap() {
        const settings = await getCmsSettings();
        if (!settings?.url) throw new HttpException("CmsService::buildSitemap: could not find website's URL", HttpStatus.INTERNAL_SERVER_ERROR);
        if (!settings.themeName) throw new HttpException("CmsService::buildSitemap: could not find website's themeName", HttpStatus.INTERNAL_SERVER_ERROR);
        const configs = await getThemeConfigs(settings.themeName);
        setStoreItem('defaultPages', configs.themeConfig?.defaultPages);

        const urls: string[] = [];
        let content = '';

        const addPage = (route: string, updDate: Date) => {
            if (!route.startsWith('/')) route = '/' + route;
            const redirect = findRedirect(route);
            if (redirect?.type === 'redirect' && redirect.to) {
                route = redirect.to;
            }

            if (redirect?.type === 'rewrite' && redirect.from === '/404') return;

            if (!route.startsWith('http')) {
                route = settings.url + route;
            }

            if (urls.includes(route)) return;
            urls.push(route);

            content +=
                `  <url>
    <loc>${route}</loc>
    <lastmod>${format(updDate, 'yyyy-MM-dd')}</lastmod>
  </url>\n`;
        }

        configs.themeConfig?.pages?.forEach(page => {
            if (!page.route || page.route.includes('[slug]') ||
                page.route.includes('[id]') || page.route === 'index'
                || page.route === '404') return;

            addPage(
                page.route,
                new Date(Date.now())
            );
        })

        const outputEntity = async (repo: Repository<any>, pageName: TDefaultPageName) => {
            const entities: BasePageEntity[] = await repo.find({
                select: ['slug', 'id', 'updateDate', 'createDate'],
            });

            entities.forEach(ent => {
                const updDate = ent.updateDate ?? ent.createDate;
                addPage(
                    resolvePageRoute(pageName, { slug: ent.slug ?? ent.id + '' }),
                    updDate ?? new Date(Date.now())
                );
            });

        }

        await outputEntity(getCustomRepository(PostRepository), 'post');
        await outputEntity(getCustomRepository(ProductRepository), 'product');
        await outputEntity(getCustomRepository(ProductCategoryRepository), 'category');
        await outputEntity(getCustomRepository(TagRepository), 'tag');

        content = `<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
${content}
</urlset>`;

        await fs.outputFile(resolve(getPublicDir(), 'default_sitemap.xml'), content, {
            encoding: 'UTF-8'
        });
        return true;
    }

protected repository: Repository<T>;

private ormRepository: Repository<Appointment>;

private ormRepository: Repository<Customer>;

invokeeRepo: Repository<InvokeeType>;

@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> {}

private ormRepository: Repository<Appointment>;

constructor(
    @InjectRepository(CommentEntity)
    private commentRepo: Repository<CommentEntity>,
  ) {
    super(commentRepo);
  }

constructor(
    @InjectRepository(UserEntity)
    private readonly usersModel: Repository<UserEntity>,
  ) {}

public async down(queryRunner: QueryRunner): Promise<any> {
    const repo: Repository<PackageManager> = this.connection.getRepository(PackageManager);

    await repo.delete({ code: 'python3' });
  }