Odoo: Fetch and merge data from multiple tables

While working with different models in Odoo we often come across situations where we want to get data from multiple tables in a single view and show it to the user. Although there are some ways already present to get data from different views and show it to the user (Odoo’s dashboard app allows us to combine views from different model’s views together). But there is no such way to combine data from multiple model tables to show it in a single view.

This can be achieved with a little bit of coding and in the upcoming sections I’ll explain just that but before proceeding let me explain the terminology I’ll be using throughout the blog.

1. Model — Odoo’s model which corresponds to a PSQL table
2. Table — Postgres table
3. PSQL — Postgresql

The following are the things we’ll be doing in the blog

1. Understand the relation between Odoo’s model and the PSQL table
2. Getting data from sale orders to check how much quantity of which product was sold to which customer.

Odoo’s model table relation

Before trying to combine data from different model’s table it’s important we have a look at the relationship between Odoo’s models and the PSQL table, most of you might already know it but still for the sake of details and the flow let’s start from zero.

The way we define a model in Odoo is in the following way:-

class partner_product(models.Model):
    _name = 'partner_product.partner_product'
    _description = 'partner_product.partner_product'

You see there is a class name ‘partner_product’ which we inherited from the ‘models.Model’ class which is the base class in which the Odoo has defined a model’s attributes that we use (they actually have a superclass named BaseModel which have all the properties but that explanation is for another topic).

As you can see that in the model attribute the first thing is the ‘_name’. The _name attribute is the one that defines the name of this model, what makes this attribute special is that on the PSQL side a table with the same name will be created but the ‘.’ will be replaced by an ‘_’. So if the name of this model is ‘partner_product.partner_product’ then on the backend as soon as you install the module containing this model a table with the name of ‘partner_product_partner_product’ will be created.

Models and tables we’ll use

To explain the process of getting data from different tables we’ll be using the following models.

1. sale.order (sale_order)
2. sale.order.line (sale_order_line)
3. product.product (account_move)
4. product.template (product_template)
5. res.partner (res_partner)

We’ll be trying to combine data from all the above sources to show them in an aggregated way in a single model. The data we need in the end is the Customer Name, Product Name, No of items sold.

Development

Pre-requisites

1. You must have a database created with the Sales and Inventory app installed.
2. A module for development, you can scaffold a module directly with the help of the scaffold command or create a directory manually. The scaffold command is as follows (while using scaffold command make sure you are in the directory which has an odoo-bin file in it)

python ./odoo-bin scaffold module_name directory_name

Defining the model

I’ve created a module named sale_invoice for the purpose and have already installed the Sales and Invoice apps installed. ‘In order to get the data from different tables, we’ll be using the PSQL query’. I’ve named the model ‘partner_product.partner_product’ to add the logic and we’ll be defining the fields and the query we need in it.

class partner_product(models.Model):
    _name = 'partner_product.partner_product'
    _auto = False
    _description = 'partner_product.partner_product'

We define the _auto attribute here to set it as false, setting it to false will suppress the Odoo from creating a PSQL table automatically rather we’d need to override the init method to create the table, you can study more about it here.

Defining the fields we need

name = fields.Char("Partner Name")
product_name = fields.Char("Product Name")
total_quantity_sold = fields.Char("Total Quantity Sold")

The above are the five fields that we’d be needing for our demo.

Overriding the init method

The init method is the first method to get initialized as soon as the module is installed or upgraded

def init(self):
    tools.drop_view_if_exists(self.env.cr, self._table)
    self.env.cr.execute(
        """CREATE or REPLACE VIEW %s as (%s)""" % (self._table, self._query())
    )

The init method is the one with the help of which we are going to create the table for this model in PSQL.

tools.drop_view_if_exists(self.env.cr, self._table)

The above statement tells the Odoo to drop the table if it already exists, it happens if we added a new field or change the table structure in some way.

self.env.cr.execute(
        """CREATE or REPLACE VIEW %s as (%s)""" % (self._table, self._query())
    )

The above statement executes the query which we will define in the next step. When the query executes it creates the table in the backend. The self._query is actually a method call. The _query method is defined in the next step where we write the PSQL query to get data from the database

Writing the query

The query is the most important step in all this, it helps us to fetch the desired data from the different tables.

A little bit of PSQL knowledge will be good because we are using a PSQL query, if you don’t have an idea about queries, I’d recommend you have a look here to get an idea of basic PSQL queries.

def _query(self, with_clause="", fields={}, groupby="", from_clause=""):
    return """

        SELECT 

        T1.id,
        pr.name "name",
        pt.name "product_name",
        T1."total_quantity_sold" 
        
        
        FROM 
        
        (
        SELECT 
        
        p.id,
        sum(sol.product_uom_qty) "total_quantity_sold"
        
        FROM
        
        product_product p
        inner join sale_order_line sol on sol.product_id = p.id
        inner join sale_order so on so.id=sol.order_id

        
        where so.state ='done'
        or so.state='sale'
        
        GROUP BY
        p.id

        )T1
        
        inner join product_product prod on prod.id=T1.id 
        inner join sale_order_line sol on sol.product_id =prod.id
        inner join product_template pt on pt.id = prod.product_tmpl_id
        inner join sale_order so on so.id=sol.order_id
        inner join res_partner pr on so.partner_id=pr.id


        where so.state ='done'
        or so.state='sale'

        GROUP BY
        T1.id,
        pr.name,
        pt.name,
        T1."total_quantity_sold"
        """

In the above query, we fetched data from five tables. This part is purely a PSQL query. We used joins in the query to get the data we desire, you can study more about joins here. In the above query what we did is first we searched the product_product and sale_order_line relation in order to group the result by the product and get the sum of the product quantity present in the sale order line, then we used joins to find the relationship between different tables with the help of foreign keys present. We have only considered the sales order which are in Sale Order or Locked state.

Once the query is done, it’s time for us to define the view file.

<odoo>
    <data>

        <record model="ir.ui.view" id="partner_product.list">
            <field name="name">partner_product list</field>
            <field name="model">partner_product.partner_product</field>
            <field name="arch" type="xml">
                <tree>
                    <field name="name"/>
                    <field name="product_name"/>
                    <field name="total_quantity_sold"/>
                </tree>
            </field>
        </record>


        <record model="ir.actions.act_window" id="partner_product_action_window">
            <field name="name">Product Partner Relation</field>
            <field name="res_model">partner_product.partner_product</field>
            <field name="view_mode">tree</field>
        </record>


        <menuitem name="Partner" id="partner_product_menu_root" parent="stock.menu_warehouse_report"
                 />


        <menuitem name="Partner Product Sale" id="partner_product.menu_1" parent="partner_product_menu_root"
                  action="partner_product_action_window"/>


    </data>
</odoo>

Once all the above steps are done properly, it’s time we install our new module and see the results. The following snippet is the result of our work.

Combination of different tables data

Conclusion

So we just learned how to get data from different tables and combine it to get the desired meaningful result. The following is the link to the code repo.

https://github.com/divy9t/OpenSource/tree/odoo/partner_product

Thanks for reading...