Search code examples
sqlnode.jspostgresqlnestjsmikro-orm

Filtering with Like operator on integer column

I'm using mikro-orm for db related opeartions. My db entity has a number field:

@Property({ defaultRaw: 'srNumber', type: 'number' })
 srNumber!: number;

and corresponding db column (Postgresql) is:

srNumber(int8)

The query input for where param in mikro-orm EntityRepository's findAndCount(where, option) is:

repository.findAndCount({"srNumber":{"$like":"%1000%"}}, options)

It translates to:

select * from table1 where srNumber like '%1000%'

The problem here is since srNumber column is not a string, there is a type-mismatch and query fails. Casting it like CAST(srNumber AS TEXT) like '%1000%' should work in db.

Is there any way to somehow specify the field casting here?

Solution

  • You can use custom SQL fragments in the query. To get around strictly typed FilterQuery, you can use expr which is just an identity function (returns its parameter), so have effect only for TS checks.

    Something like this should work:

    import { expr } from '@mikro-orm/core';

const res = await repo.findAndCount({
  [expr('cast(srNumber as text)')]: { $like: '%1000%' },
}, options);

    https://mikro-orm.io/docs/entity-manager/#using-custom-sql-fragments