postgresql中带有视图表的Odoo One2many关系

Question

我有以下报告模式：

class crm_purchased_products(models.Model):
    _name = "crm.purchased_products"
    _description = "Most purchased products report"
    _auto = False

    partner_id = fields.Many2one('res.partner', string='Customer')
    product_id = fields.Many2one('product.product', string='Product')
    invoice_qty = fields.Integer('Number of invoices')
    quantity = fields.Integer('Quantity')
    total_amount_untaxed = fields.Float(string='Total amount', digits=dp.get_precision('Account'))


    def init(self, cr):
        tools.drop_view_if_exists(cr, self._table)
        _query = """CREATE OR REPLACE VIEW crm_purchased_products AS (
            SELECT ail.partner_id AS partner_id, ail.product_id AS product_id, 
                COUNT(ail.invoice_id) AS invoice_qty, SUM(ail.quantity) AS quantity, 
                SUM(ail.price_subtotal) AS total_amount_untaxed, MAX(ai.date_invoice) AS last_purchase, 
                (MAX(ai.date_invoice) - MIN(ai.date_invoice))/COUNT(ail.invoice_id) AS days_between_purchases 
            FROM account_invoice_line AS ail 
            JOIN account_invoice AS ai ON ail.invoice_id = ai.id 
            WHERE ai.type = 'out_invoice' AND ai.state IN ('open','paid') 
            GROUP BY ail.partner_id, ail.product_id HAVING ail.product_id > 0 ORDER BY ail.partner_id ASC
        );
        """

    def search(self, cr, uid, args, offset=0, limit=None, order=None,context=None, count=False):
        return super(crm_purchased_products, self).search(cr, uid, args=args, offset=offset, limit=limit, order=order,
        context=context, count=count)

crm_purchased_products()

我想在tree res.partner form view 中插入字段，仅显示与联系人：相关联的产品

<page name="internal_notes" string="Internal Notes">
                        <field name="purchased_products">
                            <tree string="Most purchased products">
                                <field name="partner_id" invisible="1"/>
                                <field name="product_id"/>
                                <field name="invoice_qty"/>
                                <field name="quantity"/>
                                <field name="total_amount_untaxed"/>
                            </tree>
                        </field>
                    </page>

但是，当我试图把它和res.partner联系起来时

purchased_products = fields.One2many('crm.purchased_products', 'partner_id', string='Purchased products', readonly=True, copy=True)

我知道这个错误：

ProgrammingError:关系"crm_purchased_products“不存在第1行:从”crm_purchased_produ“中选择"crm_purchased_products".id

Answer 1

ID字段是自动添加到所有模型从models.Model，所以你必须得到它。试试这个：

def init(self, cr):
    tools.drop_view_if_exists(cr, self._table)
    _query = """CREATE OR REPLACE VIEW crm_purchased_products AS (
        SELECT row_number() OVER () AS id, ail.partner_id AS partner_id, ail.product_id AS product_id, 
            COUNT(ail.invoice_id) AS invoice_qty, SUM(ail.quantity) AS quantity, 
            SUM(ail.price_subtotal) AS total_amount_untaxed, MAX(ai.date_invoice) AS last_purchase, 
            (MAX(ai.date_invoice) - MIN(ai.date_invoice))/COUNT(ail.invoice_id) AS days_between_purchases 
        FROM account_invoice_line AS ail 
        JOIN account_invoice AS ai ON ail.invoice_id = ai.id 
        WHERE ai.type = 'out_invoice' AND ai.state IN ('open','paid') 
        GROUP BY row_number() OVER (), ail.partner_id, ail.product_id HAVING ail.product_id > 0 ORDER BY ail.partner_id ASC
    );
    """