Odoo xml tree view not showing all expected results

Question

i am trying to make a report of the sales and purchases transaction on each product, since it selects fields from different models (tables) i had to make a model based on a sql view now the problem is, in the xml tree view, it doesn't return back all the results it's supposed to, i checked the sql view query and ran it on postegres, and it returns 30 lines, yet what shows up on the xml tree view is only 17

code below

class ProductProfileReportView(models.Model):
    _name = "product.profile.report"
    _auto = False

    product_id = fields.Many2one(comodel_name="custom.product", string="Product", required=False, )
    action_type = fields.Char(string="Type")
    create_date = fields.Datetime(string="Date")
    invoice_id = fields.Many2one(comodel_name="custom.purchase", string="Invoice", required=False, )
    qty = fields.Integer(string="Qty")
    supplier_id = fields.Many2one(comodel_name="custom.supplier", string="Supplier", required=False, )
    customer_id = fields.Many2one(comodel_name="custom.customer", string="Customer", required=False, )
    price = fields.Float(string="Price")

    @api.model_cr
    def init(self):
        """ Event Question main report """
        tools.drop_view_if_exists(self._cr, 'product_profile_report')
        self._cr.execute(""" CREATE OR REPLACE VIEW product_profile_report AS (
            (select 
            ROW_NUMBER() OVER (ORDER BY product_id) AS id,
            custom_product.id as product_id, 
            'Purchase' as action_type, 
            custom_purchase.create_date as create_date, 
            custom_purchase.id as invoice_id, 
            custom_purchase_line.qty as qty, 
            custom_supplier.id as supplier_id,
            '0' as customer_id, 
            custom_product.sell_price as price
            from custom_product
            inner join custom_purchase_line
            on custom_product.id = custom_purchase_line.product_id
            inner join custom_purchase
            on custom_purchase_line.purchase_id = custom_purchase.id
            inner join custom_supplier
            on custom_purchase.supplier_id = custom_supplier.id)
            union
            (select 
            ROW_NUMBER() OVER (ORDER BY custom_product.id) AS id,
            custom_product.id as product_id,
            'Sale' as action_type, 
            custom_sale.create_date as create_date, 
            custom_sale.id as invoice_id, 
            custom_sale_line.qty as qty, 
            '0' as supplier_id,
            custom_customer.id as customer_id, 
            custom_product.sell_price as price
            from 
            custom_product
            inner join custom_sale_line
            on custom_product.id = custom_sale_line.branch_product_ids
            inner join custom_sale
            on custom_sale_line.order_id = custom_sale.id
            inner join custom_customer
            on custom_sale.customer_id = custom_customer.id))""")

Answer 1

Most likely problem is in here ROW_NUMBER() OVER (ORDER BY product_id) AS id, all id values have to be unique and non zero. And also the ids should be stabile when you filter and group.