Select with function in where condition

[twavv]

Is your feature request related to a problem? Please describe.
I'd like some way to query using a function as part of the where condition. Concretely, I have a unique index on UPPER(payload) for some table where the payload is case-insensitive, and I want to query using WHERE UPPER(payload) = UPPER($1) (obviously, the UPPER($1) can be done in the client side, so that's less of an issue here).

Describe the solution you'd like
Unclear what the API would be.

Describe alternatives you've considered
There is a querybuilder way of doing this, but it's not what I'm looking for here.

mikro-orm/tests/QueryBuilder.test.ts

Lines 307 to 311 in bfbc5f8

	test('select with custom expression', async () => {
	const qb1 = orm.em.createQueryBuilder(Book2);
	qb1.select('*').where({ 'json_contains(`e0`.`meta`, ?)': [{ foo: 'bar' }] });
	expect(qb1.getQuery()).toEqual('select `e0`.*, `e0`.price * 1.19 as `price_taxed` from `book2` as `e0` where json_contains(`e0`.`meta`, ?)');
	expect(qb1.getParams()).toEqual(['{"foo":"bar"}']);

Additional context
Add any other context about the feature request here.

[B4nan]

You can do the same with EM api, you just need to type cast as this dynamic key won't pass the strict FilterQuery type.

em.find(Book, { 'json_contains(`e0`.`meta`, ?)': [{ foo: 'bar' }] } as any);

[B4nan]

We could also have something like expr helper, that could allow doing this I guess:

const author = new Author2('name', 'email');
const b1 = new Book2('b1', author);
const b2 = new Book2('b2', author);
const b3 = new Book2('b3', author);
await orm.em.persistAndFlush([b1, b2, b3]);
orm.em.clear();

const expr: <T> (sql: string) => keyof T = sql => sql as any;
const books = await orm.em.find(Book2, { [expr('upper(title)')]: ['B1', 'B2'] }); // no TS errors
expect(books).toHaveLength(2);

(this test is passing with current version, so you can define such expr helper yourself right now)

WDYT?

edit: its even enough to return a string, so this works too:

const expr = (sql: string) => sql;
const books = await orm.em.find(Book2, { [expr('upper(title)')]: ['B1', 'B2'] }); // no TS errors

[B4nan]

In rc.6 it will be possible to do this:

const books = await em.find(Book, {
  [expr('upper(title)')]: em.getKnex().raw('upper(?)', ['b2']),
});

[danitrod]

I was facing this and managed to work around by creating a RegExp to query and match the case insensitive value. Don't know if it helps for your case, but here it is:

const caseInsensitive = (str: String): RegExp => {
  const len = str.length;
  let exp = '^';
  for (let i = 0; i < len; i++) {
    exp += '[' + str[i].toLowerCase() + str[i].toUpperCase() + ']';
  }
  exp += '$';
  return new RegExp(exp);
};

const book = await em.findOne(Book, { title: caseInsensitive(title) });

[KevinNovak]

@danitrod If you want to find a case insensitive value you can just use a RegExp with the 'i' flag like so:

const book = await em.findOne(Book, {
    title: new RegExp(`^${title}$`, 'i'),
});

Also make sure to escape any regex characters, especially if that title string is coming from the user:

title = title.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, '\\$&');

Hope this helps someone in the future!