Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import { Brackets, EntityRepository, Repository, SelectQueryBuilder } from 'typeorm';
- import { COLLATE, QUESTS_TREND_INTERVAL } from '../../common/constants';
- import {
- Duration, QuestRunStatus, QuestStatus,
- RouteType, SearchMode, SortOrder
- } from '../../common/enums';
- import { SearchQuery } from '../../dto/params/search-query';
- import { QuestEntity } from '../entities/quest.entity';
- import { UserEntity } from '../entities/user.entity';
- export interface StatResult {
- status: QuestStatus;
- count: string;
- }
- export interface QuestRunCountResult {
- id: number;
- count: string;
- }
- @EntityRepository(QuestEntity)
- export class QuestsRepository extends Repository<QuestEntity> {
- public getQuest(questId: number, user: UserEntity): Promise<QuestEntity> {
- return this
- .createQueryBuilder('quest')
- .innerJoinAndSelect('quest.user', 'user')
- .leftJoinAndSelect('quest.routeByType', 'route')
- .leftJoinAndSelect('quest.counters', 'counter')
- .leftJoinAndSelect('quest.media', 'media')
- .leftJoinAndMapOne('quest.fan', 'quest.favouredBy', 'fan', 'fan.id = :userId', {userId: user ? user.id : null})
- .where('quest.id = :questId', {questId})
- .getOne();
- }
- /**
- * Ищет квесты в соответствии с параметрами.
- * @param query параметры фильтрации/пагинации
- * @param userId ID текущего пользователя
- * @param authorId ID пользователя - автора квестов
- */
- public searchQuests(query: SearchQuery, userId?: number, authorId?: number): Promise<[QuestEntity[], number]> {
- const builder = this
- .createQueryBuilder('quest')
- .innerJoinAndSelect('quest.user', 'user')
- .leftJoinAndSelect('quest.counters', 'counter')
- .leftJoinAndSelect('quest.media', 'media')
- .addSelect(qb => qb
- .select('rating_counter.value / runs_counter.value')
- .from('quest_counter', 'rating_counter')
- .innerJoin('quest_counter', 'runs_counter', 'rating_counter.quest_id = runs_counter.quest_id')
- .where('quest.id = rating_counter.quest_id')
- .andWhere(`rating_counter.code = 'QUEST_RATING'`)
- .andWhere(`runs_counter.code = 'QUEST_RUNS'`),
- 'rating'
- )
- .where('quest.status = :status', {status: query.status || QuestStatus.ACTIVE});
- if (userId) {
- builder.leftJoinAndMapOne('quest.fan', 'quest.favouredBy', 'fan', 'fan.id = :userId', {userId});
- }
- this.buildSearchFilter(builder, query, userId, authorId);
- this.buildSearchOrder(builder, query);
- if ([
- SearchMode.TREND,
- SearchMode.RECOMMENDED,
- SearchMode.SUBSCRIPTIONS
- ].indexOf(query.searchMode) === -1 || query.full) {
- // builder.skip(query.offset).take(query.limit);
- }
- return builder.getManyAndCount();
- }
- /**
- * Возвращает статистику по квестам пользователя,
- * отфильтрованным в соответствии с параметрами.
- * @param query параметры фильтрации
- * @param userId ID пользователя
- */
- public getQuestStats(query: SearchQuery, userId: number): Promise<StatResult[]> {
- const builder = this.createQueryBuilder('quest');
- this.buildSearchFilter(builder, query, userId);
- return builder
- .select('quest.status', 'status')
- .addSelect('COUNT(DISTINCT quest.id)', 'count')
- .groupBy('quest.status')
- .getRawMany();
- }
- /**
- * Возвращает количество прохождений квестов за последний месяц.
- * TODO: выпилить с обновлением typeorm до 0.3.0.
- * @param questIds идентификаторы квестов
- */
- public getQuestRunCountLastMonth(questIds: number[]): Promise<QuestRunCountResult[]> {
- const builder = this
- .createQueryBuilder('quest')
- .select('quest.id', 'id')
- .whereInIds(questIds);
- this.selectQuestRunCountLastMonth(builder, 'count');
- return builder.getRawMany();
- }
- private buildSearchFilter(
- builder: SelectQueryBuilder<QuestEntity>,
- query: SearchQuery,
- userId?: number,
- authorId?: number
- ): void {
- const {searchString} = query;
- if (searchString) {
- let str = searchString.trim().toLowerCase();
- if (str.length > 2) {
- const fts = str.split(/\s+/).map(str => `${str}:*`).join(' | ');
- // quest_search_index - это специальная табличка, связанная 1:1 с quest и
- // содержащая индекс (наименование, описания, ключевые слова) для полнотекстового поиска
- builder
- .innerJoin('quest_search_index', 'qsi', 'qsi.quest_id = quest.id')
- .andWhere(`TO_TSQUERY('russian', :fts) @@ qsi.search_index`, {fts});
- } else if (str.length > 0) {
- str = `%${str}%`;
- builder.andWhere(new Brackets(qb => qb
- .where(`LOWER(quest.name COLLATE ${COLLATE}) LIKE :str`, {str})
- .orWhere(`LOWER(quest.short_description COLLATE ${COLLATE}) LIKE :str`, {str})
- .orWhere(`LOWER(quest.full_description COLLATE ${COLLATE}) LIKE :str`, {str})
- .orWhere(`LOWER(ARRAY_TO_STRING(quest.keywords, ' ') COLLATE ${COLLATE}) LIKE :str`, {str})
- ));
- }
- }
- const {category} = query;
- if (category) {
- builder.andWhere(':category = ANY(quest.categories)', {category});
- }
- const {location} = query;
- if (location) {
- builder.andWhere(':location = ANY(quest.regions)', {location});
- }
- // делаем выборку независимо от того, используется ли в фильтре
- builder.leftJoinAndSelect('quest.routeByType', 'route');
- const {routeType} = query;
- // игнорируем параметры routeLengthFrom, routeLengthTo и duration до
- // решения вопроса с длиной и продолжительностью маршрутов типа BOAT
- if (routeType === RouteType.BOAT) {
- builder.andWhere(':routeType = ANY(quest.routeTypes)', {routeType});
- } else {
- const {routeLengthFrom, routeLengthTo, duration} = query;
- if (routeLengthFrom) {
- builder.andWhere('route.length >= :routeLengthFrom', {routeLengthFrom});
- }
- if (routeLengthTo) {
- builder.andWhere('route.length <= :routeLengthTo', {routeLengthTo});
- }
- if (duration === Duration.SHORT) {
- builder.andWhere('route.duration < 2');
- } else if (duration === Duration.MIDDLE) {
- builder.andWhere('route.duration BETWEEN 2 AND 4');
- } else if (duration === Duration.LONG) {
- builder.andWhere('route.duration BETWEEN 4 AND 8');
- } else if (duration === Duration.VERYLONG) {
- builder.andWhere('route.duration > 8');
- }
- if (routeType) {
- builder.andWhere('route.routeType = :routeType', {routeType});
- }
- }
- if (!userId && query.searchMode !== SearchMode.PROFILE) return;
- switch (query.searchMode) {
- case SearchMode.TREND: {
- if (!query.full) {
- builder.andWhere('quest.popular = TRUE');
- }
- break;
- }
- case SearchMode.RECOMMENDED: {
- // этот запрос написан от безысходности, в попытке выразить требование {
- // есть прохождение у участника, у которого в списке пройденных есть хотя бы
- // один квест, который прошел текущий пользователь ИЛИ квест создан автором,
- // квесты которого уже проходил текущий пользователь
- // }
- // вероятно, требование можно переформулировать, а запрос - упростить, либо иначе
- // решить задачу рекомендаций (cron tasks? машинное обучение?)
- builder.andWhere(new Brackets(qb => qb
- .where('quest.recommend = TRUE')
- .orWhere(`
- quest.id IN (
- SELECT quest_id
- FROM quest_run
- WHERE quest_id IN (
- SELECT quest_id
- FROM quest_run
- WHERE user_account_id = :userId
- AND status = :runStatus
- )
- AND user_account_id != :userId
- AND status = :runStatus
- )
- `, {
- userId,
- runStatus: QuestRunStatus.FINISHED
- })
- .orWhere(`
- quest.author_id IN (
- SELECT author_id
- FROM quest
- INNER JOIN quest_run
- ON quest.id = quest_run.quest_id
- AND quest_run.user_account_id = :userId
- WHERE author_id != :userId
- AND quest_run.status = :runStatus
- )
- `, {
- userId,
- runStatus: QuestRunStatus.FINISHED
- })
- ));
- break;
- }
- case SearchMode.SUBSCRIPTIONS: {
- // этот запрос так же уныл, как и предыдущий
- builder.andWhere(new Brackets(qb => qb
- .orWhere(`
- quest.author_id IN (
- SELECT user_id
- FROM user_follower
- WHERE follower_id = :userId
- )
- `, {userId})
- .orWhere(`
- quest.id IN (
- SELECT quest_id
- FROM quest_run
- WHERE status = :runStatus
- AND user_account_id IN (
- SELECT user_id
- FROM user_follower
- WHERE follower_id = :userId
- )
- )
- `, {
- userId,
- runStatus: QuestRunStatus.FINISHED
- })
- ));
- break;
- }
- case SearchMode.FAVOURITES: {
- builder
- .innerJoin('user_quest', 'uq', 'uq.quest_id = quest.id')
- .andWhere('uq.user_account_id = :userId', {userId});
- break;
- }
- case SearchMode.PROFILE: {
- authorId = authorId || userId;
- builder.andWhere('quest.author_id = :authorId', {authorId});
- break;
- }
- }
- }
- private async buildSearchOrder(
- builder: SelectQueryBuilder<QuestEntity>,
- query: SearchQuery
- ): any {
- const defaultOrder = query.sort === SortOrder.DATE ?
- 'quest.creationDate' : 'rating';
- switch (query.searchMode) {
- case SearchMode.TREND:
- case SearchMode.RECOMMENDED:
- case SearchMode.SUBSCRIPTIONS: {
- if (query.full && !query.sort) {
- this.selectQuestRunCountLastMonth(builder);
- builder.orderBy('runs_month', 'DESC', 'NULLS LAST');
- } else {
- builder.orderBy(defaultOrder, 'DESC', 'NULLS LAST');
- }
- break;
- }
- case SearchMode.FAVOURITES: {
- builder.orderBy(defaultOrder, 'DESC', 'NULLS LAST');
- break;
- }
- case SearchMode.PROFILE: {
- builder.orderBy('quest.updateDate', 'DESC', 'NULLS LAST');
- break;
- }
- default: {
- builder.orderBy(defaultOrder, 'DESC', 'NULLS LAST');
- }
- }
- if (query.searchMode === SearchMode.TREND) {
- builder.addSelect(`CASE
- WHEN "quest"."id" = 30 THEN 0
- ELSE 1
- END`, 'incurr').orderBy('incurr', 'ASC')
- const r = await builder.getOne();
- console.log(r);
- }
- }
- private selectQuestRunCountLastMonth(builder: SelectQueryBuilder<QuestEntity>, alias = 'runs_month'): void {
- // количество прохождений за последний месяц
- builder.addSelect(qb => qb
- .select('COUNT(id)')
- .from('quest_run', 'quest_run')
- .where('quest.id = quest_run.quest_id')
- .andWhere(`quest_run.startDate + INTERVAL '${QUESTS_TREND_INTERVAL}' > NOW()`),
- alias
- );
- }
- public getLastDraftInEdit(user: UserEntity): Promise<QuestEntity> {
- return this
- .createQueryBuilder('quest')
- .where('quest.author_id = :userId', {userId: user.id})
- .andWhere('quest.status = :status', {status: QuestStatus.DRAFT})
- .orderBy('quest.updateDate', 'DESC')
- .limit(1)
- .getOne();
- }
- public suggestKeywords(searchString: string, limit: number): Promise<{ kw: string }[]> {
- const str = searchString.trim().toLowerCase();
- return this.query(`
- WITH keywords AS (
- SELECT DISTINCT UNNEST(keywords) AS kw
- FROM quest
- )
- SELECT kw, POSITION($1 IN LOWER(kw COLLATE ${COLLATE})) AS pos
- FROM keywords
- WHERE LOWER(kw COLLATE ${COLLATE}) LIKE $2
- ORDER BY pos, kw
- LIMIT $3
- `, [str, `%${str}%`, limit]);
- }
- public cleanFavourite(quest: QuestEntity): Promise<void> {
- return this.query('DELETE FROM user_quest WHERE quest_id = $1', [quest.id]);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement