Search by first character in a column Typeorm PostgreSQL

RAHUL KUNDU 0
Viewed 32

I have a company table where I want to search companies by their name of the first letter. In my front end I have a series of letters as checkboxes from A to z and I am passing selected letters as a,b,c in my request body.

If I execute this raw query then it is working, but how I can write this in typeorm querybuilder

select * FROM companies WHERE lower(substring(name from 1 for 1)) = ANY (ARRAY['m'])

I have tried with the query below -

public async getCompanies(
   pageOptionsQuery: CompanyPageOptionsDto,
): Promise<Company[]> {
        const queryBuilder = this._companyRepository
            .createQueryBuilder()
            .select('*');

        if (!!pageOptionsQuery.filter_by) {
            //pageOptionsQuery.filter_by is like series of selected letters
            //a,b,c

            const filters = pageOptionsQuery.filter_by.split(',');

            queryBuilder.where('LOWER(name) IN (:...filters )', {
                filters: filters,
            });
        }

        return queryBuilder.getRawMany();
}
typeorm nestjs postgresql
1 Answers

This is what I have come up with -

public async getCompanies(
   pageOptionsQuery: CompanyPageOptionsDto,
): Promise<Company[]> {
        const queryBuilder = this._companyRepository
            .createQueryBuilder()
            .select('*');

        if (!!pageOptionsQuery.filter_by) {
            //pageOptionsQuery.filter_by is like series of selected letters
            //a,b,c

            const filters = pageOptionsQuery.filter_by.split(',');

            queryBuilder.where(
                'LOWER(SUBSTRING(name, 1, 1)) IN (:...filters)',
                {
                    filters,
                },
            );
        }

        return queryBuilder.getRawMany();
}
RAHUL KUNDU0
Related