Case Insensitive search (again)

[DiegoMax]

Hello there @ypnos-web , i have been working with this code for a while, trying to implement case insensitive search in an elegant way. I think i have found it (finally).

private function _addCondition($column, $value, $type = 'and')
  {
    $right = $this->config('search.prefix') ? "{$value}%" : "%{$value}%";
    if ($this->config('search.ignoreCase')) {
      $query = TableRegistry::get($this->_tableName)->find(); // Get table reference
      $lower = $query->newExpr()->add("CONCAT(LOWER('{$right}'))");
      $condition = ["LOWER(CONCAT({$column})) LIKE" => $lower];
    } else {
      $condition = ["{$column} LIKE" => $right];
    }
    if ($type === 'or') {
      $this->config('conditionsOr', $condition); // merges
      return;
    }

    list($association, $field) = explode('.', $column);
    if ($this->_tableName == $association) {
      $this->config('conditionsAnd', $condition); // merges
    } else {
      $this->config('matching', [$association => $condition]); // merges
    }
  }

I think its elegant because its pure SQL and should work with all DB engines, as a plus, CONCAT() implies a cast to string, so it will not explode when searching on non string/varchar fields.
I have only tested this on PostgreSQL, but i think it should work in other engines too.

Let me know what you think, and i might send you a PR if you are interested, or you can just copy paste from here.

Thanks!

// Diego

[ypnos-web]

Hello Diego,

thanks for your thoughts on this. I think it is a great solution. However please have a look at the devel branch where we progressed a bit, especially with the option to set a custom operator. Have a look:

cakephp-datatables/src/Controller/Component/DataTablesComponent.php

Lines 233 to 238 in 0db0e58

	$comparison = trim($this->_getComparison($table, $column));
	// wrap value for LIKE and NOT LIKE
	if (strpos(strtolower($comparison), 'like') !== false) {
	$value = $this->config('prefixSearch') ? "{$value}%" : "%{$value}%";
	}
	$condition = ["{$column} {$comparison}" => $value];

What you see there is a special case for 'like' comparisons. I think this is where your addition of ignoreCase would fit best.

The problem with CONCAT() is that it makes comparisons of non-string types in the database very inefficient (indexes become unusable etc.), so I believe it is best to introduce this functionality only for LIKE-type comparisons. Or we could think of making case-insensitivity a per-column option, as is done with the custom operator. What do you think?

[DiegoMax]

I will take a look, and yeah im aware of the performance penalty when casting in that way, but at least in my case it was much cleaner than having to add a lot of conditions to do different things depending on the field type.
I will take a look and i will see if i can somehow improve what you have already (or maybe just use what you did) ;)
Thanks!