typeorm#Brackets TypeScript Examples

async search(
    projectId: number,
    filter: string,
    page: number,
    pageSize: number,
  ): Promise<GetManyDefaultResponse<BomSecurityException>> {
    const query = this.db
      .createQueryBuilder('se')
      .innerJoin('se.project', 'project')
      .andWhere('project.id = :projectId', { projectId })
      .andWhere(
        new Brackets(subQ => {
          subQ
            .where('lower(se.cveId) like :filter', { filter: `%${filter.toLowerCase()}%` })
            .orWhere('lower(se.notes) like :filter', { filter: `%${filter.toLowerCase()}%` })
            .orWhere('lower(se.securityItemPath) like :filter', { filter: `%${filter.toLowerCase()}%` });
        }),
      )
      .select();

    return await PaginateArrayResult(query, page, pageSize);
  }

/**
 * Apply transformed filter[s] parameter data on the db query.
 *
 * @param query
 * @param data
 */
export function applyFiltersTransformed<T>(
    query: SelectQueryBuilder<T>,
    data: FiltersTransformOutput,
) : FiltersTransformOutput {
    if (data.length === 0) {
        return data;
    }

    /* istanbul ignore next */
    query.andWhere(new Brackets((qb) => {
        for (let i = 0; i < data.length; i++) {
            if (i === 0) {
                qb.where(data[i].statement, data[i].binding);
            } else {
                qb.andWhere(data[i].statement, data[i].binding);
            }
        }
    }));

    return data;
}

public async find(search?: string, page = 1, pagesize = 20): Promise<Paginated<Song>> {
		const query = this.songRepository.createQueryBuilder('Song')

		if (search) {
			query.andWhere(new Brackets(qb => qb
				.where('LOWER(Song.title) LIKE LOWER(:search)', { search: `%${search}%` })
				.orWhere('LOWER(Song.artist) LIKE LOWER(:search)', { search: `%${search}%` })
				.orWhere('LOWER(Song.album) LIKE LOWER(:search)', { search: `%${search}%` })));
		}

		return {
			_embedded: await query
				.skip((page - 1) * pagesize)
				.take(pagesize)
				.getMany(),
			_page: {
				totalEntities: await query.getCount(),
				currentPage: page,
				itemsPerPage: pagesize,
			},
		};
	}

public async findAll(page = 1, pagesize = 200, search = null): Promise<Paginated<User>> {
		const query = this.userRepository.createQueryBuilder('User');

		if (search) {
			query
				.andWhere(new Brackets(qb => qb
					.where('User.firstName LIKE :search', { search: `%${search}%` })
					.orWhere('User.lastName LIKE :search', { search: `%${search}%` })
					.orWhere('User.email LIKE :search', { search: `%${search}%` })))
		}

		return {
			_embedded: await query
				.skip((page - 1) * pagesize)
				.take(pagesize)
				.getMany(),
			_page: {
				totalEntities: await query.getCount(),
				currentPage: page,
				itemsPerPage: pagesize,
			},
		};
	}

public async find(page = 1, pagesize = 20, search = null, includeInvoices = false): Promise<any> {
		const query = this.tenantsRepository.createQueryBuilder('Tenant')

		if (search) {
			query
				.andWhere(new Brackets(qb => qb
					.where('Tenant.name LIKE :search', { search: `%${search}%` })
					.orWhere('Tenant.url LIKE :search', { search: `%${search}%` })))
		}

		if (includeInvoices) {
			query
				.leftJoinAndSelect('Tenant.invoices', 'Invoice')
				.leftJoinAndSelect('Invoice.history', 'History')
				.orderBy('History.createdAt', 'DESC')
		}

		return {
			_embedded: await query
				.skip((page - 1) * pagesize)
				.take(pagesize)
				.getMany(),
			_page: {
				totalEntities: await query.getCount(),
				currentPage: page,
				itemsPerPage: pagesize,
			},
		};
	}

private async checkRecurringSlotForConflicts(slot: Partial<Slot>) {
		// Check a recurring slot against a recurring one
		const recurringSlotsFound = await this.checkSlotAgainstRecurringSlots(slot);

		const foundSlots = [];
		// Check recurring slot against a normal slot :monkaGiga:
		for (let index = 0; index <= 10; index++) {
			// TODO: find a way to not await every loop
			const start = moment.unix(slot.start).add(index, "weeks").unix()
			const end = moment.unix(slot.end).add(index, "weeks").unix()

			const slots = await this.slotRepository.createQueryBuilder('Slot')
				.andWhere(new Brackets(qb => qb
					.where('Slot.start > :start AND Slot.end <= :start', { start })
					.orWhere('Slot.start < :end AND Slot.end >= :end', { end })
					.orWhere('Slot.start > :start AND Slot.end <= :end', { start, end })))
				.andWhere('Slot.recurring = false')
				.getMany();
			foundSlots.push(...slots)
		}
		const lengthCalc = await this.checkSlotLength(slot);

		return [lengthCalc, [...recurringSlotsFound, ...foundSlots]];
	}

private async checkNormalSlotForConflicts(slot: Partial<Slot>) {
		// Check a normal slot against
		const foundSlotsQuery = this.slotRepository.createQueryBuilder('Slot')
			.andWhere(new Brackets(qb => qb
				.where('Slot.start > :start AND Slot.end <= :start', { start: slot.start })
				.orWhere('Slot.start < :end AND Slot.end >= :end', { end: slot.end })
				.orWhere('Slot.start > :start AND Slot.end <= :end', { start: slot.start, end: slot.end })))
			.andWhere('Slot.recurring = false')

		if (slot.uuid) {
			foundSlotsQuery.andWhere('Slot.uuid != :slotUuid', { slotUuid: slot.uuid });
		}

		const foundSlots = await foundSlotsQuery.getMany();

		// Check a normal slot against a recurring
		const recurringSlotsFound = await this.checkSlotAgainstRecurringSlots(slot);

		const lengthCalc = await this.checkSlotLength(slot);

		return [lengthCalc, [...foundSlots, ...recurringSlotsFound]]
	}

public async findOne(contentTypeUuid: string, contentUuid: string, populate = false): Promise<any> {
		const contentType = await this.contentTypeService.findOne(contentTypeUuid);

		if (!contentType) {
			throw new NotFoundException(null, 'Content type could not be found')
		}

		const contentItemQuery = this.contentRepository.createQueryBuilder('Content')
			.where(new Brackets((qb) => qb
				.where('Content.slug = :slug', { slug: contentUuid })
				.orWhere('Content.uuid = :uuid', { uuid: contentUuid })
			))
			.andWhere('Content.contentTypeUuid = :contentTypeUuid', { contentTypeUuid })
			.leftJoinAndSelect('Content.createdBy', 'CreatedBy')
			.leftJoinAndSelect('Content.updatedBy', 'UpdatedBy')
			.leftJoinAndSelect('CreatedBy._userMeta', 'CreatedByMeta')
			.leftJoinAndSelect('UpdatedBy._userMeta', 'UpdatedByMeta');

		const contentItem = await contentItemQuery.getOne();

		if (!contentItem) {
			throw new NotFoundException(null, 'Content item could not be found');
		}

		if (!populate) {
			return contentItem;
		}

		return {
			...contentItem,
			fields: await this.populationService.populateContent(contentItem.fields, contentType.fields),
		};
	}

private buildCursorQuery(
    where: WhereExpressionBuilder,
    cursors: CursorParam,
  ): void {
    const operator = this.getOperator();
    const params: CursorParam = {};
    this.paginationKeys.forEach((key) => {
      params[key] = cursors[key];
      where.andWhere(
        new Brackets((qb) => {
          const paramsHolder = {
            [`${key}_1`]: params[key],
            [`${key}_2`]: params[key],
          };
          qb.where(`${this.alias}.${key} ${operator} :${key}_1`, paramsHolder);
          if (this.paginationUniqueKey !== key) {
            qb.orWhere(`${this.alias}.${key} = :${key}_2`, paramsHolder);
          }
        }),
      );
    });
  }

private appendPagingQuery(
    builder: SelectQueryBuilder<Entity>,
  ): SelectQueryBuilder<Entity> {
    const cursors: CursorParam = {};

    if (this.hasAfterCursor()) {
      Object.assign(cursors, this.decode(this.afterCursor as string));
    } else if (this.hasBeforeCursor()) {
      Object.assign(cursors, this.decode(this.beforeCursor as string));
    }

    if (Object.keys(cursors).length > 0) {
      builder.andWhere(
        new Brackets((where) => this.buildCursorQuery(where, cursors)),
      );
    }

    builder.take(this.limit + 1);
    builder.orderBy(this.buildOrder());

    return builder;
  }

async search(
    projectId: number,
    filter: string,
    page: number,
    pageSize: number,
  ): Promise<GetManyDefaultResponse<BomManualLicense>> {
    const query = this.db
      .createQueryBuilder('ml')
      .innerJoin('ml.project', 'project')
      .andWhere('project.id = :projectId', { projectId })
      .andWhere(
        new Brackets(subQ => {
          subQ
            .where('lower(ml.productName) like :filter', { filter: `%${filter.toLowerCase()}%` })
            .orWhere('lower(ml.referenceUrl) like :filter', { filter: `%${filter.toLowerCase()}%` })
            .orWhere('lower(ml.productVersion) like :filter', { filter: `%${filter.toLowerCase()}%` });
        }),
      )
      .select();

    return await PaginateArrayResult(query, page, pageSize);
  }

getSimpleEventsWhereQuery = (dataSource: DataSource, opts: EventConditions): SelectQueryBuilder<CMEvent> => {
    const query = dataSource.getRepository(CMEvent)
        .createQueryBuilder("event");

    const {
        managerIds,
        related,
        activity,
        author,
    } = opts;

    query.andWhere('event.manager.id IN (:...managerIds)', {managerIds: managerIds});

    if (activity !== undefined) {
        query.leftJoinAndSelect('event.activity', 'activity');

        if (related === undefined) {
            query.andWhere('activity._id = :actId', {actId: activity.id});
        } else {
            if (related === 'all') {
                query.leftJoinAndSelect('activity.author', 'author')
                query.andWhere(new Brackets((qb) => {
                    qb.where(new Brackets(qbAct => orByRelatedActivities(activity, qbAct)))
                        .orWhere(new Brackets(qbAuthor => orByRelatedAuthor(activity, qbAuthor)));
                }))
            } else if (related === 'activity') {
                query.andWhere(new Brackets((qb) => orByRelatedActivities(activity, qb)));
            } else if (related === 'author') {
                query.leftJoinAndSelect('activity.author', 'author')
                query.andWhere(new Brackets((qb) => orByRelatedAuthor(activity, qb)));
            }
        }
    } else if(author !== undefined) {
        const authorVal = parseRedditEntity(author, 'user');

        query.leftJoinAndSelect('event.activity', 'activity');
        query.leftJoinAndSelect('activity.author', 'author')
            .andWhere('author.name = :authorName', {authorName: authorVal.name});
    }

    // can't order by using this AND use "select event id only" in getDistinctEventIdsWhereQuery
    // due to bug in how typeorm handles wrapping sub select for count when using take/skip

    // https://github.com/typeorm/typeorm/issues/4742#issuecomment-858333515
    // https://github.com/typeorm/typeorm/issues/747
    // https://github.com/typeorm/typeorm/issues/3501

    //query.orderBy('event._processedAt', 'DESC');
    query.orderBy('event._processedAt', 'DESC');
    //query.orderBy({'event._processedAt':'DESC'});

    return query;
}

protected async getInvite(id: string): Promise<InviteData | undefined | null> {
        const qb = this.inviteRepo.createQueryBuilder('invite');
        return await qb
            .andWhere({id})
            .andWhere(new Brackets((qb) => {
                    qb.where({_expiresAt: LessThanOrEqual(DateUtils.mixedDateToDatetimeString(dayjs().toDate()))})
                        .orWhere({_expiresAt: IsNull()})
                })
            ).getOne();
    }

applyProductReviewFilter(qb: SelectQueryBuilder<TProductReview>, filterParams?: ProductReviewFilter) {
        this.applyBaseFilter(qb, filterParams);

        // Search by approved
        if (filterParams?.approved !== undefined && filterParams?.approved !== null) {

            if (filterParams.approved) {
                qb.andWhere(`${this.metadata.tablePath}.approved = ${this.getSqlBoolStr(true)}`);
            }

            if (filterParams?.approved === false) {
                const brackets = new Brackets(subQb => {
                    subQb.where(`${this.metadata.tablePath}.approved = ${this.getSqlBoolStr(false)}`);
                    subQb.orWhere(`${this.metadata.tablePath}.approved IS NULL`);
                });
                qb.andWhere(brackets);
            }
        }

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

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

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

applyCategoryFilter(qb: SelectQueryBuilder<ProductCategory>, filterParams?: ProductCategoryFilterInput) {
        this.applyBaseFilter(qb, filterParams);

        // Search by category name or id
        if (filterParams?.nameSearch && filterParams.nameSearch !== '') {
            const likeStr = `%${filterParams.nameSearch}%`;

            const brackets = new Brackets(subQb => {
                subQb.where(`${this.metadata.tablePath}.name ${this.getSqlLike()} :likeStr`, { likeStr });

                if (!isNaN(parseInt(filterParams.nameSearch + '')))
                    subQb.orWhere(`${this.metadata.tablePath}.id = :idSearch`, {
                        idSearch: filterParams.nameSearch
                    });
            });
            qb.andWhere(brackets);
        }
    }

async getEntityMetaByKeys(type: EDBEntity, id: number, keys?: string[]): Promise<Record<string, string> | undefined | null> {
        if (!keys?.length || !id) return;
        keys = keys.filter(Boolean);
        if (!keys.length) return;
        const repo = this.getMetaClass(type)?.getRepository();
        if (!repo) return;

        const qb = repo.createQueryBuilder().select();
        keys.forEach(key => {
            if (!key) return;
            const brackets = new Brackets(subQb => {
                subQb.where({ entityId: id });
                subQb.andWhere({ key });
            });
            qb.orWhere(brackets);
        })
        const metaRecords = await qb.getMany();

        const meta = Object.assign({}, ...(metaRecords.map(record => {
            if (!record.key || !record.value) return {};
            return {
                [record.key]: record.value
            }
        })));
        if (!Object.keys(meta)?.length) return null;
        return meta;
    }

influencesRouter.get("/", async (ctx) => {
    const userSearch = ctx.query.user;
    const yearSearch = ctx.query.year;
    
    if (!ctx.query.mode) {
        return ctx.body = {
            error: "Missing mode",
        };
    }
    const mode = ModeDivisionType[ctx.query.mode.toString()];

    if (typeof yearSearch !== "string" || !/^20[0-9]{2}$/.test(yearSearch)) {
        ctx.body = {
            error: "Invalid year value.",
        };
        return;
    }
    if (typeof userSearch !== "string") {
        ctx.body = {
            error: "Invalid search query value.",
        };
        return;
    }
    
    const user = await User
        .createQueryBuilder("user")
        .leftJoin("user.otherNames", "otherName")
        .leftJoinAndSelect("user.influences", "influence", "influence.userID = user.ID")
        .leftJoinAndSelect("influence.influence", "influenceUser")
        .where(new Brackets(qb => {
            qb.orWhere("user.osuUserid = :userId", { userId: userSearch })
                .orWhere("user.osuUsername LIKE :user")
                .orWhere("otherName.name LIKE :user");
        }))
        .andWhere("influence.year <= :year", { year: yearSearch })
        .andWhere("influence.modeID = :mode", { mode })
        .orderBy("influence.year", "DESC")
        .setParameter("user", `%${userSearch}%`)
        .getOneOrFail();

    const latestRecordedYear = Math.max(...user.influences.map(i => i.year));
    user.influences = user.influences.filter(i => i.year === latestRecordedYear);

    ctx.body = user;
});

applyProductFilter(qb: SelectQueryBuilder<Product>, filterParams?: ProductFilterInput) {
        this.applyBaseFilter(qb, filterParams);

        if (filterParams) {
            if (filterParams.categoryId) {
                applyGetManyFromOne(qb, this.metadata.tablePath, 'categories',
                    getCustomRepository(ProductCategoryRepository).metadata.tablePath, filterParams.categoryId);
            }

            // Attribute filter
            if (filterParams.attributes?.length) {
                const productAttributeTable = AttributeToProduct.getRepository().metadata.tablePath;

                filterParams.attributes.forEach((attr, attrIndex) => {
                    if (!attr.key || !attr.values?.length) return;

                    const joinName = `${productAttributeTable}_${attrIndex}`;
                    qb.leftJoin(AttributeToProduct, joinName,
                        `${joinName}.${this.quote('productId')} = ${this.metadata.tablePath}.id `);
                });

                filterParams.attributes.forEach((attr, attrIndex) => {
                    if (!attr.key || !attr.values?.length) return;
                    const joinName = `${productAttributeTable}_${attrIndex}`;

                    const brackets = new Brackets(subQb1 => {
                        attr.values.forEach((val, valIndex) => {
                            const brackets = new Brackets(subQb2 => {
                                const keyProp = `key_${attrIndex}`;
                                const valueProp = `value_${attrIndex}_${valIndex}`;
                                subQb2.where(`${joinName}.${this.quote('key')} = :${keyProp}`,
                                    { [keyProp]: attr.key });
                                subQb2.andWhere(`${joinName}.${this.quote('value')} = :${valueProp}`,
                                    { [valueProp]: val });
                            });
                            subQb1.orWhere(brackets);
                        });
                    })
                    qb.andWhere(brackets);
                });
            }

            // Search by product name or sku or id
            if (filterParams.nameSearch && filterParams.nameSearch !== '') {
                const nameLikeStr = `%${filterParams.nameSearch}%`;

                const brackets = new Brackets(subQb => {
                    subQb.where(`${this.metadata.tablePath}.name ${this.getSqlLike()} :nameLikeStr`, { nameLikeStr });
                    subQb.orWhere(`${this.metadata.tablePath}.sku ${this.getSqlLike()} :nameLikeStr`, { nameLikeStr });

                    if (!isNaN(parseInt(filterParams.nameSearch + '')))
                        subQb.orWhere(`${this.metadata.tablePath}.id = :idSearch`, {
                            idSearch: filterParams.nameSearch
                        });
                });
                qb.andWhere(brackets);
            }

            // Price filter
            if (filterParams.maxPrice) {
                const query = `${this.metadata.tablePath}.price <= :maxPrice`;
                qb.andWhere(query, { maxPrice: filterParams.maxPrice });
            }
            if (filterParams.minPrice) {
                const query = `${this.metadata.tablePath}.price >= :minPrice`;
                qb.andWhere(query, { minPrice: filterParams.minPrice });
            }
        }
    }

public findOne(id: string): Promise<ContentType | undefined> {
		return this.contentTypeRepository.createQueryBuilder('Content')
			.where(new Brackets(qb => qb.where('Content.uuid = :id', { id }).orWhere('Content.slug = :id', { id })))
			.leftJoinAndSelect('Content.fields', 'Fields')
			.leftJoinAndSelect('Fields.subfields', 'Subfields')
			.orderBy('Fields.order', 'ASC')
			.getOne();
	}

public findOne(id: string): Promise<PageType | undefined> {
		return this.pageTypeRepository.createQueryBuilder('Page')
			.where(new Brackets(qb => qb.where('Page.uuid = :id', { id }).orWhere('Page.slug = :id', { id })))
			.leftJoinAndSelect('Page.fields', 'Fields')
			.leftJoinAndSelect('Fields.subfields', 'Subfields')
			.orderBy('Fields.order', 'ASC')
			.getOne();
	}

applyPostFilter(qb: SelectQueryBuilder<TPost>, filterParams?: PostFilterInput) {
        this.applyBaseFilter(qb, filterParams);

        if (filterParams?.tagIds && filterParams.tagIds.length > 0) {
            qb.leftJoin(`${this.metadata.tablePath}.tags`, getCustomRepository(TagRepository).metadata.tablePath)
            qb.andWhere(`${getCustomRepository(TagRepository).metadata.tablePath}.id IN (:...ids)`, { ids: filterParams.tagIds });
        }
        // Search by title
        if (filterParams?.titleSearch && filterParams.titleSearch !== '') {
            const titleSearch = `%${filterParams.titleSearch}%`;
            const query = `${this.metadata.tablePath}.title ${this.getSqlLike()} :titleSearch`;
            qb.andWhere(query, { titleSearch });
        }

        if (filterParams?.authorId) {
            const authorId = filterParams.authorId;
            const query = `${this.metadata.tablePath}.${this.quote('authorId')} = :authorId`;
            qb.andWhere(query, { authorId });
        }

        // Filter by published
        if (filterParams?.published !== undefined && filterParams?.published !== null) {

            if (filterParams.published === true) {
                qb.andWhere(`${this.metadata.tablePath}.published = ${this.getSqlBoolStr(true)}`);
            }

            if (filterParams.published === false) {
                const brackets = new Brackets(subQb => {
                    subQb.where(`${this.metadata.tablePath}.published = ${this.getSqlBoolStr(false)}`);
                    subQb.orWhere(`${this.metadata.tablePath}.published IS NULL`);
                });
                qb.andWhere(brackets);
            }
        }

        // Filter by featured
        if (filterParams?.featured !== undefined && filterParams?.featured !== null) {

            if (filterParams.featured === true) {
                qb.andWhere(`${this.metadata.tablePath}.featured = ${this.getSqlBoolStr(true)}`);
            }

            if (filterParams.featured === false) {
                const brackets = new Brackets(subQb => {
                    subQb.where(`${this.metadata.tablePath}.featured = ${this.getSqlBoolStr(false)}`);
                    subQb.orWhere(`${this.metadata.tablePath}.featured IS NULL`);
                });
                qb.andWhere(brackets);
            }
        }
        return qb;
    }

static search (year: number, modeString: string, stage: "voting" | "nominating", category: Category, query: StageQuery): Promise<[User[], number]> {
        // Initial repo setup
        const queryBuilder = User.createQueryBuilder("user");
            
        if (stage === "voting") {
            queryBuilder
                .innerJoinAndSelect(
                    "user.nominationsReceived", 
                    "nominationReceived", 
                    "nominationReceived.isValid = true AND nominationReceived.categoryID = :categoryId", 
                    { categoryId: category.ID }
                );
        }

        queryBuilder
            .leftJoinAndSelect("user.otherNames", "otherName")
            .leftJoinAndSelect("user.mcaEligibility", "mca")
            .where(`mca.${modeString} = 1`);

        if (category.filter?.rookie) {
            queryBuilder
                .andWhere((qb) => {
                    const subQuery = qb.subQuery()
                        .from(Beatmapset, "beatmapset")
                        .innerJoin("beatmapset.beatmaps", "beatmap")
                        .select("min(year(approvedDate))")
                        .andWhere("creatorID = user.ID")
                        .andWhere(`beatmap.modeID = ${ModeDivisionType[modeString]}`)
                        .andWhere(`beatmap.difficulty NOT LIKE '%\\'%'`)
                        .getQuery();

                    return subQuery + " = " + year;
                });
        }
        
        // Check for search text
        if (query.text) {
            queryBuilder
                .andWhere(new Brackets(qb => {
                    qb.where("user.osuUsername LIKE :criteria")
                        .orWhere("user.osuUserid LIKE :criteria")
                        .orWhere("user.discordUsername LIKE :criteria")
                        .orWhere("user.discordUserid LIKE :criteria")
                        .orWhere("otherName.name LIKE :criteria");
                }))
                .setParameter("criteria", `%${query.text}%`);
        }
        
        // Ordering
        const order = query.order || "ASC";
        let orderMethod = "user_osuUsername";
        if (query.option && query.option.toLowerCase().includes("id"))
            orderMethod = "CAST(user_osuUserid AS UNSIGNED)";
            
        // Search
        return Promise.all([
            queryBuilder
                .skip(query.skip)
                .take(50)
                .orderBy(orderMethod, order)
                .getMany(),

            queryBuilder.getCount(),
        ]);
    }

static basicSearch (query: MapperQuery) {
        const queryBuilder = User
            .createQueryBuilder("user")
            .leftJoinAndSelect("user.otherNames", "otherName")
            .leftJoinAndSelect("user.mcaEligibility", "mca")
            .where(`mca.year = :q`, { q: parseInt(query.year) });

        // Check mode
        if (query.mode && query.mode in ModeDivisionType) {
            queryBuilder.andWhere(`mca.${query.mode} = true`);
        }
        
        // Remove users with comments already
        if (query.notCommented === "true") {
            queryBuilder.andWhere((qb) => {
                const subQuery = qb.subQuery()
                    .from(UserComment, "userComment")
                    .where("userComment.targetID = user.ID")
                    .getQuery();

                return "NOT EXISTS " + subQuery;
            });
        }

        // osu! friends list
        if (query.friends && query.friends?.length > 0)
            queryBuilder.andWhere("user.osuUserid IN (" + query.friends.join(",") + ")");

        // Check for search text
        if (query.text) {
            queryBuilder
                .andWhere(new Brackets(qb => {
                    qb.where("user.osuUsername LIKE :criteria")
                        .orWhere("user.osuUserid LIKE :criteria")
                        .orWhere("otherName.name LIKE :criteria");
                }))
                .setParameter("criteria", `%${query.text}%`);
        }
        
        // Ordering
        const order = query.order || "ASC";
        let orderMethod = "CAST(user_osuUserid AS UNSIGNED)";
        if (query.option && query.option.toLowerCase().includes("alph"))
            orderMethod = "user_osuUsername";
            
        // Search
        return queryBuilder
            .skip(parseInt(query.skip || "") || 0)
            .take(50)
            .orderBy(orderMethod, order)
            .getMany();
    }

influencesReviewRouter.get("/", async (ctx) => {
    const filter = ctx.query.filter ?? undefined;
    const skip = ctx.query.skip ? parseInt(parseQueryParam(ctx.query.skip) || "") : 0;
    const year = ctx.query.year ? parseInt(parseQueryParam(ctx.query.year) || "") : undefined;
    const text = ctx.query.text ?? undefined;
    const query = Influence
        .createQueryBuilder("influence")
        .innerJoin("influence.user", "user")
        .innerJoin("influence.influence", "influenceUser")
        .innerJoin("influence.mode", "mode")
        .leftJoin("influence.reviewer", "reviewer")
        .select("influence.ID", "ID")
        .addSelect("influence.comment", "comment")
        .addSelect("user.ID", "commenterID")
        .addSelect("influence.isValid", "isValid")
        .addSelect("influence.lastReviewedAt", "lastReviewedAt")
        .addSelect("mode.name", "modeName")
        .addSelect("user.osuUserid", "commenterosuID")
        .addSelect("user.osuUsername", "commenterosuUsername")
        .addSelect("influenceUser.osuUserid", "targetosuID")
        .addSelect("influenceUser.osuUsername", "targetosuUsername")
        .addSelect("reviewer.osuUsername", "reviewer")
        .where("influence.comment IS NOT NULL")
        .andWhere("influence.comment <> ''");
    if (filter)
        query.andWhere(`isValid = 0`);
    if (text) {
        query
            .andWhere(new Brackets(qb => {
                qb.where("user.osuUsername LIKE :criteria")
                    .orWhere("user.osuUserid LIKE :criteria")
                    .orWhere("influenceUser.osuUsername LIKE :criteria")
                    .orWhere("influenceUser.osuUserid LIKE :criteria");
            }))
            .setParameter("criteria", `%${text}%`);
    }
    if (year && !isNaN(year))
        query.andWhere(`year = ${year}`);
    
    const comments = await query.offset(isNaN(skip) ? 0 : skip).limit(10).getRawMany();
    const staffComments = comments.map(comment => {
        const keys = Object.keys(comment);
        const staffComment: StaffComment = {
            ID: comment.ID,
            comment: comment.comment,
            isValid: comment.isValid === 1,
            mode: comment.modeName,
            commenter: {
                ID: 0,
                osuID: "",
                osuUsername: "",
            },
            target: {
                osuID: "",
                osuUsername: "",
            },
            lastReviewedAt: comment.lastReviewedAt ?? undefined,
            reviewer: comment.reviewer ?? undefined,
        };
        for (const key of keys) {
            if (key.includes("commenter"))
                staffComment.commenter[key.replace("commenter", "")] = comment[key];
            else if (key.includes("target"))
                staffComment.target[key.replace("target", "")] = comment[key];
        }

        return staffComment;
    });
    ctx.body = staffComments;
});

async getUserInfo(
    courseId: number,
    page: number,
    pageSize: number,
    search?: string,
    role?: Role,
  ): Promise<UserPartial[]> {
    const query = await getRepository(UserModel)
      .createQueryBuilder()
      .leftJoin(
        UserCourseModel,
        'UserCourseModel',
        '"UserModel".id = "UserCourseModel"."userId"',
      )
      .where('"UserCourseModel"."courseId" = :courseId', { courseId });

    // check if searching for specific role
    if (role) {
      query.andWhere('"UserCourseModel".role = :role', { role });
    }
    // check if searching for specific name
    if (search) {
      const likeSearch = `%${search.replace(' ', '')}%`.toUpperCase();
      query.andWhere(
        new Brackets((q) => {
          q.where(
            'CONCAT(UPPER("UserModel"."firstName"), UPPER("UserModel"."lastName")) like :searchString',
            {
              searchString: likeSearch,
            },
          );
        }),
      );
    }

    // run query
    const users = query
      .select([
        'UserModel.id',
        'UserModel.firstName',
        'UserModel.lastName',
        'UserModel.photoURL',
        'UserModel.email',
      ])
      .orderBy('UserModel.firstName')
      .skip((page - 1) * pageSize)
      .take(pageSize)
      .getMany();

    return users;
  }

commentsReviewRouter.get("/:year", validatePhaseYear, async (ctx) => {
    const mca: MCA = ctx.state.mca;
    const filter = ctx.query.filter ?? undefined;
    const skip = ctx.query.skip ? parseInt(parseQueryParam(ctx.query.skip) || "") : 0;
    const text = ctx.query.text ?? undefined;
    const query = UserComment
        .createQueryBuilder("userComment")
        .innerJoin("userComment.commenter", "commenter")
        .innerJoin("userComment.target", "target")
        .innerJoin("userComment.mode", "mode")
        .leftJoin("userComment.reviewer", "reviewer")
        .select("userComment.ID", "ID")
        .addSelect("userComment.comment", "comment")
        .addSelect("userComment.commenterID", "commenterID")
        .addSelect("userComment.isValid", "isValid")
        .addSelect("userComment.lastReviewedAt", "lastReviewedAt")
        .addSelect("mode.name", "modeName")
        .addSelect("commenter.osuUserid", "commenterosuID")
        .addSelect("commenter.osuUsername", "commenterosuUsername")
        .addSelect("target.osuUserid", "targetosuID")
        .addSelect("target.osuUsername", "targetosuUsername")
        .addSelect("reviewer.osuUsername", "reviewer")
        .where(`year = ${mca.year}`);
    if (filter)
        query.andWhere(`isValid = 0`);
    if (text) {
        query
            .andWhere(new Brackets(qb => {
                qb.where("commenter.osuUsername LIKE :criteria")
                    .orWhere("commenter.osuUserid LIKE :criteria")
                    .orWhere("target.osuUsername LIKE :criteria")
                    .orWhere("target.osuUserid LIKE :criteria");
            }))
            .setParameter("criteria", `%${text}%`);
    }
    
    const comments = await query.offset(isNaN(skip) ? 0 : skip).limit(10).getRawMany();
    const staffComments = comments.map(comment => {
        const keys = Object.keys(comment);
        const staffComment: StaffComment = {
            ID: comment.ID,
            comment: comment.comment,
            isValid: comment.isValid === 1,
            mode: comment.modeName,
            commenter: {
                ID: 0,
                osuID: "",
                osuUsername: "",
            },
            target: {
                osuID: "",
                osuUsername: "",
            },
            lastReviewedAt: comment.lastReviewedAt ?? undefined,
            reviewer: comment.reviewer ?? undefined,
        };
        for (const key of keys) {
            if (key.includes("commenter"))
                staffComment.commenter[key.replace("commenter", "")] = comment[key];
            else if (key.includes("target"))
                staffComment.target[key.replace("target", "")] = comment[key];
        }

        return staffComment;
    });
    ctx.body = staffComments;
});

export async function paginate<T>(
    query: PaginateQuery,
    repo: Repository<T> | SelectQueryBuilder<T>,
    config: PaginateConfig<T>
): Promise<Paginated<T>> {
    let page = query.page || 1
    const limit = Math.min(query.limit || config.defaultLimit || 20, config.maxLimit || 100)
    const sortBy = [] as SortBy<T>
    const searchBy: Column<T>[] = []
    const path = query.path

    function isEntityKey(entityColumns: Column<T>[], column: string): column is Column<T> {
        return !!entityColumns.find((c) => c === column)
    }

    if (config.sortableColumns.length < 1) throw new ServiceUnavailableException()

    if (query.sortBy) {
        for (const order of query.sortBy) {
            if (isEntityKey(config.sortableColumns, order[0]) && ['ASC', 'DESC'].includes(order[1])) {
                sortBy.push(order as Order<T>)
            }
        }
    }

    if (!sortBy.length) {
        sortBy.push(...(config.defaultSortBy || [[config.sortableColumns[0], 'ASC']]))
    }

    if (config.searchableColumns) {
        if (query.searchBy) {
            for (const column of query.searchBy) {
                if (isEntityKey(config.searchableColumns, column)) {
                    searchBy.push(column)
                }
            }
        } else {
            searchBy.push(...config.searchableColumns)
        }
    }

    if (page < 1) page = 1

    let [items, totalItems]: [T[], number] = [[], 0]

    let queryBuilder: SelectQueryBuilder<T>

    if (repo instanceof Repository) {
        queryBuilder = repo
            .createQueryBuilder('e')
            .take(limit)
            .skip((page - 1) * limit)
    } else {
        queryBuilder = repo.take(limit).skip((page - 1) * limit)
    }

    if (config.relations?.length) {
        config.relations.forEach((relation) => {
            queryBuilder.leftJoinAndSelect(`${queryBuilder.alias}.${relation}`, `${queryBuilder.alias}_${relation}`)
        })
    }

    for (const order of sortBy) {
        if (order[0].split('.').length > 1) {
            queryBuilder.addOrderBy(`${queryBuilder.alias}_${order[0]}`, order[1])
        } else {
            queryBuilder.addOrderBy(`${queryBuilder.alias}.${order[0]}`, order[1])
        }
    }

    if (config.where) {
        queryBuilder.andWhere(new Brackets((qb) => qb.andWhere(config.where)))
    }

    if (config.withDeleted) {
        queryBuilder.withDeleted()
    }

    if (query.search && searchBy.length) {
        queryBuilder.andWhere(
            new Brackets((qb: SelectQueryBuilder<T>) => {
                for (const column of searchBy) {
                    const propertyPath = (column as string).split('.')
                    if (propertyPath.length > 1) {
                        const condition: WherePredicateOperator = {
                            operator: 'ilike',
                            parameters: [`${qb.alias}_${column}`, `:${column}`],
                        }
                        qb.orWhere(qb['createWhereConditionExpression'](condition), {
                            [column]: `%${query.search}%`,
                        })
                    } else {
                        qb.orWhere({
                            [column]: ILike(`%${query.search}%`),
                        })
                    }
                }
            })
        )
    }

    if (query.filter) {
        const filter = parseFilter(query, config)
        queryBuilder.andWhere(
            new Brackets((qb: SelectQueryBuilder<T>) => {
                for (const column in filter) {
                    const propertyPath = (column as string).split('.')
                    if (propertyPath.length > 1) {
                        const condition = qb['getWherePredicateCondition'](
                            column,
                            filter[column]
                        ) as WherePredicateOperator
                        let parameters = { [column]: filter[column].value }
                        // TODO: refactor below
                        switch (condition.operator) {
                            case 'between':
                                condition.parameters = [`${qb.alias}_${column}`, `:${column}_from`, `:${column}_to`]
                                parameters = {
                                    [column + '_from']: filter[column].value[0],
                                    [column + '_to']: filter[column].value[1],
                                }
                                break
                            case 'in':
                                condition.parameters = [`${qb.alias}_${column}`, `:...${column}`]
                                break
                            default:
                                condition.parameters = [`${qb.alias}_${column}`, `:${column}`]
                                break
                        }
                        qb.andWhere(qb['createWhereConditionExpression'](condition), parameters)
                    } else {
                        qb.andWhere({
                            [column]: filter[column],
                        })
                    }
                }
            })
        )
    }

    ;[items, totalItems] = await queryBuilder.getManyAndCount()

    let totalPages = totalItems / limit
    if (totalItems % limit) totalPages = Math.ceil(totalPages)

    const sortByQuery = sortBy.map((order) => `&sortBy=${order.join(':')}`).join('')
    const searchQuery = query.search ? `&search=${query.search}` : ''

    const searchByQuery =
        query.searchBy && searchBy.length ? searchBy.map((column) => `&searchBy=${column}`).join('') : ''

    const filterQuery = query.filter
        ? '&' +
          stringify(
              mapKeys(query.filter, (_param, name) => 'filter.' + name),
              '&',
              '=',
              { encodeURIComponent: (str) => str }
          )
        : ''

    const options = `&limit=${limit}${sortByQuery}${searchQuery}${searchByQuery}${filterQuery}`

    const buildLink = (p: number): string => path + '?page=' + p + options

    const results: Paginated<T> = {
        data: items,
        meta: {
            itemsPerPage: limit,
            totalItems,
            currentPage: page,
            totalPages: totalPages,
            sortBy,
            search: query.search,
            searchBy: query.search ? searchBy : undefined,
            filter: query.filter,
        },
        links: {
            first: page == 1 ? undefined : buildLink(1),
            previous: page - 1 < 1 ? undefined : buildLink(page - 1),
            current: buildLink(page),
            next: page + 1 > totalPages ? undefined : buildLink(page + 1),
            last: page == totalPages || !totalItems ? undefined : buildLink(totalPages),
        },
    }

    return Object.assign(new Paginated<T>(), results)
}