0

I am attempting to create a basic search for my application. I have a GET route that looks like this:

https://my-api.com/v1/organizations

-OR-

https://my-api.com/v1/organizations?name=Stark

I've followed several tutorials, and have tried (with no luck) ILIKE or ILike.

In my database (Postgres), my table is named organizations and it has one row:

name
Stark Industries

Using the following query in my repository file works if I search the same case that is stored in the database: Stark.

...
const { name } = getOrganizationsFilterDto;

const query = await this.createQueryBuilder('organizations');

if (name) {
    query.andWhere('LOWER(organizations.name) LIKE LOWER(:name)', {
        name: `%${name}%`,
    });
}

return await query.getMany();

If I search STARK (all uppercase). I do not get any results back. []. How can I update my query so that STARK or Stark or stark or sTArk would work?

Damon
  • 4,151
  • 13
  • 52
  • 108
  • I think you are searching for full text search: https://www.postgresql.org/docs/current/datatype-textsearch.html. Btw, fixing that query to be sth like: `query.andWhere(\`to_tsvector('simple', name) @@ to_tsquery('simple', :name)`, { name: `${name}:*\` }).getMany()`. For further reading: https://stackoverflow.com/questions/10622021/suggest-like-google-with-postgresql-trigrams-and-full-text-search – deko_39 Apr 15 '22 at 07:13
  • please add the missing backtick, I have bad writing with escaping those characters @Damon – deko_39 Apr 15 '22 at 07:16
  • another option is `if (name) { query.andWhere('LOWER(organizations.name) LIKE :lowerName', { lowerName: `%${name.toLowerCase()}%`, }); } – deko_39 Apr 15 '22 at 07:26

0 Answers0