向Odoo Excel报表添加商品条码失败 排查相关SQL代码错误

• 1. 关联字段取值语法错误：原生SQL不支持Odoo ORM的多层点链取值写法，你新增的aml.product_id.barcode AS product_barcode不符合SQL规范。account_move_line表仅存储product_id外键ID，没有嵌套的barcode属性，需要额外关联product_product表才能获取商品条码值。
• 2. 遗漏关联表：查询逻辑中使用了afr.name获取对账编号，但FROM子句中没有关联account_full_reconcile表的语句，执行时会抛出表不存在的错误。

def _sql_get_line_for_report(self, type_l, report_object=None):
    self.env['account.move.line'].check_access_rights('read')
    query = """SELECT
                raml.report_object_id AS report_object_id,
                raml.view_type AS view_type,
                CASE
                    WHEN %s = 'account' THEN acc.code
                    WHEN %s = 'journal' THEN acj.code
                    WHEN %s = 'analytic' THEN an_acc.code
                    ELSE rep.ref
                END AS code,
                CASE
                    WHEN %s = 'account' THEN acc.name
                    WHEN %s = 'journal' THEN acj.name
                    WHEN %s = 'analytic' THEN an_acc.name
                    ELSE rep.name
                END AS name,
                acj.code AS j_code,
                ml.name AS move_name,
                aml.ref AS displayed_ref,
                aml.quantity AS product_qty,
                pp.barcode AS product_barcode,
                CASE
                    WHEN raml.full_reconcile_id IS NOT NULL THEN (CASE WHEN raml.reconciled = TRUE THEN afr.name ELSE '*' END)
                    ELSE ''
                END AS matching_number
            FROM
                account_report_standard_ledger_line raml
                LEFT JOIN account_account acc ON (acc.id = raml.account_id)
                LEFT JOIN account_journal acj ON (acj.id = raml.journal_id)
                LEFT JOIN res_partner rep ON (rep.id = raml.partner_id)
                LEFT JOIN account_move ml ON (ml.id = raml.move_id)
                LEFT JOIN account_move_line aml ON (aml.id = raml.move_line_id)
                LEFT JOIN product_product pp ON (pp.id = aml.product_id)
                LEFT JOIN account_full_reconcile afr ON (afr.id = raml.full_reconcile_id)
            WHERE
                raml.report_id = %s
                AND (%s OR raml.report_object_id = %s)
                AND raml.line_type IN %s
            ORDER BY
                raml.id
            """
    params = [
        self.report_type, self.report_type, self.report_type, self.report_type, self.report_type, self.report_type,
        self.report_id.id,
        True if report_object is None else False,
        report_object,
        type_l
    ]

    self.env.cr.execute(query, tuple(params))
    return self.env.cr.dictfetchall()

内容的提问来源于stack exchange，提问作者Muhammad S. Eltyar