[2011-03-22] Case insensitive search in PartialMatchFilter etc

[silverstripe-issues]

created by: @patbolo (jbarrois) assigned to: @chillu (ischommer) created at: 2011-03-22 original ticket: http://open.silverstripe.org/ticket/6548

---

When using a different database than mysql (ie postgres), searching for an asset in "Files and images" only returns results if the keyword matches filenames in a case sensitive way (ie "Stack" would return Stack.pdf, but not "stack"). The current code uses LIKE but the LIKE operator is case sensitive in Postgres (the insensitive equivalent being ILIKE).

I propose a fix based on the LOWER() function that works seamlessly across database connectors.

[silverstripe-issues]

comment by: @chillu (ischommer) created at: 2012-03-14

---

Still a problem in master, although it shifted from AssetTableField to PartialMatchFilter/StartsWithFilter/EndsWithFilter. Its fixing an overly strict PGSQL default setting, but ruins performance for everybody else: As its string manipulation, no indexes are used.

There's a discussion as long as the bible on the very same problem in Drupal: http://drupal.org/node/83738

I haven't followed it all the way through to see what conclusion they came up with, but think that ILIKE would be a better solution - see https://github.com/chillu/sapphire/tree/trac/6548.

Haven't sent pull request yet as I can't test Postgres under 3.0 just yet.

[silverstripe-issues]

comment by: hfried created at: 2012-07-03

---

Ingo, now that 3.0 is out, do you have time to take another look at this?

[silverstripe-issues]

comment by: @chillu (ischommer) created at: 2012-07-04

---

Yep, good find!

[silverstripe-issues]

comment by: hfried created at: 2012-07-25

---

Moving to 3.0.2

[silverstripe-issues]

comment by: @halkyon (sharvey) created at: 2012-08-24

---

We had this problem in a recent project, all instances of LIKE were replaced with ILIKE across the framework, not just for search filters...

[silverstripe-issues]

comment by: @chillu (ischommer) created at: 2012-09-03

---

Fixed the original issue with http://github.com/silverstripe/sapphire/commit/2facc31e1f82bf9c6146844157425e26eb9f9579.

But as Sean mentions, there's a lot more LIKE uses in core (roughly two dozen). Most importantly it will still do case sensitive searches for the "CMS filter" panel. Simply replacing LIKE with ILIKE won't work for core, since that's a postgres specific syntax. More details: http://stackoverflow.com/questions/203399/how-do-you-write-a-case-insensitive-query-for-both-mysql-and-postgres

Options:

1. Apply conditional with LIKE/ILIKE querying as per the commit above everywhere
2. Experiment further with a unified and performant LOWER() syntax

We could also create SQL getter in database driver, akin to Database->datetimeDifferenceClause() (with either of the above solutions). The ILIKE solution sounds more straightforward to me, and getter means less boilerplate.

// before
$sql = 'SELECT * FROM "Table" WHERE "Field" LIKE '%foo%'";
// option 1 (PHP 5.1+)
$like = (DB::getConn() instanceof PostgreSQLDatabase) ? 'ILIKE' : 'LIKE';
$sql = 'SELECT * FROM "Table" WHERE "Field" ' . $like . ' '%foo%'";
// option 2
$sql = 'SELECT * FROM "Table" WHERE ' . DB::getConn()->likeClause("Field", '%foo%');

On a related note, should we have a DataList->like() to make it less likely that you need to use any of the above solutions directly?

[wilr]

Fixed in e6e47cb