Hello!

I've configured combine exposed filter to search using "contains" or "contains any word" operator, using patch from here http://drupal.org/node/1782678 , and the search is only case-sensitive

i think it's logical for this filter to be case-insensitive
could someone please help me obtain such functionality

thanks in advance

CommentFileSizeAuthor
#56 views-case_insensitive_search-1805272-56.patch607 bytesStijnStroobants
#35 views_handler_filter_combine.inc_.zip2.35 KBedgar971
#34 views-case_insensitive_search-1805272-34.patch1.09 KBnielsonm
None View
#29 views-case_insensitive_search-1805272-29.patch861 bytesazuledu
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-case_insensitive_search-1805272-29.patch. Unable to apply patch. See the log in the details link for more information. View
Members fund testing for the Drupal project. Drupal Association Learn more

Comments

Pierre Paul Lefebvre’s picture

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

IWasBornToWin’s picture

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

Pierre Paul Lefebvre’s picture

Assigned: Unassigned » Pierre Paul Lefebvre
Status: Active » Postponed (maintainer needs more info)

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.

davidkarlsson’s picture

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.

dawehner’s picture

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.

Pierre Paul Lefebvre’s picture

Status: Postponed (maintainer needs more info) » Active

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

davidkarlsson’s picture

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

minnur’s picture

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’s picture

mysql is 5.1.63-cll
encoding is UTF-8 Unicode (utf8)
all tables are utf8_general_ci
collation is utf8_general_ci

engine is mysql

the drupal site is in russian, but the search doesn't work as needed with english keywords either

what do i need to change to make the search case-insensitive and how? because right now the results for "Yu" and "yu" differ.
Maybe i can somehow strtolower the keyword and the database record?

please help, i desperately need this

davidkarlsson’s picture

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

srgk’s picture

ok guys thank you all

when i transferred the site to a different server the search became case insensitive, i haven't touched anything except export/import the db
so i guess i will never know the truth

sjf’s picture

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.

lpalgarvio’s picture

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

LTech’s picture

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

dshields’s picture

any movement on this issue?

estoyausente’s picture

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

dshields’s picture

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.

Pierre Paul Lefebvre’s picture

@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.

dshields’s picture

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.

estoyausente’s picture

@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?

yazzybe’s picture

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.

yazzybe’s picture

cwightrun’s picture

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?

Dentorat’s picture

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';
      }
    }
  }
}
zhenjan’s picture

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?

cadoughe’s picture

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.

nico.knaepen’s picture

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';
      }
    }
  }
}
ahughes3’s picture

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

azuledu’s picture

Issue summary: View changes
FileSize
861 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-case_insensitive_search-1805272-29.patch. Unable to apply patch. See the log in the details link for more information. View

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.

peroja’s picture

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

WilliamV’s picture

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

stanly64’s picture

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.

geresy’s picture

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.

nielsonm’s picture

Rerolled new patch to work with drush make.

edgar971’s picture

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) . '%'));
  }


acoral’s picture

Category: Bug report » Support request

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!

Wolf_22’s picture

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.

acoral’s picture

Yeahhhhhhhhh!

Thanks, Wolf_22

#37 works for me
filter is case insensitive

rafiqasad’s picture

#37 Works for me.

Thank Wolf_22

PhilY’s picture

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

Anybody’s picture

Status: Active » Needs review

#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?

The last submitted patch, 29: views-case_insensitive_search-1805272-29.patch, failed testing.

geresy’s picture

Same here #37 fixed it. Thank you!

bocaj’s picture

#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!

neiddesign’s picture

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

maxplus’s picture

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

Codeblind’s picture

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.

Pierre Paul Lefebvre’s picture

Assigned: Pierre Paul Lefebvre » Unassigned
thtas’s picture

#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... ^

dawehner’s picture

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

laurajeans’s picture

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, " ,!?")) . '%'));
tmedlen’s picture

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

nico.knaepen’s picture

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

sandhya.addweb’s picture

Issue tags: +views
StijnStroobants’s picture

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

sense-design’s picture

#56 does not fix the Postgre SQL error