Support extension unaccent contains/equal

[longtruongagn003]

Hi,

I have issues about when using: "where with equal or contains". Database: PostgreSQL Ex: i have table Product (id, name) with data 1 Café 2 Drink

description:I have repoProduct.getOne().where(p => p.name).contains("cafe") actual: data return null; expectations: data return {id:1, name: Café}

Thanks,

[IRCraziestTaxi]

Hey, first of all, sincere apologies for taking over two months to reply to this. I got super busy last couple of months and let issues for this lib slip through the cracks.

This is indeed a problem. I will investigate ways to fix it - I would be surprised if there's not some SQL option exposed through TypeORM or something - although I cannot promise I will find the time in the next week or so.

Thanks for bringing it up! This will be a good fix.

[IRCraziestTaxi]

I did some experimenting with this just now.

My test data (in both MySQL and Postgres):

id | name
1  | Cafe
2  | Café

In MySQL, LIKE works like a charm - case insensitive and accent insensitive. Using WHERE name LIKE 'cafe' returns both records.

In Postgres, on the other hand, where "name" like 'cafe' returns neither record. Using where "name" ilike 'cafe' returns the record without the accent, but not the one with the accent. I'm not very well versed in Postgres, so I'll have to look into how you're supposed to get around the accent sensitivity. From what I've seen, there appears to be a way to do it via an unaccent extension or by messing with collations, but I would have to do a lot of digging to find a way to make it work with this library.

I will leave this issue open in the mean time because I do believe this is a problem that needs to be solved. I openly welcome any input on the approach, though.

[maximelafarie]

I'm using it this way:

qb.andWhere(new Brackets(qb => {
    qb.where('unaccent(LOWER(user.name)) ILIKE unaccent(LOWER(:name))', { name: `%${filter.search}%` })
        .orWhere('unaccent(LOWER(user.email)) ILIKE unaccent(LOWER(:email))', { email: `%${filter.search}%` });
}));

But the trick is here: I created a migration file manually that enables the unaccent function of Postrges

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS unaccent`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP EXTENSION IF EXISTS unaccent`);
    }

Cheers 🖖