[REF] expression: search on translated fields

Use a LEFT JOIN on table `ir_translation` instead of a subquery, by
using the same implementation as the one used for ordering by a
translated field.  In particular, this avoids having both the LEFT JOIN
and subquery when searching and sorting on a given translated field.

2 files changed, 5 insertions, 43 deletions

diff --git a/odoo/addons/base/tests/test_expression.py b/odoo/addons/base/tests/test_expression.py
index fdec7204503..7aeb57cac1d 100644
--- a/odoo/addons/base/tests/test_expression.py
+++ b/odoo/addons/base/tests/test_expression.py
@@ -1010,19 +1010,7 @@ class TestQueries(TransactionCase):
                  AND "res_partner_title__name"."name" = %s
                  AND "res_partner_title__name"."lang" = %s
                  AND "res_partner_title__name"."value" != %s)
-            WHERE ("res_partner_title"."id" IN (
-                WITH temp_irt_current (id, name) AS (
-                    SELECT ct.id, coalesce(it.value, ct."name")
-                    FROM res_partner_title ct
-                    LEFT JOIN ir_translation it ON
-                        (it.name = %s AND it.lang = %s AND it.type = %s
-                        AND it.res_id = ct.id AND it.value != '')
-                )
-                SELECT id
-                FROM temp_irt_current
-                WHERE name LIKE %s
-                ORDER BY name
-            ))
+            WHERE COALESCE("res_partner_title__name"."value", "res_partner_title"."name") LIKE %s
             ORDER BY COALESCE("res_partner_title__name"."value", "res_partner_title"."name")
         ''']):
             Model.search([('name', 'like', 'foo')])
diff --git a/odoo/osv/expression.py b/odoo/osv/expression.py
index fc959c062ae..95edcecb023 100644
--- a/odoo/osv/expression.py
+++ b/odoo/osv/expression.py
@@ -966,40 +966,14 @@ class expression(object):
                     sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
                     if need_wildcard:
                         right = '%%%s%%' % right
-
-                    inselect_operator = 'inselect'
-                    if sql_operator in NEGATIVE_TERM_OPERATORS:
-                        # negate operator (fix lp:1071710)
-                        sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
-                        inselect_operator = 'not inselect'
+                    if sql_operator == 'in':
+                        right = tuple(right)
 
                     unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
 
+                    left = unaccent(model._generate_translated_field(alias, left, self.query))
                     instr = unaccent('%s')
-
-                    if sql_operator == 'in':
-                        right = tuple(right)
-
-                    subselect = """WITH temp_irt_current (id, name) as (
-                            SELECT ct.id, coalesce(it.value,ct.{quote_left})
-                            FROM {current_table} ct
-                            LEFT JOIN ir_translation it ON (it.name = %s and
-                                        it.lang = %s and
-                                        it.type = %s and
-                                        it.res_id = ct.id and
-                                        it.value != '')
-                            )
-                            SELECT id FROM temp_irt_current WHERE {name} {operator} {right} order by name
-                            """.format(current_table=model._table, quote_left=_quote(left), name=unaccent('name'),
-                                       operator=sql_operator, right=instr)
-
-                    params = (
-                        model._name + ',' + left,
-                        get_lang(model.env).code,
-                        'model',
-                        right,
-                    )
-                    push(('id', inselect_operator, (subselect, params)), model, alias, internal=True)
+                    push_result(f"{left} {sql_operator} {instr}", [right])
 
                 else:
                     expr, params = self.__leaf_to_sql(leaf, model, alias)