Merge lp:~openerp-dev/openobject-server/6.1-opw-592890TranlationFieldSearch-msh into lp:openobject-server/6.1

[Mohammed Shekha(Open ERP)]

Hello,

Please go through the description of lp:~openerp-dev/openobject-server/7.0-opw-592890SearchOnTranlslatebleField-msh.

Same issue persist in stable 6.1 as well as in stable version 7.0.

Thanks.

Unmerged revisions

4352. By Mohammed Shekha<email address hidden> on 2013-06-05

[FIX]Fixed the issue of search on tranlsated field, search on tranlsated field fails due to expression parsing which fetches ids from ir_translation as well as working table and UNION of this makes search fruitless, also search fails for in language other then english when you enter part of a string for the field to search.

=== modified file 'openerp/osv/expression.py'
--- openerp/osv/expression.py	2012-01-24 12:42:52 +0000
+++ openerp/osv/expression.py	2013-06-05 10:13:29 +0000
@@ -619,38 +619,30 @@
                 if 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'
-                    #Covering in,not in operators with operands (%s,%s) ,etc.
+                    select = 'WITH temp_'+working_table._table+' (id, name) as (' \
+                        'SELECT wt.id, coalesce(it.value,wt.name) ' \
+                        'FROM '+working_table._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 != '' )" \
+                        ') '
                     if sql_operator in ['in','not in']:
                         instr = ','.join(['%s'] * len(right))
-                        subselect += '     AND value ' + sql_operator +  ' ' +" (" + instr + ")"   \
-                             ') UNION ('                \
-                             '  SELECT id'              \
-                             '    FROM "' + working_table._table + '"'       \
-                             '   WHERE "' + left + '" ' + sql_operator + ' ' +" (" + instr + "))"
+                        select += 'SELECT id FROM temp_'+working_table._table+' WHERE "name" '+sql_operator+' ('+instr+') order by name'
                     else:
-                        subselect += '     AND value ' + sql_operator + instr +   \
-                             ') UNION ('                \
-                             '  SELECT id'              \
-                             '    FROM "' + working_table._table + '"'       \
-                             '   WHERE "' + left + '" ' + sql_operator + instr + ")"
-
+                        select += 'SELECT id FROM temp_'+working_table._table+' WHERE "name" '+sql_operator+' '+instr+' order by name'
                     params = [working_table._name + ',' + left,
                               context.get('lang', False) or 'en_US',
                               'model',
-                              right,
-                              right,
-                             ]
-
-                    self.__exp[i] = ('id', 'inselect', (subselect, params))
+                              right
+                              ]
+                    self.__exp[i] = ('id', 'inselect', (select, params))
 
     def __leaf_to_sql(self, leaf, table):
         left, operator, right = leaf