CODEWITHSUNDEEP
javascriptnode.jspostgresqltypeorm
Reminent
Reminent

Reputation: 141

TypeORM select with case insensitive distinct

I'm trying to create a TypeORM query builder that connects to a postgresql DB to get all unique names in the DB. My query looks like this

names = await this._context.manager
        .getRepository(Names)
        .createQueryBuilder('names')
        .select('DISTINCT ON (names.name) names.name')
        .orderBy('names.name', 'ASC')
        .getRawMany();

Right now this query fetches all names in the DB but it's case sensitive so it can't sort out duplicates like 'Jane Doe' from 'jane doe'. So far i've tried to make the distinct upper/lowercase like this: .select('DISTINCT ON LOWER(names.name) names.name') but that doesn't work. I also found a .distinctOn() function but i couldn't make that case insensitive either.

I'm new to typeORM so i'm at a bit of a loss on where to go from here, any ideas?

I'm working in Node.JS towards a postgresql DB if that makes a difference.

Upvotes: 9

Views: 26180

Answers (2)

Muhammad Awais
Muhammad Awais

Reputation: 1976

With Repository, I did the case insensitive search with postgres as database using ILIKE operator in this way

const planning_groups: any[] =
    await this.planningGroupRepository.find({
        where: `"tenant_id" ILIKE '${tenantId}'` 
    });

where tenant_id is a column name and tenantId is a keyword to search for.

Upvotes: 6

noam steiner
noam steiner

Reputation: 4444

The subject of this issue is postgres rather than typeORM. Similar question: Eliminating Duplicate rows in Postgres.

In your case the correct syntax is:

names = await this._context.manager
        .getRepository(Names)
        .createQueryBuilder('names')
        .select('DISTINCT ON (LOWER(names.name)) names.name')
        .orderBy('LOWER(names.name)', 'ASC')
        .getRawMany();

Upvotes: 6

Related Questions