ILIKE find operator

[ivanveselin]

Issue type:

[ ] question
[ ] bug report
[x] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Would it be a big hassle to include ILIKE find operator for case insensitive string query? I see that LIKE is implemented, ILIKE would be a nice touch

[manuhortet]

Performing case insensitive operations right now is pretty counterintuitive, this is a needed feature IMO

[wafs]

If you're using postgres, I've got a solution to have a custom operator:

import { Connection, FindOperator, FindOperatorType } from 'typeorm';

class FindOperatorWithExtras<T> extends FindOperator<T> {
  constructor(
    type: FindOperatorType | 'ilike',
    value: FindOperator<T> | T,
    useParameter?: boolean,
    multipleParameters?: boolean,
  ) {
    // @ts-ignore
    super(type, value, useParameter, multipleParameters);
  }

  public toSql(
    connection: Connection,
    aliasPath: string,
    parameters: string[],
  ): string {
    // @ts-ignore
    if (this._type === 'ilike') {
      return `${aliasPath} ILIKE ${parameters[0]}`;
    }

    return super.toSql(connection, aliasPath, parameters);
  }
}

/**
 * Find Options Operator.
 * Example: { someField: Like("%some sting%") }
 */
export function ILike<T>(
  value: T | FindOperator<T>,
): FindOperatorWithExtras<T> {
  return new FindOperatorWithExtras('ilike', value);
}

I would suggest opening the FindOperator to be extensible by consumers via something like the strategy pattern or a class, so that we don't have to do hacky things like telling TS to ignore private variable usage.

[jacobator]

I used Raw operator with Postgres to achieve ILIKE like this:

import { Raw } from 'typeorm';

const name = 'hello';

const books = await this.booksRepository.find({
  where: {
    name: Raw(alias => `${alias} ILIKE '%${name}%'`),
  },
});

But there's of course SQL Injection problem that comes with this.

[noamgat]

If you're using postgres, I've got a solution to have a custom operator:

import { Connection, FindOperator, FindOperatorType } from 'typeorm';

class FindOperatorWithExtras<T> extends FindOperator<T> {
  constructor(
    type: FindOperatorType | 'ilike',
    value: FindOperator<T> | T,
    useParameter?: boolean,
    multipleParameters?: boolean,
  ) {
    // @ts-ignore
    super(type, value, useParameter, multipleParameters);
  }

  public toSql(
    connection: Connection,
    aliasPath: string,
    parameters: string[],
  ): string {
    // @ts-ignore
    if (this._type === 'ilike') {
      return `${aliasPath} ILIKE ${parameters[0]}`;
    }

    return super.toSql(connection, aliasPath, parameters);
  }
}

/**
 * Find Options Operator.
 * Example: { someField: Like("%some sting%") }
 */
export function ILike<T>(
  value: T | FindOperator<T>,
): FindOperatorWithExtras<T> {
  return new FindOperatorWithExtras('ilike', value);
}

I would suggest opening the FindOperator to be extensible by consumers via something like the strategy pattern or a class, so that we don't have to do hacky things like telling TS to ignore private variable usage.

This looks great. Does it piggyback on all of the sql-injection preventive measures of TypeORM?

[wafs]

This looks great. Does it piggyback on all of the sql-injection preventive measures of TypeORM?

@noamgat I feel like it is since it's using the params array unlike the 'raw' operator which uses the value directly.

typeorm/src/find-options/FindOperator.ts

Lines 87 to 121 in abb2dd3

	toSql(connection: Connection, aliasPath: string, parameters: string[]): string {
	switch (this._type) {
	case "not":
	if (this._value instanceof FindOperator) {
	return `NOT(${this._value.toSql(connection, aliasPath, parameters)})`;
	} else {
	return `${aliasPath} != ${parameters[0]}`;
	}
	case "lessThan":
	return `${aliasPath} < ${parameters[0]}`;
	case "lessThanOrEqual":
	return `${aliasPath} <= ${parameters[0]}`;
	case "moreThan":
	return `${aliasPath} > ${parameters[0]}`;
	case "moreThanOrEqual":
	return `${aliasPath} >= ${parameters[0]}`;
	case "equal":
	return `${aliasPath} = ${parameters[0]}`;
	case "like":
	return `${aliasPath} LIKE ${parameters[0]}`;
	case "between":
	return `${aliasPath} BETWEEN ${parameters[0]} AND ${parameters[1]}`;
	case "in":
	return `${aliasPath} IN (${parameters.join(", ")})`;
	case "any":
	return `${aliasPath} = ANY(${parameters[0]})`;
	case "isNull":
	return `${aliasPath} IS NULL`;
	case "raw":
	if (this.value instanceof Function) {
	return this.value(aliasPath);
	} else {
	return `${aliasPath} = ${this.value}`;
	}
	}

[pleerock]

Oh, we had this feature on next for years... But now there is a PR, so we should have it on master too.

[joeflateau]

@pleerock could that mean we have built in ILIKE in the next release?

[pleerock]

yes, if PR will be finished and merged anytime soon.

[leonardofalk]

I'm also looking for this, but it should fallback for UPPER(alias) LIKE UPPER(value) on drivers that don't support ILIKE operator.

Also, I agree that FindOperator should be public so we have less hacky, more extensible code.

[imnotjames]

This is live now.

[kendallroth]

@imnotjames Just wondering what you meant by "live," as it does not appear to be noted in CHANGELOG notes or any tagged releases?

It is in master but not released as far as I can tell: Compare latest release to master

[leonardofalk]

@kendallroth I think the latest release isn't exactly as master, this happened before.

The problem with the current implementation it only works with PostgreSQL.

My personal take on this:

...
case "ilike":
  if (this.connection.driver instanceof PostgresqlDriver) {
    return `${aliasPath} ILIKE ${parameters[0]}`;
  }

  return `UPPER(${aliasPath}) LIKE UPPER(${parameters[0]})`;

[kendallroth]

@leonardofalk That's what I was understanding as well, was just curious what "is live" meant (unless that referred to just being on master).

The problem with the current implementation it only works with PostgreSQL.

👍 I use PostgresSQL anyway, but does this mean it is why it hasn't been released to master? Or is it simply that there hasn't been a release build since it was merged?

[imnotjames]

You're right - I thought we had done a release but we hadn't. Apologies on that.

[imnotjames]

Also - should you want to get support for drivers / dialects that don't support ILIKE please feel free to open another issue. I'm sure there's a few ways to approach that & we can test each of them. :)

[geisterfurz007]

Hey, possibly stupid question but I am a little lost between next, master and released and couldn't find anything on this in the changelog (searched for "case-insensitive", "case insensitive" and "ilike"). Since the operator suggested above doesn't work in TypeScript anymore (Property 'toSql' does not exist on type 'FindOperator<T>'.), I am curious to know about the current status.

I also couldn't find a PR that contained more detail in this thread nor does this seem to be in the docs anywhere yet. Is there an ETA for this?

[wazcov]

can someone specify the version this is fixed in?

[noamgat]

Hey, possibly stupid question but I am a little lost between next, master and released and couldn't find anything on this in the changelog (searched for "case-insensitive", "case insensitive" and "ilike"). Since the operator suggested above doesn't work in TypeScript anymore (Property 'toSql' does not exist on type 'FindOperator<T>'.), I am curious to know about the current status.

I also couldn't find a PR that contained more detail in this thread nor does this seem to be in the docs anywhere yet. Is there an ETA for this?

I think you can now do the following instead of using the code in this issue:

return this.userRepository.findOne({ email: new FindOperator('ilike', userEmail) });

[metelski]

This answers @geisterfurz007 's question and fixes my issue as well.

TL;DR Custom case-insensitive like operator proposed by @wafs no longer works after updating TypeORM to 0.2.29. Please see above comment to have this fixed. Thanks @noamgat!

[geisterfurz007]

Does indeed; thanks for the mention @metelski!

[IlCallo]

Seems like MySQL string comparisons are case-insensitive by default. For that driver the 'like' operand is fine

[renatogbp]

return this.userRepository.findOne({ email: new FindOperator('ilike', userEmail) });

this can be further simplified as:

return this.userRepository.findOne({ email: ILike(userEmail) });

For searching it's useful to do something like:

return this.userRepository.findOne({ email: ILike(`%${query.keyword}%`) });

[adsee42]

This is a great update. But how could I use it in query builder?

I have a searchUser function searches user with given keyword

await User.createQueryBuilder("user")
      .where(
        !!keyword
          ? `(user.name ILIKE '%${keyword}%' OR user.email ILIKE '%${keyword}%'`
          : "1=1"
      )
      .andWhere(...)
      .limit(limit)
      .offset(offset)
      .getManyAndCount();

If there's quote ' in the keyword, the function returns error. With FindOperator('ilike', name), quotes seem to be escaped.

How can I re-write above query with FindOperator?

[nebkat]

Does the ILike function work? @adsee42

[adsee42]

@nebkat
(user.name ILIKE '%${keyword}%' OR user.email ILIKE '%${keyword}%' works,
but I don't know how to use it as FindOperator('ilike', name). I didn't found it documented anywhere.

[nebkat]

repository.find({
    name: ILike("John%")
});

Just import ILike from typeorm.

[0xC0DEBA5E]

Is it safe to use user input as input to the ILike Operator?

Would something like this

repository.find({
    name: ILike(`%${unsafeUserInput}%`)
});

be vulnerable to sql injection attacks?

Update:
With logging enabled I was able to see that in the generated sql the parameter was handed in separately to the query. So this should be safe to use.

[LouisMazel]

If you're using postgres, I've got a solution to have a custom operator:

import { Connection, FindOperator, FindOperatorType } from 'typeorm';

class FindOperatorWithExtras<T> extends FindOperator<T> {
  constructor(
    type: FindOperatorType | 'ilike',
    value: FindOperator<T> | T,
    useParameter?: boolean,
    multipleParameters?: boolean,
  ) {
    // @ts-ignore
    super(type, value, useParameter, multipleParameters);
  }

  public toSql(
    connection: Connection,
    aliasPath: string,
    parameters: string[],
  ): string {
    // @ts-ignore
    if (this._type === 'ilike') {
      return `${aliasPath} ILIKE ${parameters[0]}`;
    }

    return super.toSql(connection, aliasPath, parameters);
  }
}

/**
 * Find Options Operator.
 * Example: { someField: Like("%some sting%") }
 */
export function ILike<T>(
  value: T | FindOperator<T>,
): FindOperatorWithExtras<T> {
  return new FindOperatorWithExtras('ilike', value);
}

I would suggest opening the FindOperator to be extensible by consumers via something like the strategy pattern or a class, so that we don't have to do hacky things like telling TS to ignore private variable usage.

Hi,

This solution to create a custom operator seems to no longer work with the v0.3
In detail, the class function toSql is never called.

I want to create another kind of operator. Do you have any solution?