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.
Santiago Trujillo
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();
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.