Regex flags does not work in where conditions

[daenash]

Describe the bug
The regex flags in where conditions does not work.

Expected behavior
I wanted to filter on the name of a client entity. The Client entity looks like this

import { Entity, PrimaryKey, Property } from "@mikro-orm/core";

@Entity({ tableName: "clients" })
export class Client {
  @PrimaryKey({ autoincrement: true, type: "numeric" })
  id!: number;

  @Property({ type: "varchar" })
  name!: string;
}

In the database you can find this record (returned when calling a findAll() without any further options)

const clientRepository = db.orm.em.getRepository(Client);
const regexTestResponse = await clientRepository.findAll();

// returns: 
// 
// [
//   Client {
//     id: '1',
//     name: 'Corkery and Sons',
//     projects: Collection<Project> { initialized: false, dirty: false }
//   }
// ]

However

When I wanted to filter on the name with regex using the incasesensitive (i) flag it returns nothing.

const clientRepository = db.orm.em.getRepository(Client);
const regexTestResponse = await clientRepository.find({
   name: new RegExp("corkery and sons", "i"),
});

// returns: 
//
// []

Versions

Dependency	Version
node	v16.14.2
typescript	4.8.4
mikro-orm	5.4.2
your-driver	postgresql

[B4nan]

You could work this around by prefixing the regex with (?i) probably:

const regexTestResponse = await clientRepository.find({
   name: { $re: "(?i)corkery and sons" },
});

https://stackoverflow.com/a/70575793/3665878

[daenash]

@B4nan

Thank you for the quick response! Yep it works perfectly this way. 🥳

I guess the regex handling works differently for each driver that is why it's not working correctly here, right? I also noticed that I can add a flags property if I use $re in the where condition but it also throws an error.

E.g.

const regexTestResponse = await clientRepository.find({
   name: { $re: "corkery and sons", flags: 'i' },
});

> DriverException: Invalid query condition: { name: { flags: 'i' } }