Build a new view or a model based on a SQL query on Odoo 11

Question

I'm working on a Odoo module.

I want my module to be a "report" of most purchased products (by customer).

I have created a view on Odoo, but now, I need to "filter" these view by customer. The code are here:

class SaleProductsByCustomer(models.Model):
_name = "sale.order.product"
_auto = False

partner_id = fields.Many2one('res.partner')
orders = fields.Integer(string='Total Orders')
name = fields.Char(string='Name')
price_total = fields.Float(string='Total Payment')
qty = fields.Integer(string='Qty Ordered')
last_order = fields.Date(string='Last Order Date')

@api.model_cr
def init(self):
    tools.drop_view_if_exists(self._cr, 'sale_order_product')
    tools.drop_view_if_exists(self._cr, 'sale_order_product_report')
    self._cr.execute("""
        CREATE OR REPLACE VIEW sale_order_product_report AS (
            SELECT so.order_partner_id AS id, count(so.id) AS orders, pt.name, sum(so.price_total) AS price_total,
                sum(so.product_uom_qty) AS qty, max(so.create_date) AS last_order
            FROM public.sale_order_line AS so
            LEFT JOIN public.product_product AS pr ON so.product_id = pr.id
            LEFT JOIN public.product_template AS pt ON pr.product_tmpl_id = pt.id
            GROUP BY so.order_partner_id, so.product_id, pt.name
            ORDER BY qty DESC
        )""")

I've tried to access these view with this code:

class SaleProduct(models.Model):
_inherit = 'res.partner'

customer_product_history_ids = fields.One2many(comodel_name='sale.order.product', compute='_compute_customer_product_history', readonly=True)

@api.multi
@api.model_cr
def _compute_customer_product_history(self):
    for partner in self:
        if partner.id:
            sale.customer_product_history_ids = self.env['sale.order.product'].search([('partner_id', '=', partner.id)])

But odoo refuses to read it, with this error:

2018-12-13 03:05:36,889 13482 ERROR xx odoo.sql_db: bad query: b'SELECT "sale_order_product".id FROM "sale_order_product" WHERE ("sale_order_product"."partner_id" = 107989) ORDER BY "sale_order_product"."id" ' ERROR: relation "sale_order_product" does not exist

Apparently, even if I have my "sale.order.product", I cannot reference it for doing what I want.

I've followed and read lots of guides for doing a custom report and custom SQL queries, but I cannot find a complete guide to do it, and my knowledge of odoo modules aren't great.

The SQL query has been tested and work correctly. What I need is to apply a WHERE clause to the SQL, and put the results on a view.

The view I have are here:

    <record model="ir.ui.view" id="sale_order_form_views_customer_history">
    <field name="name">sale.order.form.view.customer.product.history</field>
    <field name="model">res.partner</field>
    <field name="inherit_id" ref="base.view_partner_form"/>
    <field name="arch" type="xml">
        <xpath expr="//notebook" position="inside">
            <page name="customer_product_history" string="Customer Product History">
                <field name="customer_product_history_ids">
                </field>
            </page>
        </xpath>
    </field>
</record>

Note: I've readed lot of guides, on stackoverflow and another sites but noone covers all what I'm asking. If you think this post is a duplicate, please give me a comment for reviewing the other one.

Thanks for the help.

Answer 1

The name of your model should match the name of your view.

     _name = 'sale.order.product.report'

That's what cause you this error.

Or you can tell odoo that this model is related to specific tablee in database

         _name = 'sale.order.product'
         _table = 'sale_order_product_report'

Note : You can interact with view like a normal table