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 _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_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_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 _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 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_raise_missing_lhs(self):
query = Query()
query.tables.append('"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 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 _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 _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 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())
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_raise_missing_lhs(self):
query = Query(None, 'product_product')
with self.assertRaises(AssertionError):
query.join("product_template", "categ_id", "product_category",
"id", "categ_id")
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)
