def __init__(self, domain, model, alias=None, query=None):
""" Initialize expression object and automatically parse the expression
right after initialization.
:param domain: expression (using domain ('foo', '=', 'bar') format)
:param model: root model
:param alias: alias for the model table if query is provided
:param query: optional query object holding the final result
:attr root_model: base model for the query
:attr expression: the domain to parse, normalized and prepared
:attr result: the result of the parsing, as a pair (query, params)
:attr query: Query object holding the final result
"""
self._unaccent = get_unaccent_wrapper(model._cr)
self.root_model = model
self.root_alias = alias or model._table
# normalize and prepare the expression for parsing
self.expression = distribute_not(normalize_domain(domain))
# this object handles all the joins
self.query = Query(model.env.cr, model._table, model._table_query) if query is None else query
# parse the domain expression
self.parse()
def _compute_application_count(self):
self.flush(fnames=['email_from'])
# Filter and gather emails at the same time
applicants = self.env['hr.applicant']
mails = set()
for applicant in self:
if applicant.email_from:
applicants |= applicant
mails.add(applicant.email_from.lower())
# Done via SQL since read_group does not support grouping by lowercase field
if mails:
query = Query(self.env.cr, self._table, self._table_query)
query.add_where('LOWER("hr_applicant".email_from) in %s', [tuple(mails)])
self._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
query_str = """
SELECT LOWER("%(table)s".email_from) as l_email_from,
COUNT("%(table)s".id) as count
FROM %(from)s
%(where)s
GROUP BY l_email_from
""" % {
'table': self._table,
'from': from_clause,
'where': ('WHERE %s' % where_clause) if where_clause else '',
}
self.env.cr.execute(query_str, where_clause_params)
application_data_mapped = dict((data['l_email_from'], data['count']) for data in self.env.cr.dictfetchall())
else:
application_data_mapped = dict()
for applicant in applicants:
applicant.application_count = application_data_mapped.get(applicant.email_from.lower(), 1)
(self - applicants).application_count = False
def _search_is_authorized(self, operator, value):
if operator not in ('=', '!=', '<>'):
raise ValueError('Invalid operator: %s' % (operator, ))
SS = self.env['sale.subscription']
tbls = (self._table, SS._table)
query = Query(tbls,
["%s.subscription_template_id = %s.template_id" % tbls],
[])
SS._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
self.env.cr.execute(
"""
SELECT {self}.id
FROM {from_}
WHERE {where}
""".format(self=self._table, from_=from_clause, where=where_clause),
where_clause_params)
ids = [i[0] for i in self.env.cr.fetchall()]
op = 'in' if (operator == '=' and value) or (
operator != '=' and not value) else 'not in'
return [('id', op, ids)]
def _get_sale_order_items_query(self, domain_per_model=None):
if domain_per_model is None:
domain_per_model = {}
project_domain = [('id', 'in', self.ids),
('sale_line_id', '!=', False)]
if 'project.project' in domain_per_model:
project_domain = expression.AND(
[project_domain, domain_per_model['project.project']])
project_query = self.env['project.project']._where_calc(project_domain)
self._apply_ir_rules(project_query, 'read')
project_query_str, project_params = project_query.select(
'id', 'sale_line_id')
Task = self.env['project.task']
task_domain = [('project_id', 'in', self.ids),
('sale_line_id', '!=', False)]
if Task._name in domain_per_model:
task_domain = expression.AND(
[task_domain, domain_per_model[Task._name]])
task_query = Task._where_calc(task_domain)
Task._apply_ir_rules(task_query, 'read')
task_query_str, task_params = task_query.select(
f'{Task._table}.project_id AS id', f'{Task._table}.sale_line_id')
query = Query(self._cr, 'project_sale_order_item',
' UNION '.join([project_query_str, task_query_str]))
query._where_params = project_params + task_params
return query
def test_raise_missing_lhs(self):
query = Query()
query.add_table('product_product')
self.assertRaises(AssertionError,
query.add_join,
("product_template", "product_category", "categ_id",
"id", "categ_id"),
implicit=False,
outer=False)
def _inherits_join_calc(self, alias, fname, query):
"""
Adds missing table select and join clause(s) to ``query`` for reaching
the field coming from an '_inherits' parent table (no duplicates).
:param alias: name of the initial SQL alias
:param fname: name of inherited field to reach
:param query: query object on which the JOIN should be added
:return: qualified name of field, to be used in SELECT clause
"""
# INVARIANT: alias is the SQL alias of model._table in query
model, field = self, self._fields[fname]
if field.type == "many2many":
while field.inherited:
# retrieve the parent model where field is inherited from
parent_model = self.env[field.related_field.model_name]
parent_fname = field.related[0]
# JOIN parent_model._table AS parent_alias
# ON alias.parent_fname = parent_alias.id
parent_alias = query.left_join(alias, parent_fname,
parent_model._table, "id",
parent_fname)
model, alias, field = parent_model, parent_alias, field.related_field
# special case for many2many fields: prepare a query on the comodel
# in order to reuse the mechanism _apply_ir_rules, then inject the
# query as an extra condition of the left join
comodel = self.env[field.comodel_name]
subquery = Query(self.env.cr, comodel._table)
comodel._apply_ir_rules(subquery)
# add the extra join condition only if there is an actual subquery
extra, extra_params = None, ()
if subquery.where_clause:
subquery_str, extra_params = subquery.select()
extra = '"{{rhs}}"."{}" IN ({})'.format(
field.column2, subquery_str)
# LEFT JOIN field_relation ON
# alias.id = field_relation.field_column1
# AND field_relation.field_column2 IN (subquery)
left_coumn = "id"
if alias == "sale_report":
left_coumn = "order_id"
if alias == "account_invoice_report":
left_coumn = "move_id"
rel_alias = query.left_join(
alias,
left_coumn,
field.relation,
field.column1,
field.name,
extra=extra,
extra_params=extra_params,
)
return '"{}"."{}"'.format(rel_alias, field.column2)
return super()._inherits_join_calc(alias, fname, query)
def _get_sale_order_items_query(self, domain_per_model=None):
if domain_per_model is None:
domain_per_model = {}
billable_project_domain = [('allow_billable', '=', True)]
project_domain = [('id', 'in', self.ids),
('sale_line_id', '!=', False)]
if 'project.project' in domain_per_model:
project_domain = expression.AND([
domain_per_model['project.project'],
project_domain,
billable_project_domain,
])
project_query = self.env['project.project']._where_calc(project_domain)
self._apply_ir_rules(project_query, 'read')
project_query_str, project_params = project_query.select(
'id', 'sale_line_id')
Task = self.env['project.task']
task_domain = [('project_id', 'in', self.ids),
('sale_line_id', '!=', False)]
if Task._name in domain_per_model:
task_domain = expression.AND([
domain_per_model[Task._name],
task_domain,
])
task_query = Task._where_calc(task_domain)
Task._apply_ir_rules(task_query, 'read')
task_query_str, task_params = task_query.select(
f'{Task._table}.project_id AS id', f'{Task._table}.sale_line_id')
ProjectMilestone = self.env['project.milestone']
milestone_domain = [('project_id', 'in', self.ids),
('allow_billable', '=', True),
('sale_line_id', '!=', False)]
if ProjectMilestone._name in domain_per_model:
milestone_domain = expression.AND([
domain_per_model[ProjectMilestone._name],
milestone_domain,
billable_project_domain,
])
milestone_query = ProjectMilestone._where_calc(milestone_domain)
ProjectMilestone._apply_ir_rules(milestone_query)
milestone_query_str, milestone_params = milestone_query.select(
f'{ProjectMilestone._table}.project_id AS id',
f'{ProjectMilestone._table}.sale_line_id',
)
query = Query(
self._cr, 'project_sale_order_item', ' UNION '.join(
[project_query_str, task_query_str, milestone_query_str]))
query._where_params = project_params + task_params + milestone_params
return query
def test_long_aliases(self):
query = Query(None, 'product_product')
tmp = query.join('product_product', 'product_tmpl_id',
'product_template', 'id', 'product_tmpl_id')
self.assertEqual(tmp, 'product_product__product_tmpl_id')
# no hashing
tmp_cat = query.join(tmp, 'product_category_id', 'product_category',
'id', 'product_category_id')
self.assertEqual(
tmp_cat, 'product_product__product_tmpl_id__product_category_id')
# hashing to limit identifier length
tmp_cat_cmp = query.join(tmp_cat, 'company_id', 'res_company', 'id',
'company_id')
self.assertEqual(
tmp_cat_cmp,
'product_product__product_tmpl_id__product_category_id__9f0ddff7')
tmp_cat_stm = query.join(tmp_cat, 'salesteam_id', 'res_company', 'id',
'salesteam_id')
self.assertEqual(
tmp_cat_stm,
'product_product__product_tmpl_id__product_category_id__953a466f')
# extend hashed identifiers
tmp_cat_cmp_par = query.join(tmp_cat_cmp, 'partner_id', 'res_partner',
'id', 'partner_id')
self.assertEqual(
tmp_cat_cmp_par,
'product_product__product_tmpl_id__product_category_id__56d55687')
tmp_cat_stm_par = query.join(tmp_cat_stm, 'partner_id', 'res_partner',
'id', 'partner_id')
self.assertEqual(
tmp_cat_stm_par,
'product_product__product_tmpl_id__product_category_id__9_363fdd')
def test_mixed_query_chained_explicit_implicit_joins(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append(
"product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id",
"id", "categ_id"),
implicit=False,
outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id",
"id", "user_id"),
implicit=False,
outer=True) # CHAINED outer join
query.tables.append('"account.account"')
query.where_clause.append(
"product_category.expense_account_id = account_account.id"
) # additional implicit join
self.assertEquals(
query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id"),"account.account" """
.strip())
self.assertEquals(
query.get_sql()[1].strip(),
"""product_product.template_id = product_template.id AND product_category.expense_account_id = account_account.id"""
.strip())
def test_basic_query(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append("product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id", "id", "categ_id"), implicit=False, outer=False) # add normal join
query.add_join(("product_product", "res_user", "user_id", "id", "user_id"), implicit=False, outer=True) # outer join
self.assertEquals(query.get_sql()[0].strip(),
""""product_product" LEFT JOIN "res_user" as "product_product__user_id" ON ("product_product"."user_id" = "product_product__user_id"."id"),"product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") """.strip())
self.assertEquals(query.get_sql()[1].strip(), """product_product.template_id = product_template.id""".strip())
def test_query_chained_explicit_joins(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append("product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id", "id", "categ_id"), implicit=False, outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id", "id", "user_id"), implicit=False, outer=True) # CHAINED outer join
self.assertEquals(query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")""".strip())
self.assertEquals(query.get_sql()[1].strip(), """product_product.template_id = product_template.id""".strip())
def _where_calc(self, domain, active_test=True):
"""Computes the WHERE clause needed to implement an OpenERP domain.
:param domain: the domain to compute
:type domain: list
:param active_test: whether the default filtering of records with ``active``
field set to ``False`` should be applied.
:return: the query expressing the given domain as provided in domain
:rtype: osv.query.Query
"""
# if the object has a field named 'active', filter out all inactive
# records unless they were explicitely asked for
if 'active' in self._fields and active_test and self._context.get(
'active_test', True):
# the item[0] trick below works for domain items and '&'/'|'/'!'
# operators too
if not any(item[0] == 'active' for item in domain):
domain = [('active', '=', 1)] + domain
#is_multi_search_installed = self.env['ir.module.module'].search([('state','=','installed'),('name','=','multi_search')], limit=1)
self.env.cr.execute(
"SELECT id FROM ir_module_module WHERE name='multi_search_with_comma' and state='installed' limit 1"
)
is_multi_search_installed = self.env.cr.fetchone()
if domain:
modified_domain = []
#_logger.info(str(domain))
for domain_tuple in domain:
if not is_multi_search_installed:
modified_domain.append(domain_tuple)
continue
if type(domain_tuple) in (list, tuple):
if str(domain_tuple[1]) == 'ilike':
multi_name = domain_tuple[2].split(',')
len_name = len(multi_name)
if len_name > 1:
for length in multi_name:
modified_domain.append('|')
for f_name in multi_name:
modified_domain.append([
domain_tuple[0], domain_tuple[1],
f_name.strip()
])
modified_domain.append(domain_tuple)
e = expression.expression(modified_domain, self)
tables = e.get_tables()
where_clause, where_params = e.to_sql()
where_clause = [where_clause] if where_clause else []
else:
where_clause, where_params, tables = [], [], ['"%s"' % self._table]
return Query(tables, where_clause, where_params)
def test_basic_query(self):
query = Query(None, 'product_product')
query.add_table('product_template')
query.add_where("product_product.template_id = product_template.id")
# add inner join
alias = query.join("product_template", "categ_id", "product_category",
"id", "categ_id")
self.assertEqual(alias, 'product_template__categ_id')
# add left join
alias = query.left_join("product_product", "user_id", "res_user", "id",
"user_id")
self.assertEqual(alias, 'product_product__user_id')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"product_product", "product_template" JOIN "product_category" AS "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" AS "product_product__user_id" ON ("product_product"."user_id" = "product_product__user_id"."id")'
)
self.assertEqual(where_clause,
"product_product.template_id = product_template.id")
def _compute_application_count(self):
self.flush_model(['email_from'])
applicants = self.env['hr.applicant']
for applicant in self:
if applicant.email_from or applicant.partner_phone or applicant.partner_mobile:
applicants |= applicant
# Done via SQL since read_group does not support grouping by lowercase field
if applicants.ids:
query = Query(self.env.cr, self._table, self._table_query)
query.add_where('hr_applicant.id in %s', [tuple(applicants.ids)])
# Count into the companies that are selected from the multi-company widget
company_ids = self.env.context.get('allowed_company_ids')
if company_ids:
query.add_where('other.company_id in %s', [tuple(company_ids)])
self._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
# In case the applicant phone or mobile is configured in wrong field
query_str = """
SELECT hr_applicant.id as appl_id,
COUNT(other.id) as count
FROM hr_applicant
JOIN hr_applicant other ON LOWER(other.email_from) = LOWER(hr_applicant.email_from)
OR other.partner_phone = hr_applicant.partner_phone OR other.partner_phone = hr_applicant.partner_mobile
OR other.partner_mobile = hr_applicant.partner_mobile OR other.partner_mobile = hr_applicant.partner_phone
%(where)s
GROUP BY hr_applicant.id
""" % {
'where': ('WHERE %s' % where_clause) if where_clause else '',
}
self.env.cr.execute(query_str, where_clause_params)
application_data_mapped = dict(
(data['appl_id'], data['count'])
for data in self.env.cr.dictfetchall())
else:
application_data_mapped = dict()
for applicant in applicants:
applicant.application_count = application_data_mapped.get(
applicant.id, 1) - 1
(self - applicants).application_count = False
def test_table_expression(self):
query = Query(None, 'foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '"foo"')
query = Query(None, 'bar', 'SELECT id FROM foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '(SELECT id FROM foo) AS "bar"')
query = Query(None, 'foo')
query.add_table('bar', 'SELECT id FROM foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '"foo", (SELECT id FROM foo) AS "bar"')
query = Query(None, 'foo')
query.join('foo', 'bar_id', 'SELECT id FROM foo', 'id', 'bar')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"foo" JOIN (SELECT id FROM foo) AS "foo__bar" ON ("foo"."bar_id" = "foo__bar"."id")'
)
def test_query_chained_explicit_joins(self):
query = Query()
query.add_table('product_product')
query.add_table('product_template')
query.where_clause.append(
"product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id",
"id", "categ_id"),
implicit=False,
outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id",
"id", "user_id"),
implicit=False,
outer=True) # CHAINED outer join
self.assertEqual(
query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")"""
.strip())
self.assertEqual(
query.get_sql()[1].strip(),
"""product_product.template_id = product_template.id""".strip())
class expression(object):
""" Parse a domain expression
Use a real polish notation
Leafs are still in a ('foo', '=', 'bar') format
For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
"""
def __init__(self, domain, model, alias=None, query=None):
""" Initialize expression object and automatically parse the expression
right after initialization.
:param domain: expression (using domain ('foo', '=', 'bar') format)
:param model: root model
:param alias: alias for the model table if query is provided
:param query: optional query object holding the final result
:attr root_model: base model for the query
:attr expression: the domain to parse, normalized and prepared
:attr result: the result of the parsing, as a pair (query, params)
:attr query: Query object holding the final result
"""
self._unaccent = get_unaccent_wrapper(model._cr)
self.root_model = model
self.root_alias = alias or model._table
# normalize and prepare the expression for parsing
self.expression = distribute_not(normalize_domain(domain))
# this object handles all the joins
self.query = Query(model.env.cr, model._table, model._table_query) if query is None else query
# parse the domain expression
self.parse()
# ----------------------------------------
# Leafs management
# ----------------------------------------
def get_tables(self):
warnings.warn("deprecated expression.get_tables(), use expression.query instead",
DeprecationWarning)
return self.query.tables
# ----------------------------------------
# Parsing
# ----------------------------------------
def parse(self):
""" Transform the leaves of the expression
The principle is to pop elements from a leaf stack one at a time.
Each leaf is processed. The processing is a if/elif list of various
cases that appear in the leafs (many2one, function fields, ...).
Three things can happen as a processing result:
- the leaf is a logic operator, and updates the result stack
accordingly;
- the leaf has been modified and/or new leafs have to be introduced
in the expression; they are pushed into the leaf stack, to be
processed right after;
- the leaf is converted to SQL and added to the result stack
Here is a suggested execution:
step stack result_stack
['&', A, B] []
substitute B ['&', A, B1] []
convert B1 in SQL ['&', A] ["B1"]
substitute A ['&', '|', A1, A2] ["B1"]
convert A2 in SQL ['&', '|', A1] ["B1", "A2"]
convert A1 in SQL ['&', '|'] ["B1", "A2", "A1"]
apply operator OR ['&'] ["B1", "A1 or A2"]
apply operator AND [] ["(A1 or A2) and B1"]
Some internal var explanation:
:var list path: left operand seen as a sequence of field names
("foo.bar" -> ["foo", "bar"])
:var obj model: model object, model containing the field
(the name provided in the left operand)
:var obj field: the field corresponding to `path[0]`
:var obj column: the column corresponding to `path[0]`
:var obj comodel: relational model of field (field.comodel)
(res_partner.bank_ids -> res.partner.bank)
"""
cr, uid, context, su = self.root_model.env.args
def to_ids(value, comodel, leaf):
""" Normalize a single id or name, or a list of those, into a list of ids
:param {int,long,basestring,list,tuple} value:
if int, long -> return [value]
if basestring, convert it into a list of basestrings, then
if list of basestring ->
perform a name_search on comodel for each name
return the list of related ids
"""
names = []
if isinstance(value, str):
names = [value]
elif value and isinstance(value, (tuple, list)) and all(isinstance(item, str) for item in value):
names = value
elif isinstance(value, int):
if not value:
# given this nonsensical domain, it is generally cheaper to
# interpret False as [], so that "X child_of False" will
# match nothing
_logger.warning("Unexpected domain [%s], interpreted as False", leaf)
return []
return [value]
if names:
return list({
rid
for name in names
for rid in comodel._name_search(name, [], 'ilike', limit=None)
})
return list(value)
def child_of_domain(left, ids, left_model, parent=None, prefix=''):
""" Return a domain implementing the child_of operator for [(left,child_of,ids)],
either as a range using the parent_path tree lookup field
(when available), or as an expanded [(left,in,child_ids)] """
if not ids:
return [FALSE_LEAF]
if left_model._parent_store:
domain = OR([
[('parent_path', '=like', rec.parent_path + '%')]
for rec in left_model.browse(ids)
])
else:
# recursively retrieve all children nodes with sudo(); the
# filtering of forbidden records is done by the rest of the
# domain
parent_name = parent or left_model._parent_name
child_ids = set()
records = left_model.sudo().browse(ids)
while records:
child_ids.update(records._ids)
records = records.search([(parent_name, 'in', records.ids)], order='id')
domain = [('id', 'in', list(child_ids))]
if prefix:
return [(left, 'in', left_model._search(domain, order='id'))]
return domain
def parent_of_domain(left, ids, left_model, parent=None, prefix=''):
""" Return a domain implementing the parent_of operator for [(left,parent_of,ids)],
either as a range using the parent_path tree lookup field
(when available), or as an expanded [(left,in,parent_ids)] """
if not ids:
return [FALSE_LEAF]
if left_model._parent_store:
parent_ids = [
int(label)
for rec in left_model.browse(ids)
for label in rec.parent_path.split('/')[:-1]
]
domain = [('id', 'in', parent_ids)]
else:
# recursively retrieve all parent nodes with sudo() to avoid
# access rights errors; the filtering of forbidden records is
# done by the rest of the domain
parent_name = parent or left_model._parent_name
parent_ids = set()
records = left_model.sudo().browse(ids)
while records:
parent_ids.update(records._ids)
records = records[parent_name]
domain = [('id', 'in', list(parent_ids))]
if prefix:
return [(left, 'in', left_model._search(domain, order='id'))]
return domain
HIERARCHY_FUNCS = {'child_of': child_of_domain,
'parent_of': parent_of_domain}
def pop():
""" Pop a leaf to process. """
return stack.pop()
def push(leaf, model, alias, internal=False):
""" Push a leaf to be processed right after. """
leaf = normalize_leaf(leaf)
check_leaf(leaf, internal)
stack.append((leaf, model, alias))
def pop_result():
return result_stack.pop()
def push_result(query, params):
result_stack.append((query, params))
# process domain from right to left; stack contains domain leaves, in
# the form: (leaf, corresponding model, corresponding table alias)
stack = []
for leaf in self.expression:
push(leaf, self.root_model, self.root_alias)
# stack of SQL expressions in the form: (expr, params)
result_stack = []
while stack:
# Get the next leaf to process
leaf, model, alias = pop()
# ----------------------------------------
# SIMPLE CASE
# 1. leaf is an operator
# 2. leaf is a true/false leaf
# -> convert and add directly to result
# ----------------------------------------
if is_operator(leaf):
if leaf == NOT_OPERATOR:
expr, params = pop_result()
push_result('(NOT (%s))' % expr, params)
else:
ops = {AND_OPERATOR: '(%s AND %s)', OR_OPERATOR: '(%s OR %s)'}
lhs, lhs_params = pop_result()
rhs, rhs_params = pop_result()
push_result(ops[leaf] % (lhs, rhs), lhs_params + rhs_params)
continue
if is_boolean(leaf):
expr, params = self.__leaf_to_sql(leaf, model, alias)
push_result(expr, params)
continue
# Get working variables
left, operator, right = leaf
path = left.split('.', 1)
field = model._fields.get(path[0])
comodel = model.env.get(getattr(field, 'comodel_name', None))
# ----------------------------------------
# FIELD NOT FOUND
# -> from inherits'd fields -> work on the related model, and add
# a join condition
# -> ('id', 'child_of', '..') -> use a 'to_ids'
# -> but is one on the _log_access special fields, add directly to
# result
# TODO: make these fields explicitly available in self.columns instead!
# -> else: crash
# ----------------------------------------
if not field:
raise ValueError("Invalid field %s.%s in leaf %s" % (model._name, path[0], str(leaf)))
elif field.inherited:
parent_model = model.env[field.related_field.model_name]
parent_fname = model._inherits[parent_model._name]
parent_alias = self.query.left_join(
alias, parent_fname, parent_model._table, 'id', parent_fname,
)
push(leaf, parent_model, parent_alias)
elif left == 'id' and operator in HIERARCHY_FUNCS:
ids2 = to_ids(right, model, leaf)
dom = HIERARCHY_FUNCS[operator](left, ids2, model)
for dom_leaf in dom:
push(dom_leaf, model, alias)
# ----------------------------------------
# PATH SPOTTED
# -> many2one or one2many with _auto_join:
# - add a join, then jump into linked column: column.remaining on
# src_table is replaced by remaining on dst_table, and set for re-evaluation
# - if a domain is defined on the column, add it into evaluation
# on the relational table
# -> many2one, many2many, one2many: replace by an equivalent computed
# domain, given by recursively searching on the remaining of the path
# -> note: hack about columns.property should not be necessary anymore
# as after transforming the column, it will go through this loop once again
# ----------------------------------------
elif len(path) > 1 and field.store and field.type == 'many2one' and field.auto_join:
# res_partner.state_id = res_partner__state_id.id
coalias = self.query.left_join(
alias, path[0], comodel._table, 'id', path[0],
)
push((path[1], operator, right), comodel, coalias)
elif len(path) > 1 and field.store and field.type == 'one2many' and field.auto_join:
# use a subquery bypassing access rules and business logic
domain = [(path[1], operator, right)] + field.get_domain_list(model)
query = comodel.with_context(**field.context)._where_calc(domain)
subquery, subparams = query.select('"%s"."%s"' % (comodel._table, field.inverse_name))
push(('id', 'inselect', (subquery, subparams)), model, alias, internal=True)
elif len(path) > 1 and field.store and field.auto_join:
raise NotImplementedError('auto_join attribute not supported on field %s' % field)
elif len(path) > 1 and field.store and field.type == 'many2one':
right_ids = comodel.with_context(active_test=False)._search([(path[1], operator, right)], order='id')
push((path[0], 'in', right_ids), model, alias)
# Making search easier when there is a left operand as one2many or many2many
elif len(path) > 1 and field.store and field.type in ('many2many', 'one2many'):
right_ids = comodel.with_context(**field.context)._search([(path[1], operator, right)], order='id')
push((path[0], 'in', right_ids), model, alias)
elif not field.store:
# Non-stored field should provide an implementation of search.
if not field.search:
# field does not support search!
_logger.error("Non-stored field %s cannot be searched.", field)
if _logger.isEnabledFor(logging.DEBUG):
_logger.debug(''.join(traceback.format_stack()))
# Ignore it: generate a dummy leaf.
domain = []
else:
# Let the field generate a domain.
if len(path) > 1:
right = comodel._search([(path[1], operator, right)], order='id')
operator = 'in'
domain = field.determine_domain(model, operator, right)
model._flush_search(domain, order='id')
for elem in normalize_domain(domain):
push(elem, model, alias, internal=True)
# -------------------------------------------------
# RELATIONAL FIELDS
# -------------------------------------------------
# Applying recursivity on field(one2many)
elif field.type == 'one2many' and operator in HIERARCHY_FUNCS:
ids2 = to_ids(right, comodel, leaf)
if field.comodel_name != model._name:
dom = HIERARCHY_FUNCS[operator](left, ids2, comodel, prefix=field.comodel_name)
else:
dom = HIERARCHY_FUNCS[operator]('id', ids2, model, parent=left)
for dom_leaf in dom:
push(dom_leaf, model, alias)
elif field.type == 'one2many':
domain = field.get_domain_list(model)
inverse_is_int = comodel._fields[field.inverse_name].type in ('integer', 'many2one_reference')
unwrap_inverse = (lambda ids: ids) if inverse_is_int else (lambda recs: recs.ids)
if right is not False:
# determine ids2 in comodel
if isinstance(right, str):
op2 = (TERM_OPERATORS_NEGATION[operator]
if operator in NEGATIVE_TERM_OPERATORS else operator)
ids2 = comodel._name_search(right, domain or [], op2, limit=None)
elif isinstance(right, collections.abc.Iterable):
ids2 = right
else:
ids2 = [right]
if inverse_is_int and domain:
ids2 = comodel._search([('id', 'in', ids2)] + domain, order='id')
if isinstance(ids2, Query) and comodel._fields[field.inverse_name].store:
op1 = 'not inselect' if operator in NEGATIVE_TERM_OPERATORS else 'inselect'
subquery, subparams = ids2.subselect('"%s"."%s"' % (comodel._table, field.inverse_name))
push(('id', op1, (subquery, subparams)), model, alias, internal=True)
elif ids2 and comodel._fields[field.inverse_name].store:
op1 = 'not inselect' if operator in NEGATIVE_TERM_OPERATORS else 'inselect'
subquery = 'SELECT "%s" FROM "%s" WHERE "id" IN %%s' % (field.inverse_name, comodel._table)
subparams = [tuple(ids2)]
push(('id', op1, (subquery, subparams)), model, alias, internal=True)
else:
# determine ids1 in model related to ids2
recs = comodel.browse(ids2).sudo().with_context(prefetch_fields=False)
ids1 = unwrap_inverse(recs.mapped(field.inverse_name))
# rewrite condition in terms of ids1
op1 = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
push(('id', op1, ids1), model, alias)
else:
if comodel._fields[field.inverse_name].store and not (inverse_is_int and domain):
# rewrite condition to match records with/without lines
op1 = 'inselect' if operator in NEGATIVE_TERM_OPERATORS else 'not inselect'
subquery = 'SELECT "%s" FROM "%s" where "%s" is not null' % (field.inverse_name, comodel._table, field.inverse_name)
push(('id', op1, (subquery, [])), model, alias, internal=True)
else:
comodel_domain = [(field.inverse_name, '!=', False)]
if inverse_is_int and domain:
comodel_domain += domain
recs = comodel.search(comodel_domain, order='id').sudo().with_context(prefetch_fields=False)
# determine ids1 = records with lines
ids1 = unwrap_inverse(recs.mapped(field.inverse_name))
# rewrite condition to match records with/without lines
op1 = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
push(('id', op1, ids1), model, alias)
elif field.type == 'many2many':
rel_table, rel_id1, rel_id2 = field.relation, field.column1, field.column2
if operator in HIERARCHY_FUNCS:
# determine ids2 in comodel
ids2 = to_ids(right, comodel, leaf)
domain = HIERARCHY_FUNCS[operator]('id', ids2, comodel)
ids2 = comodel._search(domain, order='id')
# rewrite condition in terms of ids2
if comodel == model:
push(('id', 'in', ids2), model, alias)
else:
subquery = 'SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % (rel_id1, rel_table, rel_id2)
push(('id', 'inselect', (subquery, [tuple(ids2) or (None,)])), model, alias, internal=True)
elif right is not False:
# determine ids2 in comodel
if isinstance(right, str):
domain = field.get_domain_list(model)
op2 = (TERM_OPERATORS_NEGATION[operator]
if operator in NEGATIVE_TERM_OPERATORS else operator)
ids2 = comodel._name_search(right, domain or [], op2, limit=None)
elif isinstance(right, collections.abc.Iterable):
ids2 = right
else:
ids2 = [right]
if isinstance(ids2, Query):
# rewrite condition in terms of ids2
subop = 'not inselect' if operator in NEGATIVE_TERM_OPERATORS else 'inselect'
subquery, subparams = ids2.subselect()
query = 'SELECT "%s" FROM "%s" WHERE "%s" IN (%s)' % (rel_id1, rel_table, rel_id2, subquery)
push(('id', subop, (query, subparams)), model, alias, internal=True)
else:
# rewrite condition in terms of ids2
subop = 'not inselect' if operator in NEGATIVE_TERM_OPERATORS else 'inselect'
subquery = 'SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % (rel_id1, rel_table, rel_id2)
ids2 = tuple(it for it in ids2 if it) or (None,)
push(('id', subop, (subquery, [ids2])), model, alias, internal=True)
else:
# rewrite condition to match records with/without relations
op1 = 'inselect' if operator in NEGATIVE_TERM_OPERATORS else 'not inselect'
subquery = 'SELECT "%s" FROM "%s" where "%s" is not null' % (rel_id1, rel_table, rel_id1)
push(('id', op1, (subquery, [])), model, alias, internal=True)
elif field.type == 'many2one':
if operator in HIERARCHY_FUNCS:
ids2 = to_ids(right, comodel, leaf)
if field.comodel_name != model._name:
dom = HIERARCHY_FUNCS[operator](left, ids2, comodel, prefix=field.comodel_name)
else:
dom = HIERARCHY_FUNCS[operator]('id', ids2, model, parent=left)
for dom_leaf in dom:
push(dom_leaf, model, alias)
else:
def _get_expression(comodel, left, right, operator):
#Special treatment to ill-formed domains
operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
if isinstance(right, tuple):
right = list(right)
if (not isinstance(right, list)) and operator in ['not in', 'in']:
operator = dict_op[operator]
elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
operator = dict_op[operator]
res_ids = comodel.with_context(active_test=False)._name_search(right, [], operator, limit=None)
if operator in NEGATIVE_TERM_OPERATORS:
res_ids = list(res_ids) + [False] # TODO this should not be appended if False was in 'right'
return left, 'in', res_ids
# resolve string-based m2o criterion into IDs
if isinstance(right, str) or \
isinstance(right, (tuple, list)) and right and all(isinstance(item, str) for item in right):
push(_get_expression(comodel, left, right, operator), model, alias)
else:
# right == [] or right == False and all other cases are handled by __leaf_to_sql()
expr, params = self.__leaf_to_sql(leaf, model, alias)
push_result(expr, params)
# -------------------------------------------------
# BINARY FIELDS STORED IN ATTACHMENT
# -> check for null only
# -------------------------------------------------
elif field.type == 'binary' and field.attachment:
if operator in ('=', '!=') and not right:
inselect_operator = 'inselect' if operator in NEGATIVE_TERM_OPERATORS else 'not inselect'
subselect = "SELECT res_id FROM ir_attachment WHERE res_model=%s AND res_field=%s"
params = (model._name, left)
push(('id', inselect_operator, (subselect, params)), model, alias, internal=True)
else:
_logger.error("Binary field '%s' stored in attachment: ignore %s %s %s",
field.string, left, operator, reprlib.repr(right))
push(TRUE_LEAF, model, alias)
# -------------------------------------------------
# OTHER FIELDS
# -> datetime fields: manage time part of the datetime
# column when it is not there
# -> manage translatable fields
# -------------------------------------------------
else:
if field.type == 'datetime' and right:
if isinstance(right, str) and len(right) == 10:
if operator in ('>', '<='):
right += ' 23:59:59'
else:
right += ' 00:00:00'
push((left, operator, right), model, alias)
elif isinstance(right, date) and not isinstance(right, datetime):
if operator in ('>', '<='):
right = datetime.combine(right, time.max)
else:
right = datetime.combine(right, time.min)
push((left, operator, right), model, alias)
else:
expr, params = self.__leaf_to_sql(leaf, model, alias)
push_result(expr, params)
elif field.translate is True and right:
need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
if need_wildcard:
right = '%%%s%%' % right
if sql_operator in ('in', 'not 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')
push_result(f"{left} {sql_operator} {instr}", [right])
else:
expr, params = self.__leaf_to_sql(leaf, model, alias)
push_result(expr, params)
# ----------------------------------------
# END OF PARSING FULL DOMAIN
# -> put result in self.result and self.query
# ----------------------------------------
[self.result] = result_stack
where_clause, where_params = self.result
self.query.add_where(where_clause, where_params)
def __leaf_to_sql(self, leaf, model, alias):
left, operator, right = leaf
# final sanity checks - should never fail
assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
"Invalid operator %r in domain term %r" % (operator, leaf)
assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._fields, \
"Invalid field %r in domain term %r" % (left, leaf)
assert not isinstance(right, BaseModel), \
"Invalid value %r in domain term %r" % (right, leaf)
table_alias = '"%s"' % alias
if leaf == TRUE_LEAF:
query = 'TRUE'
params = []
elif leaf == FALSE_LEAF:
query = 'FALSE'
params = []
elif operator == 'inselect':
query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
params = list(right[1])
elif operator == 'not inselect':
query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
params = list(right[1])
elif operator in ['in', 'not in']:
# Two cases: right is a boolean or a list. The boolean case is an
# abuse and handled for backward compatibility.
if isinstance(right, bool):
_logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
if (operator == 'in' and right) or (operator == 'not in' and not right):
query = '(%s."%s" IS NOT NULL)' % (table_alias, left)
else:
query = '(%s."%s" IS NULL)' % (table_alias, left)
params = []
elif isinstance(right, Query):
subquery, subparams = right.subselect()
query = '(%s."%s" %s (%s))' % (table_alias, left, operator, subquery)
params = subparams
elif isinstance(right, (list, tuple)):
if model._fields[left].type == "boolean":
params = [it for it in (True, False) if it in right]
check_null = False in right
else:
params = [it for it in right if it != False]
check_null = len(params) < len(right)
if params:
if left == 'id':
instr = ','.join(['%s'] * len(params))
else:
field = model._fields[left]
instr = ','.join([field.column_format] * len(params))
params = [field.convert_to_column(p, model, validate=False) for p in params]
query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
else:
# The case for (left, 'in', []) or (left, 'not in', []).
query = 'FALSE' if operator == 'in' else 'TRUE'
if (operator == 'in' and check_null) or (operator == 'not in' and not check_null):
query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
elif operator == 'not in' and check_null:
query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
else: # Must not happen
raise ValueError("Invalid domain term %r" % (leaf,))
elif left in model and model._fields[left].type == "boolean" and ((operator == '=' and right is False) or (operator == '!=' and right is True)):
query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
params = []
elif (right is False or right is None) and (operator == '='):
query = '%s."%s" IS NULL ' % (table_alias, left)
params = []
elif left in model and model._fields[left].type == "boolean" and ((operator == '!=' and right is False) or (operator == '==' and right is True)):
query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
params = []
elif (right is False or right is None) and (operator == '!='):
query = '%s."%s" IS NOT NULL' % (table_alias, left)
params = []
elif operator == '=?':
if right is False or right is None:
# '=?' is a short-circuit that makes the term TRUE if right is None or False
query = 'TRUE'
params = []
else:
# '=?' behaves like '=' in other cases
query, params = self.__leaf_to_sql((left, '=', right), model, alias)
else:
need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
cast = '::text' if sql_operator.endswith('like') else ''
if left not in model:
raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
format = '%s' if need_wildcard else model._fields[left].column_format
unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
column = '%s.%s' % (table_alias, _quote(left))
query = '(%s %s %s)' % (unaccent(column + cast), sql_operator, unaccent(format))
if (need_wildcard and not right) or (right and operator in NEGATIVE_TERM_OPERATORS):
query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
if need_wildcard:
params = ['%%%s%%' % pycompat.to_text(right)]
else:
field = model._fields[left]
params = [field.convert_to_column(right, model, validate=False)]
return query, params
def to_sql(self):
warnings.warn("deprecated expression.to_sql(), use expression.query instead",
DeprecationWarning)
return self.result
def _read_from_database(self, field_names, inherited_field_names=[]):
""" Read the given fields of the records in ``self`` from the database,
and store them in cache. Access errors are also stored in cache.
:param field_names: list of column names of model ``self``; all those
fields are guaranteed to be read
:param inherited_field_names: list of column names from parent
models; some of those fields may not be read
"""
if not self:
return
env = self.env
cr, user, context = env.args
# make a query object for selecting ids, and apply security rules to it
param_ids = object()
query = Query(['"%s"' % self._table],
['"%s".id IN %%s' % self._table], [param_ids])
self._apply_ir_rules(query, 'read')
# determine the fields that are stored as columns in tables; ignore 'id'
fields_pre = [
field
for field in (self._fields[name]
for name in field_names + inherited_field_names)
if field.name != 'id'
if field.base_field.store and field.base_field.column_type
if not (
field.inherited and callable(field.base_field.translate))
]
# the query may involve several tables: we need fully-qualified names
def qualify(field):
col = field.name
res = self._inherits_join_calc(self._table, field.name, query)
if field.type == 'binary' and (context.get('bin_size') or
context.get('bin_size_' + col)):
# PG 9.2 introduces conflicting pg_size_pretty(numeric) -> need ::cast
res = 'pg_size_pretty(length(%s)::bigint)' % res
return '%s as "%s"' % (res, col)
qual_names = [
qualify(name) for name in [self._fields['id']] + fields_pre
]
# determine the actual query to execute
from_clause, where_clause, params = query.get_sql()
query_str = "SELECT %s FROM %s WHERE %s" % (
",".join(qual_names), from_clause, where_clause)
result = []
param_pos = params.index(param_ids)
for sub_ids in cr.split_for_in_conditions(self.ids):
params[param_pos] = tuple(sub_ids)
cr.execute(query_str, params)
result.extend(cr.dictfetchall())
ids = [vals['id'] for vals in result]
fetched = self.browse(ids)
if ids:
# translate the fields if necessary
if context.get('lang'):
for field in fields_pre:
if not field.inherited and callable(field.translate):
name = field.name
translate = field.get_trans_func(fetched)
for vals in result:
vals[name] = translate(vals['id'], vals[name])
# store result in cache
for vals in result:
record = self.browse(vals.pop('id'), self._prefetch)
record._cache.update(
record._convert_to_cache(vals, validate=False))
# determine the fields that must be processed now;
# for the sake of simplicity, we ignore inherited fields
for name in field_names:
field = self._fields[name]
if not field.column_type:
field.read(fetched)
# Warn about deprecated fields now that fields_pre and fields_post are computed
for name in field_names:
field = self._fields[name]
if field.deprecated:
_logger.warning('Field %s is deprecated: %s', field,
field.deprecated)
# store failed values in cache for the records that could not be read
missing = self - fetched
if missing:
extras = fetched - self
if extras:
raise AccessError(
_("Database fetch misses ids ({}) and has extra ids ({}), may be caused by a type incoherence in a previous request"
).format(
missing._ids,
extras._ids,
))
# mark non-existing records in missing
forbidden = missing.exists()
if forbidden:
_logger.info(
_('The requested operation cannot be completed due to record rules: Document type: %s, Operation: %s, Records: %s, User: %s') % \
(self._name, 'read', ','.join([str(r.id) for r in self][:6]), self._uid))
# store an access error exception in existing records
exc = AccessError(
# _('The requested operation cannot be completed due to security restrictions. Please contact your system administrator.\n\n(Document type: %s, Operation: %s)') % \
# (self._name, 'read')
# Modificación del mensaje de error: José Candelas
_('The requested operation cannot be completed due to security restrictions. Please contact your system administrator.\n\n(Document type: %s, Operation: %s, Forbidden: %s)') % \
(self._name, 'read', forbidden)
)
self.env.cache.set_failed(forbidden, self._fields.values(),
exc)
def test_mixed_query_chained_explicit_implicit_joins(self):
query = Query(None, 'product_product')
query.add_table('product_template')
query.add_where("product_product.template_id = product_template.id")
# add inner join
alias = query.join("product_template", "categ_id", "product_category",
"id", "categ_id")
self.assertEqual(alias, 'product_template__categ_id')
# add CHAINED left join
alias = query.left_join("product_template__categ_id", "user_id",
"res_user", "id", "user_id")
self.assertEqual(alias, 'product_template__categ_id__user_id')
# additional implicit join
query.add_table('account.account')
query.add_where(
"product_category.expense_account_id = account_account.id")
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"product_product", "product_template", "account.account" JOIN "product_category" AS "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" AS "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")'
)
self.assertEqual(
where_clause,
"product_product.template_id = product_template.id AND product_category.expense_account_id = account_account.id"
)
def test_raise_missing_lhs(self):
query = Query(None, 'product_product')
with self.assertRaises(AssertionError):
query.join("product_template", "categ_id", "product_category",
"id", "categ_id")