Can't search with cyrillic character ( Unicode )

[dremdem]

I'm try to search unicode character in DataTables and no result founded. Bytheway, I rewrite source query with the same ( "like" ) filter and filter is working.

[dremdem]

Here some SQL-trace:

2016-12-16 06:38:44,316 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2016-12-16 06:38:44,328 INFO sqlalchemy.engine.base.Engine SELECT count() AS count_1 FROM (SELECT [SoftClassTemplate].[TemplateId] AS [SoftClassTemplate_TemplateId] FROM [SoftClassTemplate]) AS anon_1 2016-12-16 06:38:44,340 INFO sqlalchemy.engine.base.Engine () 2016-12-16 06:38:47,163 INFO sqlalchemy.engine.base.Engine SELECT count() AS count_1 FROM (SELECT [SoftClassTemplate].[TemplateId] AS [SoftClassTemplate_TemplateId] FROM [SoftClassTemplate] WHERE lower(CAST([SoftClassTemplate].[TemplateId] AS VARCHAR(max))) LIKE lower(?) OR lower(CAST([SoftClassTemplate].[TemplateName] AS VARCHAR(max))) LIKE lower(?) OR lower(CAST([SoftClassTemplate].[Description] AS VARCHAR(max))) LIKE lower(?)) AS anon_1 2016-12-16 06:38:47,175 INFO sqlalchemy.engine.base.Engine (u'%\u041f\u0440\u043e%', u'%\u041f\u0440\u043e%', u'%\u041f\u0440\u043e%') 2016-12-16 06:38:47,425 INFO sqlalchemy.engine.base.Engine SELECT TOP 10 [SoftClassTemplate].[TemplateId] AS [SoftClassTemplate_TemplateId], [SoftClassTemplate].[TemplateName] AS [SoftClassTemplate_TemplateName], [SoftClassTemplate].[Description] AS [SoftClassTemplateDescription] FROM [SoftClassTemplate] WHERE lower(CAST([SoftClassTemplate].[TemplateId] AS VARCHAR(max))) LIKE lower(?) OR lower(CAST([SoftClassTemplate].[TemplateName] AS VARCHAR(max))) LIKE lower(?) OR lower(CAST([SoftClassTemplate].[Description] AS VARCHAR(max))) LIKE lower(?) ORDER BY [SoftClassTemplate].[TemplateId] ASC 2016-12-16 06:38:47,436 INFO sqlalchemy.engine.base.Engine (u'%\u041f\u0440\u043e%', u'%\u041f\u0440\u043e%', u'%\u041f\u0440\u043e%') 2016-12-16 06:38:47,689 INFO sqlalchemy.engine.base.Engine ROLLBACK 10.0.2.2 - - [16/Dec/2016 06:38:47] "GET /templates/category/all?draw=3&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=%D0%9F%D1%80%D0%BE&search%5Bregex%5D=false&=1481888245581 HTTP/1.1" 200 -

[dremdem]

Seems like the problem in NVARCHAR column ( Microsoft SQL Server RDBMS ). If I use NVARCHAR type in CAST clause it's ok.

[dremdem]

Personally for me it's resolved by rewriting _set_global_filter_expression method: ( replace Text with UnicodeText )

class DataTablesUni(DataTables):
    def _set_global_filter_expression(self):
        # global search filter
        global_search = self.params.get('search[value]', '')
        if global_search is '':
            return

        if (self.allow_regex_searches and
                self.params.get('search[regex]') == 'true'):
            op = self._get_regex_operator()
            val = clean_regex(global_search)
            filter_for = lambda col: col.sqla_expr.op(op)(val)
        else:
            val = '%' + global_search + '%'
            filter_for = lambda col: col.sqla_expr.cast(UnicodeText).ilike(val)

        global_filter = [filter_for(col)
                         for col in self.columns if col.global_search]

        self.filter_expressions.append(or_(*global_filter))

[tdamsma]

I think this should be merged into master, care to make a PR?

[tdamsma]

Closed old issue