typeorm#SelectQueryBuilder TypeScript Examples

paginateRequest = async (builder: SelectQueryBuilder<any>, req: Request, defaultPerPage: number = 15, maxPerPage: number = 100): Promise<PaginationAwareObject> => {
    const per_page = Math.min(getPerPage(req, defaultPerPage), maxPerPage);
    const page = getPage(req);

    let skip = (page-1)*per_page;

    let [res,count] = await builder
        .skip(skip)
        .take(per_page)
        .getManyAndCount();

    const calcule_last_page = count % per_page;
    const last_page = calcule_last_page === 0 ? count / per_page : Math.trunc(count / per_page) + 1;

    return {
        from:       skip<=count ? skip+1 : null,
        to:         (count > skip+per_page) ? skip+per_page : count,
        per_page:   per_page,
        total:      count,
        current_page: page,
        prev_page:  page > 1? (page-1): null,
        next_page:  count > (skip + per_page) ? page+1 : null,
        last_page:  last_page,
        data:       res || []
    }
}

export function coacheesToMatchQuery(manager: EntityManager): SelectQueryBuilder<Pupil> {
    return manager.createQueryBuilder()
        .select("p")
        .from(Pupil, "p")
        .where("p.active IS TRUE \
                AND p.verification IS NULL \
                AND p.isProjectCoachee IS TRUE \
                AND p.openProjectMatchRequestCount > 0 \
                AND p.projectFields <> '{}' \
                AND split_part(p.email, '@', 2) NOT IN (:...emailDomainExclusions)", { emailDomainExclusions: InvalidEmailDomains});
}

export async function paginate<T>(
  queryBuilder: SelectQueryBuilder<T>,
  serializer: Serializer,
  {query, baseUrl}: Request,
) {
  const page = Number(query.page ?? 1);

  const count = await queryBuilder.cache(60 * 1000).getCount();
  const totalPage = Math.floor(count / PER_PAGE);
  const prevPage = page === 1 ? 1 : page - 1;
  const nextPage = page === totalPage ? page : page + 1;
  const offset = page > 1 ? (page - 1) * PER_PAGE : 0;

  const data = await queryBuilder
    .clone()
    .offset(offset)
    .limit(PER_PAGE)
    .cache(60 * 1000)
    .getMany();

  const getUrlForPage = page =>
    `${baseUrl}?${new URLSearchParams({...query, page})}`;

  const response = serializer.serialize(data);
  response.links = {
    first: getUrlForPage(1),
    last: getUrlForPage(totalPage),
    prev: getUrlForPage(prevPage),
    next: getUrlForPage(nextPage),
  };

  return response;
}

paginate = async function(builder: SelectQueryBuilder<any>, page: number, per_page: number): Promise<PaginationAwareObject> {
    let skip = (page-1)*per_page;
    const total = builder;
    const count = await total.getCount()
    const calcule_last_page = count % per_page;
    const last_page = calcule_last_page === 0 ? count / per_page : Math.trunc(count / per_page) + 1;
    let res = await builder
    .skip(skip)
    .take(per_page)
    .getMany()
    return {
        from:       skip<=count ? skip+1 : null,
        to:         (count > skip+per_page) ? skip+per_page : count,
        per_page:   per_page,
        total:      count,
        current_page: page,
        prev_page:  page > 1? (page-1): null,
        next_page:  count > (skip + per_page) ? page+1 : null,
        last_page:  last_page,
        data:       res || []
    }
}

export function applyQueryParseOutput<T>(
    query: SelectQueryBuilder<T>,
    context: ParseOutput,
) : ParseOutput {
    const keys = Object.keys(context);

    for (let i = 0; i < keys.length; i++) {
        const key = keys[i] as `${Parameter}`;

        switch (key) {
            case Parameter.FIELDS:
                applyQueryFieldsParseOutput(query, context[key]);
                break;
            case Parameter.FILTERS:
                applyQueryFiltersParseOutput(query, context[key]);
                break;
            case Parameter.PAGINATION:
                applyQueryPaginationParseOutput(query, context[key]);
                break;
            case Parameter.RELATIONS:
                applyQueryRelationsParseOutput(query, context[key]);
                break;
            case Parameter.SORT:
                applyQueryRelationsParseOutput(query, context[key]);
                break;
        }
    }

    return context;
}

// IDs where they are the first for each year starting from 2007

function createUserQuery (year, modeId, i) : SelectQueryBuilder<User> {
    let query = User
        .createQueryBuilder("user")
        .innerJoin("user.beatmapsets", "beatmapset","beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
        .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId });
    if (i === yearIDthresholds.length - 1)
        query = query
            .andWhere(`user.osuUserid >= ${yearIDthresholds[i]}`);
    else
        query = query
            .andWhere(`user.osuUserid >= ${yearIDthresholds[i]} and user.osuUserid < ${yearIDthresholds[i + 1]}`);
    return query;
}

applyGetPaged = <T>(qb: SelectQueryBuilder<T>, sortByTableName?: string, params?: TPagedParams<T>): SelectQueryBuilder<T> => {
    const cmsSettings = getStoreItem('cmsSettings');
    const p = params ?? {};
    if (!p.pageNumber) p.pageNumber = 1;
    if (!p.pageSize) {
        const def = cmsSettings?.defaultPageSize
        p.pageSize = (def && typeof def === 'number' && def > 0) ? def : 15;
    }
    if (p.pageSize > MAX_PAGE_SIZE) p.pageSize = MAX_PAGE_SIZE;

    if (p.orderBy && isSimpleString(String(p.orderBy))) {
        if (p.order !== 'DESC' && p.order !== 'ASC') p.order = 'DESC';
        if (sortByTableName) qb.orderBy(`${sortByTableName}.${p.orderBy}`, p.order);
        else qb.orderBy(p.orderBy + '', p.order);
    }

    return qb.skip(p.pageSize * (p.pageNumber - 1)).take(p.pageSize);
}

constructor(queryBuilder: SelectQueryBuilder<any>, criteria: ICriteria, config: IPaginatorConfig = { metadata: {}, helper: null })
    {
        this.queryBuilder = queryBuilder;
        this.filter = criteria.getFilter();
        this.sort = criteria.getSort();
        this.pagination = criteria.getPagination();
        this.offset = this.pagination.getOffset();
        this.limit = this.pagination.getLimit();
        this.metadata = config?.metadata ?? {};
        this.helper = config?.helper ?? null;
    }

filterResultsBuilder = <T>(builder: SelectQueryBuilder<T>, rootEntity: string, aliasPrefix: string) => {
    return builder
        .leftJoinAndSelect(`${rootEntity}._authorIs`, `${aliasPrefix}AuthorIs`)
        .leftJoinAndSelect(`${rootEntity}._itemIs`, `${aliasPrefix}ItemIs`)

        .leftJoinAndSelect(`${aliasPrefix}AuthorIs.criteriaResults`, `${aliasPrefix}AuthorCritResults`)
        .leftJoinAndSelect(`${aliasPrefix}ItemIs.criteriaResults`, `${aliasPrefix}ItemCritResults`)

        .leftJoinAndSelect(`${aliasPrefix}AuthorCritResults.criteria`, `${aliasPrefix}AuthorCriteria`)
        .leftJoinAndSelect(`${aliasPrefix}ItemCritResults.criteria`, `${aliasPrefix}ItemCriteria`)
}

async function PaginateArrayResult<T>(
  query: SelectQueryBuilder<T>,
  page: number,
  pageSize: number,
): Promise<GetManyDefaultResponse<T>> {
  const total = await query.getCount();
  page = Math.max(0, page - 1);
  const data = await query
    .skip(page * pageSize)
    .take(pageSize)
    .getMany();
  return {
    count: data.length,
    data,
    page: page + 1,
    total,
    pageCount: pageSize && total ? Math.ceil(total / pageSize) : undefined,
  };
}

/**
   * Create Sub Query.
   */
  private createSubQuery(isMain: boolean = false) {
    const queryBuilder: SelectQueryBuilder<T> = this.qb
      .createQueryBuilder()
      .subQuery()
    
    if (isMain) {
      queryBuilder.from(this.entityType, this.qb.alias);
    }

    const query = new QueryBuilder<T, T>(
      this.entityType,
      this.queryObject,
      this.qb.alias,
    );

    query.qb = queryBuilder;

    return query;
  }

public async paginate(
    builder: SelectQueryBuilder<Entity>,
  ): Promise<PagingResult<Entity>> {
    const entities = await this.appendPagingQuery(builder).getMany();
    const hasMore = entities.length > this.limit;

    if (hasMore) {
      entities.splice(entities.length - 1, 1);
    }

    if (entities.length === 0) {
      return this.toPagingResult(entities);
    }

    if (!this.hasAfterCursor() && this.hasBeforeCursor()) {
      entities.reverse();
    }

    if (this.hasBeforeCursor() || hasMore) {
      this.nextAfterCursor = this.encode(entities[entities.length - 1]);
    }

    if (this.hasAfterCursor() || (hasMore && this.hasBeforeCursor())) {
      this.nextBeforeCursor = this.encode(entities[0]);
    }

    return this.toPagingResult(entities);
  }

filterResultQueryset(qs: SelectQueryBuilder<ScanTask>) {
    if (this.filters?.name) {
      qs.andWhere('scan.name ILIKE :name', {
        name: `${this.filters?.name}`
      });
    }
    if (this.filters?.status) {
      qs.andWhere('scan_task.status ILIKE :status', {
        status: `${this.filters?.status}`
      });
    }
    return qs;
  }

export function coachesToMatchQuery(manager: EntityManager): SelectQueryBuilder<Student> {
    //NOTE: also those who are screened successful as a tutor will be allowed to get project coaching matches
    return manager.createQueryBuilder()
        .select("s")
        .from(Student, "s")
        .innerJoin("s.projectFields", "projectFieldWithGradeRestriction")
        .leftJoin("s.projectCoachingScreening", "projectCoachingScreening")
        .leftJoin("s.screening", "screening")
        .where("s.active IS TRUE \
                AND s.verification IS NULL \
                AND s.isProjectCoach IS TRUE \
                AND s.openProjectMatchRequestCount > 0 \
                AND split_part(s.email, '@', 2) NOT IN (:...emailDomainExclusions) \
                AND ( \
                    ( projectCoachingScreening.success IS TRUE AND (s.wasJufoParticipant <> 'yes' OR s.hasJufoCertificate IS TRUE OR s.jufoPastParticipationConfirmed IS TRUE OR s.isUniversityStudent IS TRUE) ) \
                    OR (screening.success IS TRUE AND s.isStudent) \
                    )", { emailDomainExclusions: InvalidEmailDomains});
    //NOTE (and probably TODO): if someone has invalid column combinations (that shouldn't be allowed by registration and the general model), those are not caught at the moment. E.g. if s.wasJufoParticipant = NULL AND projectCoachingScreening.success = TRUE, this one could falsely get into the matching even if he's not a university Student.
}

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 async findMedia(by: {id?: string}, options: RepositoryFindOptions = {}): Promise<Optional<Media>> {
    let domainEntity: Optional<Media>;
    
    const query: SelectQueryBuilder<TypeOrmMedia> = this.buildMediaQueryBuilder();
  
    this.extendQueryWithByProperties(by, query);
    
    if (!options.includeRemoved) {
      query.andWhere(this.excludeRemovedMediaClause);
    }
    
    const ormEntity: Optional<TypeOrmMedia> = await query.getOne();
    
    if (ormEntity) {
      domainEntity = TypeOrmMediaMapper.toDomainEntity(ormEntity);
    }
    
    return domainEntity;
  }

function addFilters({
  query,
  modelName,
  allowedFilters,
  filters,
}: AddFiltersParams): SelectQueryBuilder<QuestionModel> {
  for (const filter of filters) {
    if (allowedFilters.includes(filter.type)) {
      APPLY_FILTER_MAP[modelName][filter.type]({ query, filter });
    }
  }
  return query;
}

/**
 * Boot the package by patching the SelectQueryBuilder
 *  
 */
export function pagination(req: Request, res: Response, next: NextFunction):void {
    SelectQueryBuilder.prototype.paginate = async function(per_page?: number|null): Promise<PaginationAwareObject> {
        let current_page =  getPage(req);
        if (!per_page) per_page = getPerPage(req) // If not set, then get from request, default to 15
        else per_page = getPerPage(req, per_page);// If set, check if the request has per_page (which will override), or fallback to the set default
        return await paginate(this,current_page,per_page);
    }
    //console.log("pagination registered");
    next();
}

function joinRelation<Entity>(relationName: string, query: SelectQueryBuilder<Entity>) {
  const meta = query.expressionMap.mainAlias!.metadata;
  const relation = meta.findRelationWithPropertyPath(relationName);

  if (relation) {
    query.innerJoin(`${query.alias}.${relationName}`, relationName);
    return true;
  }

  return false;
}

/**
 * Apply parsed fields parameter data on the db query.
 *
 * @param query
 * @param data
 */
/* istanbul ignore next */
export function applyQueryFieldsParseOutput<T>(
    query: SelectQueryBuilder<T>,
    data: FieldsApplyOutput,
) {
    if (data.length === 0) {
        return data;
    }
    for (let i = 0; i < data.length; i++) {
        const prefix : string = (data[i].alias ? `${data[i].alias}.` : '');
        const key = `${prefix}${data[i].key}`;

        switch (data[i].value) {
            case FieldOperator.INCLUDE:
                query.addSelect(key);
                break;
            case FieldOperator.EXCLUDE:
                // todo: not implemented yet :/
                break;
            default:
                query.select(key);
                break;
        }
    }

    return data;
}

static queryStatistic (year: number, modeId: number): SelectQueryBuilder<Beatmapset> {
        return this
            .createQueryBuilder("beatmapset")
            .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
            .innerJoin("beatmapset.creator", "creator")
            .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
            .limit(1);
    }

applyGetManyFromOne = <T>(qb: SelectQueryBuilder<T>, firstEntityName: string,
    firstEntityProp: keyof T, secondEntityName: string, secondEntityId: number): SelectQueryBuilder<T> => {
    return qb.innerJoin(`${firstEntityName}.${firstEntityProp}`,
        secondEntityName, `${secondEntityName}.id = :entityId`,
        { entityId: secondEntityId });
}

private queryBuilder: SelectQueryBuilder<any>;

getUsersProjectsQuery(userId: string): SelectQueryBuilder<Project> {
    return this.db.createQueryBuilder('project').where('project.userId IN (:...userId)', { userId });
  }

/**
   * Apply Join
   *
   * @param propertySelector Property Selector.
   * @param joinAlias Alias for relation table.
   * @param joinQueryBuilderFn Type of Join.
   * @param joinCondition Condition when join.
   */
  private applyJoinSubQuery(
    subQuery: SelectQueryBuilder<T>,
    joinAlias: string,
    joinQueryBuilderFn: 'INNER' | 'LEFT',
    joinCondition?: (qb: QueryBuilder<T, P>) => QueryBuilder<T, P>,
  ) {
    if (joinCondition && typeof joinCondition === 'function') {
      const query = this.createSubQuery(true);
      const qb = joinCondition(query);
      let condition = '';
      if (qb.queryBuilderParts) {
        for (const part of qb.queryBuilderParts) {
          const condWhere = part.partParams[0];
          const whereQueryBuilderFn = part.partAction[0];
          if (qb.queryBuilderParts.indexOf(part) === 0) {
            condition += condWhere;
          } else {
            condition += ` ${whereQueryBuilderFn} ${condWhere}`;
          }
        }
      }
      if (condition) {
        if (joinQueryBuilderFn === 'INNER') {
          this.qb.innerJoin(sb => subQuery, joinAlias, condition);
        } else {
          this.qb.leftJoin(sb => subQuery, joinAlias, condition);
        }
      }
    } else {
      if (joinQueryBuilderFn === 'INNER') {
        this.qb.innerJoin(sb => subQuery, joinAlias);
      } else {
        this.qb.leftJoin(sb => subQuery, joinAlias);
      }
    }

    return this;
  }

export function createQueryBuilder<T>(entity: ObjectType<T>, alias: string): SelectQueryBuilder<T> {
  return getConnection()
    .getRepository(entity)
    .createQueryBuilder(alias);
}

async filterResultQueryset(qs: SelectQueryBuilder<Domain>, event) {
    if (this.filters?.reverseName) {
      qs.andWhere('domain.name ILIKE :name', {
        name: `%${this.filters?.reverseName}%`
      });
    }
    if (this.filters?.ip) {
      qs.andWhere('domain.ip LIKE :ip', { ip: `%${this.filters?.ip}%` });
    }
    if (this.filters?.port) {
      qs.andHaving('COUNT(CASE WHEN services.port = :port THEN 1 END) >= 1', {
        port: this.filters?.port
      });
    }
    if (this.filters?.service) {
      qs.andHaving(
        'COUNT(CASE WHEN services.products->>0 ILIKE :service THEN 1 END) >= 1',
        { service: `%${this.filters?.service}%` }
      );
    }
    if (this.filters?.organization) {
      qs.andWhere('organization.id = :org', {
        org: this.filters.organization
      });
    }
    if (this.filters?.organizationName) {
      qs.andWhere('organization.name ILIKE :name', {
        name: `%${this.filters?.organizationName}%`
      });
    }
    if (this.filters?.tag) {
      qs.andWhere('organization.id IN (:...orgs)', {
        orgs: await getTagOrganizations(event, this.filters.tag)
      });
    }
    if (this.filters?.vulnerability) {
      qs.andHaving(
        'COUNT(CASE WHEN vulnerabilities.title ILIKE :title THEN 1 END) >= 1',
        {
          title: `%${this.filters?.vulnerability}%`
        }
      );
    }
    return qs;
  }

export function createFilterPupilRequestByStatusQueryFrom(qb: SelectQueryBuilder<Pupil>, status: InterestConfirmationStatus) {
    return qb.andWhere("pticr.status = :status", { status });
}

private buildMediaQueryBuilder(): SelectQueryBuilder<TypeOrmMedia> {
    return this
      .createQueryBuilder(this.mediaAlias)
      .select();
  }