changeset a88e6cd681a7 in modules/sale:default details: https://hg.tryton.org/modules/sale?cmd=changeset;node=a88e6cd681a7 description: Use CTE for currency rate in reporting queries
The query plan generated by PostgreSQL is much faster this way. issue9950 review343221002 diffstat: sale_reporting.py | 129 ++++++++++++++++++++++++++++------------------------- 1 files changed, 67 insertions(+), 62 deletions(-) diffs (334 lines): diff -r 4c7b8b0d9060 -r a88e6cd681a7 sale_reporting.py --- a/sale_reporting.py Sun Dec 27 00:19:30 2020 +0100 +++ b/sale_reporting.py Sun Jan 17 00:58:44 2021 +0100 @@ -8,7 +8,7 @@ except ImportError: pygal = None from dateutil.relativedelta import relativedelta -from sql import Null, Literal, Column +from sql import Null, Literal, Column, With from sql.aggregate import Sum, Max, Min, Count from sql.conditionals import Coalesce from sql.functions import CurrentTimestamp, DateTrunc, Power, Ceil, Log @@ -54,10 +54,11 @@ @classmethod def table_query(cls): - from_item, tables = cls._joins() - return from_item.select(*cls._columns(tables), - where=cls._where(tables), - group_by=cls._group_by(tables)) + from_item, tables, withs = cls._joins() + return from_item.select(*cls._columns(tables, withs), + where=cls._where(tables, withs), + group_by=cls._group_by(tables, withs), + with_=withs.values()) @classmethod def _joins(cls): @@ -71,10 +72,11 @@ tables['line'] = line = Line.__table__() tables['line.sale'] = sale = Sale.__table__() tables['line.sale.company'] = company = Company.__table__() - currency_sale = Currency.currency_rate_sql() - tables['currency_sale'] = currency_sale - currency_company = Currency.currency_rate_sql() - tables['currency_company'] = currency_company + withs = {} + currency_sale = With(query=Currency.currency_rate_sql()) + withs['currency_sale'] = currency_sale + currency_company = With(query=Currency.currency_rate_sql()) + withs['currency_company'] = currency_company from_item = (line .join(sale, condition=line.sale == sale.id) @@ -91,21 +93,21 @@ & ((currency_company.end_date == Null) | (currency_company.end_date >= sale.sale_date)) )) - return from_item, tables + return from_item, tables, withs @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): line = tables['line'] sale = tables['line.sale'] - currency_company = tables['currency_company'] - currency_sale = tables['currency_sale'] + currency_company = withs['currency_company'] + currency_sale = withs['currency_sale'] quantity = Coalesce(line.actual_quantity, line.quantity) revenue = cls.revenue.sql_cast( Sum(quantity * line.unit_price * currency_company.rate / currency_sale.rate)) return [ - cls._column_id(tables).as_('id'), + cls._column_id(tables, withs).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'), @@ -116,17 +118,17 @@ ] @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): line = tables['line'] return Min(line.id) @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): sale = tables['line.sale'] return [sale.company] @classmethod - def _where(cls, tables): + def _where(cls, tables, withs): context = Transaction().context sale = tables['line.sale'] @@ -192,12 +194,12 @@ cls._order = [('date', 'ASC')] @classmethod - def _columns(cls, tables): - return super(AbstractTimeseries, cls)._columns(tables) + [ - cls._column_date(tables).as_('date')] + def _columns(cls, tables, withs): + return super(AbstractTimeseries, cls)._columns(tables, withs) + [ + cls._column_date(tables, withs).as_('date')] @classmethod - def _column_date(cls, tables): + def _column_date(cls, tables, withs): context = Transaction().context sale = tables['line.sale'] date = DateTrunc(context.get('period'), sale.sale_date) @@ -205,9 +207,9 @@ return date @classmethod - def _group_by(cls, tables): - return super(AbstractTimeseries, cls)._group_by(tables) + [ - cls._column_date(tables)] + def _group_by(cls, tables, withs): + return super(AbstractTimeseries, cls)._group_by(tables, withs) + [ + cls._column_date(tables, withs)] class Context(ModelView): @@ -276,15 +278,16 @@ customer = fields.Many2One('party.party', "Customer") @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): sale = tables['line.sale'] - return super(CustomerMixin, cls)._columns(tables) + [ + return super(CustomerMixin, cls)._columns(tables, withs) + [ sale.party.as_('customer')] @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): sale = tables['line.sale'] - return super(CustomerMixin, cls)._group_by(tables) + [sale.party] + return super(CustomerMixin, cls)._group_by(tables, withs) + [ + sale.party] def get_rec_name(self, name): return self.customer.rec_name @@ -303,7 +306,7 @@ cls._order.insert(0, ('customer', 'ASC')) @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): sale = tables['line.sale'] return sale.party @@ -318,20 +321,21 @@ product = fields.Many2One('product.product', "Product") @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): line = tables['line'] - return super(ProductMixin, cls)._columns(tables) + [ + return super(ProductMixin, cls)._columns(tables, withs) + [ line.product.as_('product')] @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): line = tables['line'] - return super(ProductMixin, cls)._group_by(tables) + [line.product] + return super(ProductMixin, cls)._group_by(tables, withs) + [ + line.product] @classmethod - def _where(cls, tables): + def _where(cls, tables, withs): line = tables['line'] - where = super(ProductMixin, cls)._where(tables) + where = super(ProductMixin, cls)._where(tables, withs) where &= line.product != Null return where @@ -352,7 +356,7 @@ cls._order.insert(0, ('product', 'ASC')) @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): line = tables['line'] return line.product @@ -371,7 +375,7 @@ pool = Pool() Product = pool.get('product.product') TemplateCategory = pool.get('product.template-product.category.all') - from_item, tables = super(CategoryMixin, cls)._joins() + from_item, tables, withs = super(CategoryMixin, cls)._joins() if 'line.product' not in tables: product = Product.__table__() tables['line.product'] = product @@ -385,16 +389,16 @@ from_item = (from_item .join(template_category, condition=product.template == template_category.template)) - return from_item, tables + return from_item, tables, withs @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): template_category = tables['line.product.template_category'] - return super(CategoryMixin, cls)._columns(tables) + [ + return super(CategoryMixin, cls)._columns(tables, withs) + [ template_category.category.as_('category')] @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): pool = Pool() Category = pool.get('product.category') category = Category.__table__() @@ -407,15 +411,15 @@ return Min(line.id * nb_category + template_category.id) @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): template_category = tables['line.product.template_category'] - return super(CategoryMixin, cls)._group_by(tables) + [ + return super(CategoryMixin, cls)._group_by(tables, withs) + [ template_category.category] @classmethod - def _where(cls, tables): + def _where(cls, tables, withs): template_category = tables['line.product.template_category'] - where = super(CategoryMixin, cls)._where(tables) + where = super(CategoryMixin, cls)._where(tables, withs) where &= template_category.category != Null return where @@ -436,7 +440,7 @@ cls._order.insert(0, ('category', 'ASC')) @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): template_category = tables['line.product.template_category'] return template_category.category @@ -566,30 +570,31 @@ def _joins(cls): pool = Pool() Address = pool.get('party.address') - from_item, tables = super(CountryMixin, cls)._joins() + from_item, tables, withs = super(CountryMixin, cls)._joins() if 'line.sale.shipment_address' not in tables: address = Address.__table__() tables['line.sale.shipment_address'] = address sale = tables['line.sale'] from_item = (from_item .join(address, condition=sale.shipment_address == address.id)) - return from_item, tables + return from_item, tables, withs @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): address = tables['line.sale.shipment_address'] - return super(CountryMixin, cls)._columns(tables) + [ + return super(CountryMixin, cls)._columns(tables, withs) + [ address.country.as_('country')] @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): address = tables['line.sale.shipment_address'] - return super(CountryMixin, cls)._group_by(tables) + [address.country] + return super(CountryMixin, cls)._group_by(tables, withs) + [ + address.country] @classmethod - def _where(cls, tables): + def _where(cls, tables, withs): address = tables['line.sale.shipment_address'] - where = super(CountryMixin, cls)._where(tables) + where = super(CountryMixin, cls)._where(tables, withs) where &= address.country != Null return where @@ -607,7 +612,7 @@ cls._order.insert(0, ('country', 'ASC')) @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): address = tables['line.sale.shipment_address'] return address.country @@ -625,21 +630,21 @@ subdivision = fields.Many2One('country.subdivision', "Subdivision") @classmethod - def _columns(cls, tables): + def _columns(cls, tables, withs): address = tables['line.sale.shipment_address'] - return super(SubdivisionMixin, cls)._columns(tables) + [ + return super(SubdivisionMixin, cls)._columns(tables, withs) + [ address.subdivision.as_('subdivision')] @classmethod - def _group_by(cls, tables): + def _group_by(cls, tables, withs): address = tables['line.sale.shipment_address'] - return super(SubdivisionMixin, cls)._group_by(tables) + [ + return super(SubdivisionMixin, cls)._group_by(tables, withs) + [ address.subdivision] @classmethod - def _where(cls, tables): + def _where(cls, tables, withs): address = tables['line.sale.shipment_address'] - where = super(SubdivisionMixin, cls)._where(tables) + where = super(SubdivisionMixin, cls)._where(tables, withs) where &= address.subdivision != Null return where @@ -658,7 +663,7 @@ cls._order.insert(0, ('subdivision', 'ASC')) @classmethod - def _column_id(cls, tables): + def _column_id(cls, tables, withs): address = tables['line.sale.shipment_address'] return address.subdivision