Case-insensitive getBy

[zener05]

Hi, I use PostgreSQL as a DB engine and I have a problem with case-sensitive columns.

For example I would like to have User::$email as case-insensitive. Is possible to set case-sensitivity for one specified column/attribute? It would be awesome to set something like:
@property string $email {case-insensitive}

Then all methods like findBy, getBy... would use ILIKE instead of =.

Or is there any easy way how to do it? Thank you.

[hrach]

The use-case seems pretty reasonable to support directly in Orm.

This is possible to do yourself. There are few differences between Orm 3.1 & 4 (master) - but they are mainly just in naming of the supporting classes.

• create extension for metadata parser
  • create own MetadataParserFactory and override DI definition (see OrmExtension)
  • modify Orm's metadata parser to parse this new modifier and add its info to args of PropertyMetadata.
• create own version of ValueOperatorFunction (CompareFunction in Orm 4)
  • inherit and override behavior when the meta contains info about case-insensitive
  • override it for both - array & dbal
  • register your version of in your BaseRepository in createCollectionFunction()

[hrach]

Postponing to 4.1. But in 4.0 it should be really possible on your own.

[mabar]

I did a small research on case sensitivity, accents and text normalization and this was my best in-database (postgresql) solution:

CREATE COLLATION ori_cmf.ignore_case_and_accents (
	provider = "icu",
	locale = "und@colStrength=primary",
	deterministic = false
);

CREATE TABLE ori_cmf.emails
(
	"email_address" varchar(254) COLLATE ori_cmf.ignore_case_and_accents NOT NULL CHECK ("email_address" IS NORMALIZED)
);

Strings are considered equal even if:

• different case is used ([email protected] vs [email protected])
  • according to standard part before @ should be treated differently than after, but who cares
• accents are used (fóó@bař.baž vs [email protected])
• characters looking like letters are used
  • it's the IS NORMALIZED part, check NFC normal form and unicode equivalence topics
  • e.g. this may be visually the same, depending on used font - https://3v4l.org/sLIW9

For different use cases, I have these collations:

CREATE COLLATION ori_cmf.strict (
	provider = "icu",
	locale = "und-x-icu",
	deterministic = true
	);

CREATE COLLATION ori_cmf.ignore_case (
	provider = "icu",
	locale = "und-u-ks-level2",
	deterministic = false
	);

CREATE COLLATION ori_cmf.ignore_accents (
	provider = "icu",
	locale = "und@colStrength=primary;colCaseLevel=yes",
	deterministic = false
	);

CREATE COLLATION ori_cmf.ignore_case_and_accents (
	provider = "icu",
	locale = "und@colStrength=primary",
	deterministic = false
	);

Of course it would be nice to have equal functions in orm, to be compatible with array mapper, but this may be good enough for most :)