Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comment | File | Size | Author |
---|---|---|---|
#56 | views-case_insensitive_search-1805272-56.patch | 607 bytes | StijnStroobants |
| |||
#35 | views_handler_filter_combine.inc_.zip | 2.35 KB | edgar971 |
#34 | views-case_insensitive_search-1805272-34.patch | 1.09 KB | nielsonm |
#29 | views-case_insensitive_search-1805272-29.patch | 861 bytes | azuledu |
Comments
Comment #1
Pierre Paul Lefebvre CreditAttribution: Pierre Paul Lefebvre commentedThe 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
Comment #2
IWasBornToWin CreditAttribution: IWasBornToWin commentedPatch works excellent, also works well for me - regardless of caps or not.
Comment #3
Pierre Paul Lefebvre CreditAttribution: Pierre Paul Lefebvre commentedI 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.
Comment #4
davidkarlsson CreditAttribution: davidkarlsson commentedI 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.
Comment #5
dawehnerWell 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.
Comment #6
Pierre Paul Lefebvre CreditAttribution: Pierre Paul Lefebvre commentedI 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
Comment #7
davidkarlsson CreditAttribution: davidkarlsson commenteddawehner: 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
Comment #8
minnur CreditAttribution: minnur commentedI 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.
Comment #9
srgk CreditAttribution: srgk commentedmysql 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
Comment #10
davidkarlsson CreditAttribution: davidkarlsson commentedsrgk: 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
Comment #11
srgk CreditAttribution: srgk commentedok 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
Comment #12
sjf CreditAttribution: sjf commentedSame 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.
Comment #13
lpalgarvio CreditAttribution: lpalgarvio commentedi'm having the same issue, but with results supplied by module Data (retrives fields from other SQL schemas)
Comment #14
LTech CreditAttribution: LTech commentedIt is also case sensitive for me. Is there a way to change this? Thanks
Comment #15
dshields CreditAttribution: dshields commentedany movement on this issue?
Comment #16
estoyausenteSame problem. I search in titles, and some text and long-text field, and are sensitive. Suscribe!
Comment #17
dshields CreditAttribution: dshields commentedThis 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.
Comment #18
Pierre Paul Lefebvre CreditAttribution: Pierre Paul Lefebvre commented@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
If nothing shows up after this, we will escalate to the table level and see PHP mysql client collation info.
Comment #19
dshields CreditAttribution: dshields commentedIt 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.
Comment #20
estoyausente@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?
Comment #21
Yazzbe CreditAttribution: Yazzbe commentedI 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.
Comment #22
Yazzbe CreditAttribution: Yazzbe commentedComment #23
cwightrun CreditAttribution: cwightrun commentedI'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?
Comment #24
Dentorat CreditAttribution: Dentorat commentedHey, 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
Comment #25
zhenjan CreditAttribution: zhenjan commentedThe 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?
Comment #26
cadoughe CreditAttribution: cadoughe commentedI'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.
Comment #27
nico.knaepen CreditAttribution: nico.knaepen commentedThe 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:
Comment #28
ahughes3 CreditAttribution: ahughes3 commentedI 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
Comment #29
azuledu CreditAttribution: azuledu commentedThe 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.
Comment #30
perhenrik CreditAttribution: perhenrik commentedThanks 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
Comment #31
WilliamV CreditAttribution: WilliamV commented#24 and #27 both work like a charm, thank you for sharing this insights!
Comment #32
stanly64 CreditAttribution: stanly64 commentedHello 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.
Comment #33
geresy CreditAttribution: geresy commentedI 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.
Comment #34
nielsonm CreditAttribution: nielsonm commentedRerolled new patch to work with drush make.
Comment #35
edgar971 CreditAttribution: edgar971 commentedI 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.
Comment #36
acoral CreditAttribution: acoral commentedHi 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?
Thanks!
Comment #37
Wolf_22 CreditAttribution: Wolf_22 commentedI 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:
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.
Comment #38
acoral CreditAttribution: acoral commentedYeahhhhhhhhh!
Thanks, Wolf_22
#37 works for me
filter is case insensitive
Comment #39
rafiqasad CreditAttribution: rafiqasad commented#37 Works for me.
Thank Wolf_22
Comment #40
PhilYMy turn to thanks Wolf_22 for #37 (using D7.36 & Views 7.x-3.10)
Comment #41
Anybody#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?
Comment #43
geresy CreditAttribution: geresy commentedSame here #37 fixed it. Thank you!
Comment #44
bocaj CreditAttribution: bocaj commented#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!
Comment #45
Thremulant CreditAttribution: Thremulant commented#37 worked for my site. Shouldn't this be implemented directly on the Views module??
Comment #46
maxplus CreditAttribution: maxplus commentedHi,
thanks, I'm using #37 on a production site and will report back with the results after some testing.
Comment #47
Codeblind CreditAttribution: Codeblind commentedI 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.
Comment #48
Pierre Paul Lefebvre CreditAttribution: Pierre Paul Lefebvre commentedComment #49
thtas CreditAttribution: thtas commented#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... ^
Comment #50
dawehnerHi!
Does someone know whether this issue has a corresponding Drupal 8 issue to fix this for now and forever?
Comment #51
laurajeans CreditAttribution: laurajeans as a volunteer commentedSolution for postgres users: in /views/handlers/views_handler_filter_combine.inc
Views 7.x-3.14
PSQL 9.3.10
Line 122
Comment #52
tmedlen CreditAttribution: tmedlen commented#51 worked for me using PSQL, though also had to edit the $field and $placeholder in function op_contains()
Comment #53
nico.knaepen CreditAttribution: nico.knaepen at Logic in Motion commentedCan someone patch the change for #51? Otherwise it will not get commited.
Comment #54
zalak.addweb CreditAttribution: zalak.addweb commentedComment #55
Lendude@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
Comment #56
StijnStroobantsHad the same issue. #37 solved the problem for me.
Created a patch.
Comment #57
sense-design#56 does not fix the Postgre SQL error
Comment #58
Chris Matthews CreditAttribution: Chris Matthews as a volunteer commentedThe 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.
Comment #59
MustangGB CreditAttribution: MustangGB commentedComment #60
strelkovandreyvalerievichAnyone 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
Comment #61
dalinIt 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)";