Merge lp:~openerp-dev/openobject-server/7.0-opw-592890SearchOnTranlslatebleField-msh into lp:openobject-server/7.0

[Mohammed Shekha(Open ERP)]

Hello,

Fixed the issue of no other operator supported in advance search(except contains) if language is other then english for translated field.

Demo:- Install german language -> use german language -> go to supplier listview(to face this issue add/show country field in listview), add advance filter with Land, enthalt nicht(doesn't contains) and value(say for example Belgien)
Result: It doesn't give resule according to filter applied, even though we filter that doesn't contains Belgien still it will give records with Belgien.

Reason: It is due to expression parsing, if field is translateable then we make UNION of res_id from it_translation and same string we search in current working table(i.e. records from currenct working table where filed value not Belgien, in working table field value always be in English source language).

Fixed the issue by making virtual table using WITH query with LEFT JOIN ir_tranlsations, this way we will be able to search on translated field with whole string as well as part of a tranlsated string.

Thanks.

Unmerged revisions

=== modified file 'openerp/osv/expression.py'
--- openerp/osv/expression.py	2013-05-14 10:33:33 +0000
+++ openerp/osv/expression.py	2013-06-05 10:10:37 +0000
@@ -1020,37 +1020,32 @@
                 elif field.translate:
                     need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
                     sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
+                    instr = ' %s'
                     if need_wildcard:
                         right = '%%%s%%' % right
 
-                    subselect = '( SELECT res_id'          \
-                             '    FROM ir_translation'  \
-                             '   WHERE name = %s'       \
-                             '     AND lang = %s'       \
-                             '     AND type = %s'
-                    instr = ' %s'
+                    select = 'WITH temp_'+working_model._table+' (id, name) as (' \
+                        'SELECT wt.id, coalesce(it.value,wt.name) ' \
+                        'FROM '+working_model._table+' wt ' \
+                        'LEFT JOIN ir_translation it ON (it.name = %s and ' \
+                                        'it.lang = %s and ' \
+                                        'it.type = %s and ' \
+                                        'it.res_id = wt.id and ' \
+                                        "it.value != '')" \
+                        ') '
                     #Covering in,not in operators with operands (%s,%s) ,etc.
                     if sql_operator in ['in', 'not in']:
                         instr = ','.join(['%s'] * len(right))
-                        subselect += '     AND value ' + sql_operator + ' ' + " (" + instr + ")"   \
-                             ') UNION ('                \
-                             '  SELECT id'              \
-                             '    FROM "' + working_model._table + '"'       \
-                             '   WHERE "' + left + '" ' + sql_operator + ' ' + " (" + instr + "))"
+                        select += 'SELECT id FROM temp_'+working_model._table+' WHERE "name" '+sql_operator+' ('+instr+') order by name'
                     else:
-                        subselect += '     AND value ' + sql_operator + instr +   \
-                             ') UNION ('                \
-                             '  SELECT id'              \
-                             '    FROM "' + working_model._table + '"'       \
-                             '   WHERE "' + left + '" ' + sql_operator + instr + ")"
+                        select += 'SELECT id FROM temp_'+working_model._table+' WHERE "name" '+sql_operator+' '+instr+' order by name'
 
                     params = [working_model._name + ',' + left,
                               context.get('lang', False) or 'en_US',
                               'model',
                               right,
-                              right,
                              ]
-                    push(create_substitution_leaf(leaf, ('id', 'inselect', (subselect, params)), working_model))
+                    push(create_substitution_leaf(leaf, ('id', 'inselect', (select, params)), working_model))
 
                 else:
                     push_result(leaf)