Make filter in back-end not case sensitive ?

[MikaelNazarenko]

Hope the question is clear. How to make filter in back-end not case sensitive ? Because now, for example, if I type last name in customers page with upper case it will not find the row with lower case

[aimeos]

If you use MySQL, you can still use "utf8mb4_unicode_ci" as collation to get a case-insensitive search. More details are explained here: https://github.com/aimeos/aimeos-laravel#database

[IvanIgniter]

Can I have a follow-up question regarding this issue?
We are using a mysql 'collation' => 'utf8mb4_unicode_ci' and still it only find result in case sensitive.
We have already release to production about a year ago using this collation. Is there any other way we search using case insensitive filter?

I am using laravel 6.x, PHP 7.4, Docker desktop/Ubuntu and the Aimeos 2021 (aimeos-laravel: 2021.10.4 / aimeos-core: 2021.10.13)

[aimeos]

Make sure that the collation is really used for the columns you want to make case insensitive, e.g. the mshop_product.label column and check e.g. with "SHOW CREATE TABLE mshop_product". There is not other way.

[IvanIgniter]

This is the table schema. Please let me know how to search the mshop_product.label a case insensitive?

CREATE TABLE `mshop_product` (
`id` int NOT NULL AUTO_INCREMENT,
`siteid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`dataset` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`type` varbinary(64) NOT NULL,
`code` varbinary(64) NOT NULL,
`label` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`config` text COLLATE utf8_unicode_ci NOT NULL,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
`scale` double NOT NULL DEFAULT '1',
`rating` decimal(4,2) NOT NULL DEFAULT '0.00',
`ratings` int NOT NULL DEFAULT '0',
`instock` smallint NOT NULL DEFAULT '0',
`status` smallint NOT NULL,
`mtime` datetime NOT NULL,
`ctime` datetime NOT NULL,
`editor` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`target` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`barcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`standard1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`standard2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`delivery_slip_display` int DEFAULT '0',
`inventory_alert_qty` int DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_mspro_siteid_code` (`siteid`,`code`),
KEY `idx_mspro_id_sid_stat_st_end_rt` (`id`,`siteid`,`status`,`start`,`end`,`rating`),
KEY `idx_mspro_sid_stat_st_end_rt` (`siteid`,`status`,`start`,`end`,`rating`),
KEY `idx_mspro_sid_rating` (`siteid`,`rating`),
KEY `idx_mspro_sid_label` (`siteid`,`label`),
KEY `idx_mspro_sid_start` (`siteid`,`start`),
KEY `idx_mspro_sid_end` (`siteid`,`end`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci

[aimeos]

Maybe the collation needs to be "utf8mb3_unicode_ci" to fit for "utf8mb3" character set.

[IvanIgniter]

I have altered the table to be
ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
but still give me a case sensitive result. I can't see items that are capitalize if I input small caps vice versa.
Kindly help this issue?

[aimeos]

Does this also apply to the label column?

[IvanIgniter]

This is again the table schema to make sure I didn't miss something?

CREATE TABLE `mshop_product` (
`id` int NOT NULL AUTO_INCREMENT,
`siteid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`dataset` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`type` varbinary(64) NOT NULL,
`code` varbinary(64) NOT NULL,
`label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`config` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
`scale` double NOT NULL DEFAULT '1',
`rating` decimal(4,2) NOT NULL DEFAULT '0.00',
`ratings` int NOT NULL DEFAULT '0',
`instock` smallint NOT NULL DEFAULT '0',
`status` smallint NOT NULL,
`mtime` datetime NOT NULL,
`ctime` datetime NOT NULL,
`editor` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`target` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`barcode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`standard1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`standard2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`delivery_slip_display` int DEFAULT '0',
`inventory_alert_qty` int DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_mspro_siteid_code` (`siteid`,`code`),
KEY `idx_mspro_id_sid_stat_st_end_rt` (`id`,`siteid`,`status`,`start`,`end`,`rating`),
KEY `idx_mspro_sid_stat_st_end_rt` (`siteid`,`status`,`start`,`end`,`rating`),
KEY `idx_mspro_sid_rating` (`siteid`,`rating`),
KEY `idx_mspro_sid_label` (`siteid`,`label`),
KEY `idx_mspro_sid_start` (`siteid`,`start`),
KEY `idx_mspro_sid_end` (`siteid`,`end`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

[aimeos]

Everything seems fine. Does it work if you use the MySQL client and execute a SELECT statement directly?