Search queries that pass through some filters

34 Views Asked by At

I'd like your help because I'm stuck with a function that doesn't work as expected due to the search filter. I know it's the cause of the misfunctions because with a simple one : it does work well. I expect the search filter to take only the game with names close to the searched one. before it was

                query.andWhere(`unaccent(UPPER(game.name)) LIKE unaccent(UPPER('%${word}%'))`)

and it worked quite well but I wanted more precision and that the user can be able to make a mistake searching the game name so I changed it to :

                const levenshteinQuery = `levenshtein(UPPER(game.name), UPPER('${word}'))`;
            query.andWhere(`unaccent(UPPER(game.name)) LIKE unaccent(UPPER('%${word}%')) OR (${levenshteinQuery}) <= 1`);

But with the search filter written like that, some of the game appear while they should be filtered due to other filters of my functions. I don't understand how to write the function so that it considers both search ways and also applys all other filters.

Here is an overview of the function :

async getAll(
    paginationDto: PaginationDto,
    filter: FilterAnnouncementDto,
    user: UserEntity,
): Promise<PaginatedResult<AnnouncementEntity>> {

    const query = this.announcementRepository
        .createQueryBuilder('announcement')
        .leftJoinAndSelect('announcement.createdBy', 'createdBy')
        .leftJoinAndSelect('announcement.game', 'game')
        .leftJoin('announcement.likers', 'likers')
        .select([
            'announcement.id',
            'announcement.image1',
            'announcement.type',
            'announcement.createdAt',
            'announcement.price',
            'announcement.viewCount',
            'game.image1',
            'game.name'
        ])
        .groupBy('announcement.id')
        .addGroupBy('createdBy.id')
        .addGroupBy('game.id');

    ///add some filter
       ....

    if (filter.search) {
        filter.search = filter.search.replace(/'/g, "''");
        const searchWhere = filter.search.split(' ');
        searchWhere.forEach((word) => {
            const levenshteinQuery = `levenshtein(UPPER(game.name), UPPER('${word}'))`;
            query.andWhere(`unaccent(UPPER(game.name)) LIKE unaccent(UPPER('%${word}%')) OR (${levenshteinQuery}) <= 1`);
        });
    }

    ///add some other stuff
       ....
    const results = await query.getManyAndCount();
            return {
                total: results[1],
                page: paginationDto.page,
                limit: paginationDto.limit,
                results: results[0],
            };
}

Thanks a lot for your help, it's appreciated

0

There are 0 best solutions below