SOLVED: Search all doesn't work with non-english characters

Status
Not open for further replies.
dimoss

dimoss

Well-Known Member
Hi

The search all function doesn't work with fields containing non-english characters even if the db collation is UTF8.
I am trying to search a name contains Greek characters and it doesn't work. When I changed the name to English the search function work fine.
This is a problem also in the latest F3.0.9 for J!2.5.x version.

Thanks,

Dimos
 
It's working on my 3.1 site (tested with ??????).
Is your DB table column UTF8, too? (Changing the DB collation won't change any existing table/column collations)
Field included in search all?
 
Hi Troester

Yes, the table column is UTF 8 general ci in both cases (F3.1.1 and F3.0.9)
In both cases the element is cdd if this helps you.
Thanks
 
Hi again

Testing more I saw that it works for simple field element in both cases.
Thanks
 
CDD is also working on my site (3.1rc1).
Can you enable fabrikdebug (Fabrik options) and append &fabrikdebug=1 to your URL
A click on "list getData: your table"
should show the mySQL query
 
I get the following:

Code: 
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_jbs`.`id` AS `fab_jbs___id`, `fab_jbs`.`id` AS `fab_jbs___id_raw`, `fab_jbs`.`create_date` AS `fab_jbs___create_date`, `fab_jbs`.`create_date` AS `fab_jbs___create_date_raw`, `fab_jbs`.`current_date` AS `fab_jbs___current_date`, `fab_jbs`.`current_date` AS `fab_jbs___current_date_raw`, `fab_jbs`.`create_user` AS `fab_jbs___create_user_raw`, `entr_users_0`.`id` AS `fab_jbs___create_user`, `fab_jbs`.`update_user` AS `fab_jbs___update_user_raw`, `entr_users_1`.`id` AS `fab_jbs___update_user`, `fab_jbs`.`assoc` AS `fab_jbs___assoc_raw`, `entr_users`.`username` AS `fab_jbs___assoc`, `fab_jbs`.`tournament` AS `fab_jbs___tournament_raw`, `fab_tournaments`.`tournament` AS `fab_jbs___tournament`, `fab_jbs`.`player_id` AS `fab_jbs___player_id_raw`, `fab_plist`.`name` AS `fab_jbs___player_id`, `fab_jbs`.`year` AS `fab_jbs___year`, `fab_jbs`.`year` AS `fab_jbs___year_raw`, `fab_jbs`.`cat` AS `fab_jbs___cat`, `fab_jbs`.`cat` AS `fab_jbs___cat_raw`, `fab_jbs`.`limit` AS `fab_jbs___limit`, `fab_jbs`.`limit` AS `fab_jbs___limit_raw`, `fab_jbs`.`count` AS `fab_jbs___count`, `fab_jbs`.`count` AS `fab_jbs___count_raw`, `fab_jbs`.`wl` AS `fab_jbs___wl`, `fab_jbs`.`wl` AS `fab_jbs___wl_raw`, `fab_jbs`.`wl_remove` AS `fab_jbs___wl_remove`, `fab_jbs`.`wl_remove` AS `fab_jbs___wl_remove_raw`, `fab_jbs`.`id` AS slug , `fab_jbs`.`id` AS `__pk_val` FROM `fab_jbs` LEFT JOIN `entr_users` AS `entr_users` ON `entr_users`.`username` = `fab_jbs`.`assoc` LEFT JOIN `fab_tournaments` AS `fab_tournaments` ON `fab_tournaments`.`id` = `fab_jbs`.`tournament` LEFT JOIN `fab_plist` AS `fab_plist` ON `fab_plist`.`player_id` = `fab_jbs`.`player_id` LEFT JOIN `#__users` AS `entr_users_0` ON `entr_users_0`.`id` = `fab_jbs`.`create_user` LEFT JOIN `#__users` AS `entr_users_1` ON `entr_users_1`.`id` = `fab_jbs`.`update_user` WHERE ( fab_jbs.assoc = 'GER' AND ( fab_jbs.tournament IN (SELECT id FROM fab_tournaments WHERE activation = 'Yes') ) ) AND ( ( `fab_plist`.`name` REGEXP LOWER('????????') OR `fab_plist`.`name` REGEXP LOWER('????????')) ) ORDER BY `fab_jbs`.`id` ASC

You see I look for '????????' which is a Greek Name.
But it returns nothing. I repeat that this is only stands for CDD (I suppose for dbjoin) and not for simple fields.

As you can see it is joined and get the name from the fab_plist___name which is a calc element and TEXT UTF8 General ci in the db.
 
I enabled the Advanced Search all function and search for Any of these terms and worked!

Code: 
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_jbs`.`id` AS `fab_jbs___id`, `fab_jbs`.`id` AS `fab_jbs___id_raw`, `fab_jbs`.`create_date` AS `fab_jbs___create_date`, `fab_jbs`.`create_date` AS `fab_jbs___create_date_raw`, `fab_jbs`.`current_date` AS `fab_jbs___current_date`, `fab_jbs`.`current_date` AS `fab_jbs___current_date_raw`, `fab_jbs`.`create_user` AS `fab_jbs___create_user_raw`, `entr_users_0`.`id` AS `fab_jbs___create_user`, `fab_jbs`.`update_user` AS `fab_jbs___update_user_raw`, `entr_users_1`.`id` AS `fab_jbs___update_user`, `fab_jbs`.`assoc` AS `fab_jbs___assoc_raw`, `entr_users`.`username` AS `fab_jbs___assoc`, `fab_jbs`.`tournament` AS `fab_jbs___tournament_raw`, `fab_tournaments`.`tournament` AS `fab_jbs___tournament`, `fab_jbs`.`player_id` AS `fab_jbs___player_id_raw`, `fab_plist`.`name` AS `fab_jbs___player_id`, `fab_jbs`.`year` AS `fab_jbs___year`, `fab_jbs`.`year` AS `fab_jbs___year_raw`, `fab_jbs`.`cat` AS `fab_jbs___cat`, `fab_jbs`.`cat` AS `fab_jbs___cat_raw`, `fab_jbs`.`limit` AS `fab_jbs___limit`, `fab_jbs`.`limit` AS `fab_jbs___limit_raw`, `fab_jbs`.`count` AS `fab_jbs___count`, `fab_jbs`.`count` AS `fab_jbs___count_raw`, `fab_jbs`.`wl` AS `fab_jbs___wl`, `fab_jbs`.`wl` AS `fab_jbs___wl_raw`, `fab_jbs`.`wl_remove` AS `fab_jbs___wl_remove`, `fab_jbs`.`wl_remove` AS `fab_jbs___wl_remove_raw`, `fab_jbs`.`id` AS slug , `fab_jbs`.`id` AS `__pk_val` FROM `fab_jbs` LEFT JOIN `entr_users` AS `entr_users` ON `entr_users`.`username` = `fab_jbs`.`assoc` LEFT JOIN `fab_tournaments` AS `fab_tournaments` ON `fab_tournaments`.`id` = `fab_jbs`.`tournament` LEFT JOIN `fab_plist` AS `fab_plist` ON `fab_plist`.`player_id` = `fab_jbs`.`player_id` LEFT JOIN `#__users` AS `entr_users_0` ON `entr_users_0`.`id` = `fab_jbs`.`create_user` LEFT JOIN `#__users` AS `entr_users_1` ON `entr_users_1`.`id` = `fab_jbs`.`update_user` WHERE ( fab_jbs.assoc = 'GER' AND ( fab_jbs.tournament IN (SELECT id FROM fab_tournaments WHERE activation = 'Yes') ) ) AND ( MATCH(`fab_plist`.`name`) against ('????????*' IN BOOLEAN MODE) ) ORDER BY `fab_jbs`.`id` ASC

It seems that somehow needs this to work for non-english characters because when I deactivated again this feature it worked correctly for English characters even if I searched only the surname and not the whole name.

Anyway...:)
 
No, search all is not case sensitive.

A normal field is searched with
LOWER(`field`) REGEXP LOWER('StRiNg') but CDDs have
CONCAT_WS(.....) REGEXP LOWER('StRiNg')

Should be LOWER(CONCAT_WS(.....)) REGEXP LOWER('StRiNg')
 
`fab_plist`.`name` REGEXP LOWER('????????') should be
LOWER(`fab_plist`.`name`) REGEXP LOWER('????????')
Click to expand...
troester - depends on the type of search all - if 'Extended search all' = yes then we do not use that type of query
where as dimoss's is:

AND ( MATCH(`fab_plist`.`name`) against ('????????*' IN BOOLEAN MODE) )
Click to expand...

Dimoss - can you try turning off 'Extended search all' and see if that works?

-Rob
 
I have a standard "search all" (not extended), two fields included :
test.testfeld1 = field element
test.cdd = cdd element (LEFT JOIN `test3` AS `test3` ON `test3`.`id` = `test`.`cdd`)
test3.feld = cdd label = field element

Fabrik debug is showing

WHERE ( ( LOWER(`test`.`testfeld1`) REGEXP LOWER('????') OR `test3`.`feld` REGEXP LOWER('????') OR `test3`.`feld` REGEXP LOWER('????') OR `test3`.`feld` REGEXP LOWER('????')) )

As you can see the test.testfeld1 and searched string are "lowered",
the cdd label test3.feld is not.

Strange enough REGEXP is case insensitiv if the string contains acsii characters (feld containing "GROSS" is found with `test3`.`feld` REGEXP LOWER('GROSS'), this seems to be a MySQL bug or inconsistency.
So nobody is running into this search all issue as long as he's using ascii only.
 
Hi Rob

I confirm what Troester says.
I tested with the Extended function disabled in two different situations:

1. Searching a field element: Works ok both with Uppercase & Lowercase letters which means '????????' or '????????' or even with half of the word.

2. Searching a cdd element: It doesn't work either with Uppercase neither with Lowercase letters.

In both cases the English (ascii) characters search works as expected.

I tested on a J!3.1.5 with F! latest github ver. I believe the same behavior stands for the F!3.0.9 also

Thanks!

Dimos
 
I think I've fixed this one for 3.1
Could you update and let me know if it works for you?

-Rob
 
Hi Rob

I tested it with latest Github and found the following:

1. Search in field element works ok as before both with capital and plain letters.
2. Search in CDD element works ok with capitals BUT not with plain letters. For example searching for '????????' or '?????' return results as expected. However looking for '????????' returns nothing. On the contrary looking for '?????' (half word) returns results.

Another problem I discovered is that the CDD element lost the settings. For example I had set it to watch a specific element but now shows - in id, label and foreign key. With the 23-10-2013 github was ok.

Thanks.
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 116 (members: 2, guests: 114)
    Back
    Top