Combine fields filter case-sensitive search

The search is case insensitive for me. Are you using it on a field of a special type? I tested it on text and text_long.
Tested with Views 7.x-3.x-dev

Thanks

Patch works excellent, also works well for me - regardless of caps or not.

I would need more info to look more into it. I've assigned the bug to myself and changed the status.
@srgk Even if it is not exactly related to my patch, I have time to look into it.

I am having the same issue using Views 7.x-3.5+17-dev and Drupal 7.15 with MySQL 5.5.21.

It doesn't seem to matter what type of field I'm using the combined filter on. It's always case sensitive no matter what I try.

Well that's how d7 is working, also the normal string filter, like for content: title is simply case-sensitive,
so i don't really see the bug here, it's more like a feature request which would require tons of workarounds.

I still want to look into it. Im surprised it's not case sensitive for me but it is for you guys. I'll test it and confirm the bug tonight.

Thanks

dawehner: I tried making a view with an exposed filter on "Content: Title" and in my tests the filter was not case sensitive. I think it can depend on the database collation and type of database field though:

"The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation." - http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

I have the same problem.
My exposed field searches in node title, node body and few taxonomy term reference fields, but the search is only case-sensitive.

srgk: For me the issue was that one of the fields I was filtering on had the collation utf8_bin.

It seems that if one or more fields that you are filtering on is or gets converted to a binary character set the entire filter becomes case sensitive because you then compare bytes instead of strings. So I changed the field from utf8_bin to utf8_general_ci and then my filter became case-insensitive.

Sergei Golubchik explains it well in his answer in this bug report: http://bugs.mysql.com/bug.php?id=22343

Same problem when using a term reference field as one of the filtered fields. I switched it instead to "Taxonomy term: Name", available via a relationship, and that solved the problem.

i'm having the same issue, but with results supplied by module Data (retrives fields from other SQL schemas)

It is also case sensitive for me. Is there a way to change this? Thanks

any movement on this issue?

Same problem. I search in titles, and some text and long-text field, and are sensitive. Suscribe!

This is not a great solution, but it works.
I created a Rule that populates another field on my content type with the names of referenced taxonomy terms when saving the node. Then I expose this field in views filters, rather than try to expose the term name directly in views, which results in duplicate results and this issue with case-sensitivity.

Not a fix, but a workaround that has allowed me to move on.

@dshields @SamuelSolis Could you provide more information on your database? It is currently case insensitive for me so it's pretty hard for me to debug.

Here is some information I would need

If it's a basic install (I'm hoping this is the case) : Operating System + Version

If not :
- DB type (mysql I guess?)
- Database collation and character set

use mysuperDatabase
show variables like "character_set_database";
show variables like "collation_database";

If nothing shows up after this, we will escalate to the table level and see PHP mysql client collation info.

It becomes case sensitive when I add a taxonomy term reference to the group of filters.
If I add a relationship to the term and then add the name of the term to the list of filters, the case sensitivity goes away, but I end up with duplicate results when there are multiple terms selected.

@Pierre Paul Lefebvre

It's a basic install.

This is de SO version:
Linux version 2.6.32-5-amd64 (Debian 2.6.32-48squeeze1) (dannf@debian.org) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Mon Feb 25 00:26:11 UTC 2013

MySQL:
Servidor: Localhost via UNIX socket
Versión del servidor: 5.1.66-0+squeeze1
Versión del protocolo: 10
Juegos de caracteres de MySQL: UTF-8 Unicode (utf8)

Web Server:
Apache/2.2.16 (Debian)
Versión del cliente: 5.1.66
extensión PHP: mysqli

Do you miss anything?

I have the same issue as in #19
The keywords filter becomes case sensitive when I add a taxonomy term reference to the group of filters.

I'm also experiencing this issue, but I don't have a Term in my relationships or filters. I do, however, have a file in my relationships.

Anyone have a fix?

Hey, I've written a little snippet that takes care of this issue for me, this applies to all combine filters site wide to make them case insensitive. It's probably not very clean, but it's something

function hook_views_query_alter(&$view, &$query) {
  // Find all combine fields and make them case insensitive.
  foreach ($query->where as $group_key => $group) {
    foreach ($group['conditions'] as $key => $condition) {
      if(preg_match('/:views_combine/', $condition['field'])) {
        $query->where[$group_key]['conditions'][$key]['field'] = $condition['field'] . ' COLLATE utf8_general_ci';
      }
    }
  }
}

The problem still exists for me too. Combine filter search in text fileds is case-sensitive for all language versions of the site (english too). Is it a way to solve the problem?

I've got this problem, too. I took out all but a simple text field from the combined filter and it's still returning as case-sensitive. I added the hook from #24 and nothing changed. This is really frustrating.

The snippet in comment #24 works perfectly for me, thnx penthehuman.

One rework on this snippet to make it work in combination with dropdown and combine filters:

function hook_views_query_alter(&$view, &$query) {
  // Find all combine fields and make them case insensitive.
  foreach ($query->where as $group_key => $group) {
    foreach ($group['conditions'] as $key => $condition) {
      if (is_string($condition['field']) && (preg_match('/:views_combine/', $condition['field']))) {
        $query->where[$group_key]['conditions'][$key]['field'] = $condition['field'] . ' COLLATE utf8_general_ci';
      }
    }
  }
}

I am in the same place as cadoughe. I added the hook from #24 and tried #27 and still no luck. Does ANYONE know of another way or what I may be doing wrong? I want my combined filters to be case insensative

The case-(in)sensitive search with the LIKE operator depends on database type and collation.

Not all collations are available on all systems so, I think, a patch involving collation is not appropriate.

LIKE operator are case insensitive by default on MySQL and case sensitive in PostgreSQL and sqlite. This is solved in includes/database/pgsql/database.inc changing LIKE by ILIKE. Some kind of database detection (like in a normal views filter) would be the best to fix this issue. Anyway, the easiest and database compatible solution is transform fields and conditions to lower-case with the lower() function present in MySQL, PostgreSQL and sqlite. The problem is that lower() can't use indexes, making the search a little bit slower.

See attached patch.

Thanks for the hints on this! I had included a date-field in the combined field and that also caused the case-sensitive behavior. Sites bug fixed :D

#24 and #27 both work like a charm, thank you for sharing this insights!

Hello All,

I also had the same issue with one of my external data table on my Drupal 7 site. On the contrary I noticed other three tables I used previously on the same site do not show any issues. Hence I closely studied how this time it happened, I found, a field I used as "int" added to the combine filter. I removed the field from the selection and found search result shows case in-sensitive. Then I changed the field type into varchar and tested again. There I got the result I wanted - case in-sensitive search result.

Though solution #24 and #27 work, I found the exact problem is "collate" property of the field. If you included a field without collated, mysql will output case sensitive result for "%LIKE%" combine search.

I hope this is the right solution / fix for this issue.

I had a few Profile2 fields including a term reference with the Autocomplete widget and I was trying to search that with an exposed combined fields filter.

I managed to work around the problem by creating a relationship with my vocabulary and using the taxonomy term instead as a filter.

#24, #27 & #29 did not work for me.

Rerolled new patch to work with drush make.

I had the same issue, here is what I did to fix it. If you try the patcher they add LOWER or UPPER to the search term which can work depending on the existing content on the database. What I did is add COLLATE utf8_general_ci to the expression which would do a case-insensitive search. So searching for CAR would returning anything with Car, CAR, cAr, and so on. I'm sure there is a better way of doing this but I just edited the "op_contains" function in "/handlers/views_handler_filter_combine.inc" I should mention that I'm using the CONTAINS operator on my View.

  function op_contains($field) {
    $placeholder = $this->placeholder();
     //this is the old expression
    //$this->query->add_where_expression($this->options['group'], "$field LIKE $placeholder", array($placeholder => '%' . db_like($this->value) . '%'));
    //this is the new expression
    $this->query->add_where_expression($this->options['group'], "$field LIKE  $placeholder COLLATE utf8_general_ci", array($placeholder => '%' . db_like($this->value) . '%'));
  }

Hi guys!

I tried to check the encoding of the database tables and all that is offered above.
But the only thing that helped - it's use LOWER in the operator Contains (patch from 34).

The problem is still not solved, because I use the operator 'Contains any word' instead of the operator 'Contains'.
I tried to modify the code myself. But my knowledge of php are not good.

Can somebody help to solve this problem if using LOWER on 'Contains any word' operator?

function op_word($field) {
    $where = $this->operator == 'word' ? db_or() : db_and();

    // Don't filter on empty strings.
    if (empty($this->value)) {
      return;
    }

    preg_match_all('/ (-?)("[^"]+"|[^" ]+)/i', ' ' . $this->value, $matches, PREG_SET_ORDER);
    foreach ($matches as $match) {
      $phrase = FALSE;
      // Strip off phrase quotes.
      if ($match[2]{0} == '"') {
        $match[2] = substr($match[2], 1, -1);
        $phrase = TRUE;
      }
      $words = trim($match[2], ',?!();:-');
      $words = $phrase ? array($words) : preg_split('/ /', $words, -1, PREG_SPLIT_NO_EMPTY);
      $placeholder = $this->placeholder();
      foreach ($words as $word) {
        $where->where($field . " LIKE $placeholder", array($placeholder => '%' . db_like(trim($word, " ,!?")) . '%'));
//$where->where(LOWER $field . " LIKE LOWER $placeholder", array($placeholder => '%' . db_like(trim($word, " ,!?")) . '%'));      
}
    }

    if (!$where) {
      return;
    }

    // Previously this was a call_user_func_array() but that's unnecessary
    // as views will unpack an array that is a single arg.
    $this->query->add_where($this->options['group'], $where);
  }

Thanks!

I could be wrong here but in "views/handlers/views_handler_filter_combine.inc" on line 75, the following *appears* to fix this issue for me:

-$expression = "CONCAT_WS(' ', $expression)";
+$expression = "CONVERT(CONCAT_WS(' ', $expression) USING utf8)";

Could someone chime in on whether this is advisable or not? I'm not familiar enough with MySQL encoding standards to know if this is a good solution or a terrible misunderstanding. (What I do know is that it *appears* to fix the issue in both my development and production environments that use MySQL 5.5x and 5.1. Beforehand, the 5.1 environment needed case to be specified in the combine field for the results to come back but in the 5.5, it worked out-of-the-box.)

Insight is appreciated.

Yeahhhhhhhhh!

Thanks, Wolf_22

#37 works for me
filter is case insensitive

#37 Works for me.

Thank Wolf_22

My turn to thanks Wolf_22 for #37 (using D7.36 & Views 7.x-3.10)

#37 works great and yes, if someone has feedback for us, if it's OK across databases let's create a patch and get this RTBC as soon as possible :)

Any feedback?

Same here #37 fixed it. Thank you!

#37 worked perfectly for me as well! It would be ideal for this to be an option when configuring the "Combine fields" filter, but this is great!

#37 worked for my site. Shouldn't this be implemented directly on the Views module??

Hi,
thanks, I'm using #37 on a production site and will report back with the results after some testing.

I tried #37 but this caused all searches on the combined field to return no results. Solve #27 appears to be working in staging, but I'll know more after QA gets done with it.

#37 doesn't work with postgres (9.4.5):

"SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "USING" LINE 11: ...table.field) USING utf8... ^

Hi!
Does someone know whether this issue has a corresponding Drupal 8 issue to fix this for now and forever?

Solution for postgres users: in /views/handlers/views_handler_filter_combine.inc

Views 7.x-3.14
PSQL 9.3.10

Line 122

        - $where->where($field . " LIKE $placeholder", array($placeholder => '%' . db_like(trim($word, " ,!?")) . '%'));
        + $where->where("LOWER({$field}) LIKE LOWER({$placeholder})", array($placeholder => '%' . db_like(trim($word, " ,!?")) . '%'));

#51 worked for me using PSQL, though also had to edit the $field and $placeholder in function op_contains()

Can someone patch the change for #51? Otherwise it will not get commited.

@dawehner for D8 that would probably be #2784739: Fix PostgreSQL operator in views and #2664530: Views Combined fields filter with "Contains any word" or "Contains all words" operator results in an incorrect SQL query and a fatal error

Had the same issue. #37 solved the problem for me.
Created a patch.

#56 does not fix the Postgre SQL error

The latest 2 year old patch in #56 does not fix the Postgre SQL error mentioned in #51 and #52 so setting back to Needs work.

Anyone Tell please =), and whether it is possible to use the lowering or raising of the register for filter type: Contains, Contains Any Word, Contains All Words
Like in #51 patch

It looks like either:
* We test what the current DB is and set up the solution appropriate to each
* Or try something that might be DB agnostic like $expression = "CAST(CONCAT_WS(' ', $expression) AS CHAR)";