Make filter in back-end not case sensitive ?

Help for integrating the Laravel package
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
MikaelNazarenko
Expert
Posts: 274
Joined: 27 Jun 2019, 16:19

Make filter in back-end not case sensitive ?

Post by MikaelNazarenko » 07 Dec 2019, 12:36

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

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Make filter in back-end not case sensitive ?

Post by aimeos » 08 Dec 2019, 10:07

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
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
IvanIgniter
Posts: 58
Joined: 01 Dec 2021, 07:41

Re: Make filter in back-end not case sensitive ?

Post by IvanIgniter » 18 Aug 2022, 07:01

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)

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Make filter in back-end not case sensitive ?

Post by aimeos » 19 Aug 2022, 05:54

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.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
IvanIgniter
Posts: 58
Joined: 01 Dec 2021, 07:41

Re: Make filter in back-end not case sensitive ?

Post by IvanIgniter » 23 Aug 2022, 08:52

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

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Make filter in back-end not case sensitive ?

Post by aimeos » 24 Aug 2022, 07:48

Maybe the collation needs to be "utf8mb3_unicode_ci" to fit for "utf8mb3" character set.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
IvanIgniter
Posts: 58
Joined: 01 Dec 2021, 07:41

Re: Make filter in back-end not case sensitive ?

Post by IvanIgniter » 26 Aug 2022, 04:44

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?

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Make filter in back-end not case sensitive ?

Post by aimeos » 27 Aug 2022, 05:57

Does this also apply to the label column?
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
IvanIgniter
Posts: 58
Joined: 01 Dec 2021, 07:41

Re: Make filter in back-end not case sensitive ?

Post by IvanIgniter » 01 Sep 2022, 03:09

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

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Make filter in back-end not case sensitive ?

Post by aimeos » 02 Sep 2022, 10:05

Everything seems fine. Does it work if you use the MySQL client and execute a SELECT statement directly?
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply