Filtering with Like operator on integer column

user_2303778 0
Viewed 571

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?

mikro-orm nestjs node.js postgresql sql
1 Answers

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

user_36658780
Related