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