Crud: Cyrillic issue table id=crudTable - Ajax error

[lachogenchev]

Hello,

I receive next error:
DataTables warning: table id=crudTable - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Console log: 500 (Internal Server Error)

This issue appears when Im typing into the search bar on cyrillic.
I have table with 100 000+ rows, so I did add $this->crud->enableAjaxTable(); to my controller.
This problem occurs only with $this->crud->enableAjaxTable(); enabled. I did test it with just one row and still have this problem.

[iMokhles]

check

setRoute route path in the Your Model CrudController is the same as the
CRUD::resource('model', 'ModelCrudController');

[lloy0076]

It's almst certainly the AJAX tables bug.

[lachogenchev]

I just recreate all the CRUD with a different name of the model and controller and it work without any problem. However if tried with the same name as before I had the same issue.

[tabacitu]

Hi @lachogenchev ,

Sounds like one of the columns isn't searchable, maybe model_function or an attribute? The best long-term fix would be to use the upgrade branch if you haven't overwritten the search() method in your CrudControllers - it fixes all AjaxDatatables issues. We'll launch it as Backpack 3.3 by the end of the week.

To use the upgrade branch please use the version dev-upgrade in your composer.json file, then run composer update. When we launch, you can just type ^3.3.0 instead.

Please let me know if this fixes it for you and we can close the issue.

Cheers!

[mparfeniuk]

@tabacitu Looks like the issue still exist
I have this response from server trying to put cyrillic symbols is search input:
"message": "SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation 'like' (SQL: select count(*) as aggregate frommovieswhere (idlike %\u041f% ornamelike %\u041f% ordescriptionlike %\u041f% orupdated_atlike %\u041f% oryearlike %\u041f%))"

Probably something with updated_at fields

[tabacitu]

@mparfeniuk - huh, that's weird, I never got that error in Backpack before. Did you get this after you upgraded to 3.3?

[mparfeniuk]

@tabacitu it's my first acquaintance with BackPack and I started from 3.3 right away. For now I just remove "updated_at" column from list and all working good

[tabacitu]

Huh. Weird, @mparfeniuk . It seems to be working for me without any problem, using the П character you were searching for: https://cl.ly/2J0c1n47310v

I recommend you check the collation of your db tables - that might be an issue too. But as far as Backpack goes, seems to be working, so I'll close this issue. Please reopen if anybody else can confirm it. I'm wrong all the time.

Cheers!

[whmlol]

I encountered the same problem and i used
$query->orWhere($column['name'], 'like binary', '%' . $searchTerm . '%');
replace
$query->orWhere($column['name'], 'like', '%' . $searchTerm . '%');
in the applySearchLogicForColumn() method

[tabacitu]

Thank you for sharing your solution @whmlol . Let's wait a bit and see if others get the same issue & fix in cyrilic. If so, we might implement this as default.

Cheers!

[talapai]

Please, fix this issue. We use backpack. But this bug frustrating us. Mysql can't search in date fields if query is cyrillic

[tabacitu]

Hi @talapai ,

Sorry this caused you frustration. Just reopened this so we can fix it.

However :-)

I don't _think_ LIKE BINARY is a solution we can implement in the core package. As far as I know, this forces the search to be case sensitive. So if you search "_justin_" it won't find "_Justin Case_" which... I think is not the expected behaviour. We can't break all people's apps in order for the search work with Cyrillic characters corner case.

My gut tells me this is something that could be mysql-related. Something that _could_ be fixed by changing the mysql version, changing from mysql to mysqli driver, or changing the collation on the table, something like that. I will investigate further.

Cheers!

[gumpon]

I used isdate() function checked searchTerm variable.

if ($column['tableColumn']) {
            switch ($columnType) {                
                case 'date':
                case 'datetime':
                    if ($this->isDate($searchTerm)) {
                        $query->orWhere($column['name'], 'like', '%'.$searchTerm.'%');                      
                    }
                    break;
                case 'email':   
                case 'text':
                case 'textarea':
                    $query->orWhere($column['name'], 'like', '%'.$searchTerm.'%');
                    break;

                case 'select':
                case 'select_multiple':
                    $query->orWhereHas($column['entity'], function ($q) use ($column, $searchTerm) {
                        $q->where($column['attribute'], 'like', '%'.$searchTerm.'%');
                    });
                    break;

                default:
                    return;
                    break;
            }
        }
    }

    function isDate($value) 
    {
        if (!$value) {
            return false;
        }

        try {
            new \DateTime($value);
            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

[tabacitu]

Hi @gumpon , thanks for the code. What does this fix for you? So the problem for you appeared when cyrilic characters were used in a search? Or in a date filter? I don't understand. Or anytime it was used, as long as a date column was in the table?

Also, MySQL, PosgreSQL or SQLite?

Thanks!

[gumpon]

Hi @tabacitu , I had a problem when search thai character search and search date.

[tabacitu]

Hmm... thank you @gumpon , I think you're right. There's an opportunity to improve the date search there - I've just done so here - https://github.com/Laravel-Backpack/CRUD/commit/b54b70caac1d1299a7c7c2bebadddab5f6abf949

Will be tagged later today, and available with a composer update.

Thanks. Cheers!