Odoo 14 POS透视报表新增字段报psycopg2.ProgrammingError如何解决

核心错误原因 #

报错提示s.partner_state_id不存在是因为两个逻辑错误：

1. 你给report.pos.order模型加的partner_state_id字段属于透视报表视图模型，而SQL语句里的s别名对应的是pos_order（POS订单原始表），pos_order表本身没有partner_state_id字段，直接引用自然找不到。
2. 关联表错配：partner_id.state_id关联的是res.country.state（国家省州表），你写的res_company_shop是完全不相关的表，关联逻辑错误。

正确修改代码 #

调整SQL查询逻辑 #

report.pos.order是SQL视图模型，字段值需要从SQL查询返回，无需设置store=True的关联字段，三个方法修改如下：

class PosOrderReport(models.Model):
    _inherit = 'report.pos.order'

    partner_state_id = fields.Many2one('res.country.state', string="State", readonly=True)

    def _select(self):
        return """
            SELECT
                MIN(l.id) AS id,
                COUNT(*) AS nbr_lines,
                s.date_order AS date,
                SUM(l.qty) AS product_qty,
                SUM(l.qty * l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS price_sub_total,
                SUM(ROUND((l.qty * l.price_unit) * (100 - l.discount) / 100 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END, cu.decimal_places)) AS price_total,
                SUM((l.qty * l.price_unit) * (l.discount / 100) / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS total_discount,
                CASE
                    WHEN SUM(l.qty * u.factor) = 0 THEN NULL
                    ELSE (SUM(l.qty*l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)/SUM(l.qty * u.factor))::decimal
                END AS average_price,
                SUM(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') AS INT)) AS delay_validation,
                s.id as order_id,
                s.partner_id AS partner_id,
                s.user_id AS user_id,
                rp.state_id AS partner_state_id,
                s.company_id AS company_id,
                l.route_id AS route_id,
                s.sale_journal AS journal_id,
                l.product_id AS product_id,
                pt.categ_id AS product_categ_id,
                p.product_tmpl_id,
                ps.config_id,
                pt.pos_categ_id,
                s.pricelist_id,
                s.session_id,
                s.account_move IS NOT NULL AS invoiced
        """

    def _from(self):
        return """
            FROM pos_order_line AS l
                INNER JOIN pos_order s ON (s.id=l.order_id)
                LEFT JOIN res_partner rp ON (s.partner_id = rp.id)
                LEFT JOIN product_product p ON (l.product_id=p.id)
                LEFT JOIN product_template pt ON (p.product_tmpl_id=pt.id)
                LEFT JOIN uom_uom u ON (u.id=pt.uom_id)
                LEFT JOIN pos_session ps ON (s.session_id=ps.id)
                LEFT JOIN res_company co ON (s.company_id=co.id)
                LEFT JOIN stock_location_route r ON (l.route_id=r.id)
                LEFT JOIN res_currency cu ON (co.currency_id=cu.id)
        """

    def _group_by(self):
        return """
            GROUP BY
                s.id, s.date_order, s.partner_id,s.state, pt.categ_id,
                s.user_id, s.company_id, rp.state_id, l.route_id, s.sale_journal,
                s.pricelist_id, s.account_move, s.create_date, s.session_id,
                l.product_id,
                pt.categ_id, pt.pos_categ_id,
                p.product_tmpl_id,
                ps.config_id
        """

生效操作 #

修改完代码后升级对应模块，Odoo会自动重建report.pos.order的SQL视图，字段即可正常在透视表中使用。

内容的提问来源于stack exchange，提问作者K.ju