Deep Dive into Odoo's ORM: Exploring Advanced Queries and Performance
Understanding Odoo's ORM for Developers
Deep Dive into Odoo's ORM: Exploring Advanced Queries and Performance
JD Berkowitz 17 May, 2023
Share this post

Odoo's ORM provides an abstraction layer over SQL, enabling developers to interact with the database without having to write raw SQL queries. This makes it possible to perform a wide range of database operations in a way that's consistent with the Python programming language.

ORM stands for Object-Relational Mapping. It's a programming technique used in software development to convert data between incompatible type systems using object-oriented programming languages. This creates a virtual object database that can be used from within the programming language.

In the context of Odoo, the ORM allows developers to interact with the database in a more intuitive and Pythonic way, using Python classes and objects instead of writing raw SQL queries. The ORM handles the conversion between these Python objects and the underlying database records.

 
 

Advanced Queries with Odoo's ORM

Odoo's ORM allows for a rich set of query operations. While simple operations like search and read are commonly used, there are also many advanced features that can be harnessed for more complex requirements.

1. Domain Operators: Odoo uses domain operators to define search conditions. Beyond simple operators like "=", "!=" or "ilike", there are more advanced operators like "child_of", "parent_of", and "in". Understanding how and when to use these can greatly enhance the flexibility of your queries.

Summary of Domain Operators in Odoo's ORM

Domain operators in Odoo provide a way to define search conditions. Below are several commonly used and advanced operators:

  1. '=': The equals operator is used to match a field with a specific value. For example, [('name', '=', 'John')] will match records where the name field is exactly 'John'.

  2. '!=': The not equals operator is used to match records where a field does not have a specific value. For example, [('name', '!=', 'John')] will match records where the name field is not 'John'.

  3. 'ilike': The ilike operator is used to perform case-insensitive search. For example, [('name', 'ilike', 'john')] will match records where the name field contains 'john', 'John', 'JOHN', etc.

  4. 'in': The in operator is used to match a field with any value in a list. For example, [('name', 'in', ['John', 'Jane'])] will match records where the name field is either 'John' or 'Jane'.

  5. 'not in': The not in operator is used to match a field with none of the values in a list. For example, [('name', 'not in', ['John', 'Jane'])] will match records where the name field is neither 'John' nor 'Jane'.

  6. 'child_of': The child_of operator is used in hierarchical structures. For example, [('parent_id', 'child_of', [some_id])] will match records that are descendants of the record with id some_id.

  7. 'parent_of': The parent_of operator is used in hierarchical structures. For example, [('child_id', 'parent_of', [some_id])] will match records that are ancestors of the record with id some_id.

  8. 'like': The like operator is used to perform case-sensitive search. For example, [('name', 'like', 'John')] will match records where the name field contains 'John' but not 'john' or 'JOHN'.

  9. '=?': This operator is used to handle NULL values correctly. If the right-hand side is False or None, the domain will always match.

  10. '<''<=''>''>=': These operators are used to compare a field with a specific value.

  • '<' (less than): This operator is used to match records where a field is less than a specific value. For example, [('age', '<', 30)] will match records where the age field is less than 30.

  • '<=' (less than or equal to): This operator is used to match records where a field is less than or equal to a specific value. For example, [('age', '<=', 30)] will match records where the age field is less than or equal to 30.

  • '>' (greater than): This operator is used to match records where a field is greater than a specific value. For example, [('age', '>', 30)] will match records where the age field is greater than 30.

  • '>=' (greater than or equal to): This operator is used to match records where a field is greater than or equal to a specific value. For example, [('age', '>=', 30)] will match records where the age field is greater than or equal to 30.

  • These operators are particularly useful when working with numerical fields or date fields. For date fields, the comparison will be based on the chronological order of the dates. For example, [('start_date', '>', '2023-05-01')] will match records where the start_date field is later than May 1, 2023.

  • These domain operators can be combined to form more complex queries. Understanding how and when to use these can greatly enhance the flexibility of your queries.

2. Recordsets: A recordset represents a collection of records in Odoo. Developers can manipulate these recordsets in Pythonic ways, such as iterating over them, indexing into them, or even performing set operations.

3. Computed Fields and Onchange Methods: These are powerful tools for dynamically computing the value of a field or for triggering actions when a field's value changes. They can be used in complex ways to implement intricate business logic.

Performance Considerations

While Odoo's ORM provides a lot of convenience, it's important to be aware of the performance implications of your database interactions. Here are a few key points to consider:

  1. Avoid Large Recordsets: Fetching large amounts of data into memory can slow down your system and consume large amounts of memory. Use filters and limits to reduce the size of your recordsets.

  2. Use Indexes: For fields that you often search or sort on, consider adding a database index. This can greatly speed up these operations.

  3. Minimize Database Hits: Each interaction with the database takes time. Try to minimize these by fetching all the data you need in one go, rather than making multiple queries.

  4. Care with Computed Fields: Computed fields can be a powerful tool, but they can also slow down your system if not used carefully. Make sure your compute methods are as efficient as possible, and consider storing the computed values in the database if they're needed often.

ORM Best Practices

  1. Plan Your Queries: Think about what data you need and how to fetch it in the most efficient way. This often means minimizing the number of queries and reducing the size of the recordsets you're working with.

  2. Use the ORM's Features: Odoo's ORM has many powerful features, like computed fields and onchange methods. Use these to implement your business logic in a clear and efficient way.

  3. Monitor Performance: Use the logging and profiling tools provided by Odoo to monitor the performance of your queries. This can help you identify and fix any performance bottlenecks.


ORM Performance: Poorly Designed vs. Well-Designed Queries

Understanding how to write efficient queries with Odoo's ORM is crucial for maintaining good performance. Let's look at some examples of poorly designed queries and how they can be improved.

Example 1: Fetching unnecessary data

Poorly Designed Query:

employees = self.env['hr.employee'].search([])
for employee in employees:
    print(employee.name, employee.department_id.name)

This query fetches all fields for all employees, even though we only need the name field from the hr.employee model and the name field from the related hr.department model.

Well-Designed Query:

employees = self.env['hr.employee'].search([], fields=['name', 'department_id'])
for employee in employees:
    print(employee.name, employee.department_id.name)

In the improved query, we specify the fields we need when calling the search method, which reduces the amount of data fetched from the database.

Example 2: Making unnecessary database hits

Poorly Designed Query:

employee_ids = self.env['hr.employee'].search([]).ids
for employee_id in employee_ids:
    employee = self.env['hr.employee'].browse(employee_id)
    print(employee.name)

This code first makes a query to fetch all employee IDs, then makes a separate query for each employee to fetch their name.

Well-Designed Query:

employees = self.env['hr.employee'].search([])
for employee in employees:
    print(employee.name)

The improved code fetches all employees in a single query and then prints their names. This reduces the number of database hits, which can significantly improve performance for large datasets.

Example 3: Failing to utilize the power of domain operators

Poorly Designed Query:

employees = self.env['hr.employee'].search([])
for employee in employees:
    if employee.department_id.name == 'Sales':
        print(employee.name)

This code fetches all employees and then checks in Python code whether each employee's department is 'Sales'.

Well-Designed Query:

employees = self.env['hr.employee'].search([('department_id.name', '=', 'Sales')])
for employee in employees:
    print(employee.name)

The improved code uses the power of Odoo's domain operators to filter employees by department in the database query itself. This reduces the amount of data fetched and the amount of processing done in Python, which can lead to significant performance gains.

Understanding self.env['model_name'] in Odoo

The env attribute is a critical part of the Odoo framework, providing access to the current environment — the user context, the cursor, and the current model instances. self.env is available in all models and contains essential information about the current state of the system.

When you write self.env['model_name'], you are accessing an instance of the specified model in the current environment. Let's break down what's happening:

  • self refers to the current record or recordset. In Odoo, methods on models are executed on recordsets, which are collections of records. A method can be executed on a single record or multiple records at once, and self will always refer to the record or records the method is being executed on.

  • env is an attribute of self that represents the environment of the current transaction. It holds several pieces of information, including the cursor for database access, the current user, and the current context.

  • 'model_name' is a string that specifies the name of the model you want to access. For example, 'res.partner' for the Partner model or 'hr.employee' for the Employee model.

So, when you write self.env['res.partner'], you're getting an instance of the Partner model in the current environment. You can then call methods on this model instance, such as search(), browse(), or create(), to perform operations on the Partner records.

Here are a few examples:

  • Fetching records: partners = self.env['res.partner'].search([('country_id.name', '=', 'Belgium')])

  • Creating a new record: new_partner = self.env['res.partner'].create({'name': 'John Doe'})

  • Accessing a specific record by ID: partner = self.env['res.partner'].browse(1)

Remember, self.env['model_name'] is a powerful tool that lets you access and manipulate any model in the Odoo system. Be sure to use it wisely to maintain good performance and avoid unintended side effects.

Choosing Fields to Index

Indexes are a key component in optimizing database performance in Odoo. They speed up the retrieval of rows from the database but come at a cost of additional storage space and slower write operations. Therefore, careful thought should be given to decide which fields to index. Here are some key considerations:

  1. Frequency of Queries: Fields that are often used in search operations are prime candidates for indexing. If a certain field is commonly included in search domains, adding an index can significantly speed up these queries.

  2. Cardinality: High cardinality fields (those with many unique values) often benefit more from indexing than low cardinality fields (those with few unique values). For example, an index on a gender field (which has a low cardinality with values like 'male' and 'female') may not improve performance much. On the other hand, an index on a email or employee_id field (which are likely to have many unique values) can greatly improve query speed.

  3. Size of the Table: The larger the table, the more an index can help. Indexing fields in small tables may not provide a noticeable performance improvement.

  4. Write-heavy vs Read-heavy Fields: Fields that are frequently updated may not be the best candidates for indexing because each write operation will require updating the index, which can slow down performance. On the other hand, fields that are often read but rarely updated can benefit from an index.

  5. Sorting Operations: Fields that are often used for sorting can also benefit from an index. An index can significantly speed up the sorting process.

  6. Foreign Keys: Foreign key fields are often good candidates for indexing, especially if they are frequently used in join operations.

Remember, while indexing can significantly improve read operations, they also add overhead for write operations because each time a record is inserted or updated, the index needs to be updated as well. Therefore, it's a balance that needs to be struck based on the specific usage patterns of your Odoo instance.

To add an index to a field in Odoo, you can set the index=True attribute in the field definition. For example:

class Partner(models.Model):
    _name = 'res.partner'
    email = fields.Char(index=True)

In this example, an index will be created for the email field in the res.partner model.

Always remember to monitor your system's performance, identify bottlenecks, and adjust your indexing strategy as needed. This can make a significant difference in the speed and efficiency of your Odoo system.

Monitoring System Performance in Odoo

Maintaining optimal performance is crucial for a smooth and efficient operation of your Odoo system. Here are some key points to consider when monitoring system performance:

  1. Odoo's Built-in Logging: Odoo's logging system can provide valuable insights into system performance. By setting the log level to "debug" or "debug_sql", you can monitor database queries and their execution times. This can help you identify slow queries that may need optimization.

  2. PostgreSQL Statistics Collector: PostgreSQL, the database system used by Odoo, has a built-in statistics collector that tracks information about its activity. You can query these statistics to understand the behavior of your database. This includes information about table sizes, index usage, and query performance. This information can be accessed via SQL queries or through performance monitoring tools that interface with PostgreSQL.

  3. Odoo Profiler Module: There are community modules available, such as the Odoo Profiler, that can help you analyze the performance of your Odoo instance. This module provides detailed performance data at the controller, model, and function level, helping you pinpoint areas that need optimization.

  4. Server Monitoring Tools: Use server monitoring tools to track CPU, memory, disk I/O, and network usage. High CPU usage could indicate inefficient code, and increasing memory usage over time could indicate a memory leak. Disk I/O can also be a bottleneck, especially if large amounts of data are being read or written.

  5. Web Client Performance: For a comprehensive understanding of your system's performance, don't forget to monitor the client-side performance as well. Tools like Google Lighthouse can provide insights into how your Odoo web client is performing in terms of speed, accessibility, best practices, and SEO.

Regular monitoring and profiling of your system is the key to maintaining good performance. By identifying and addressing bottlenecks promptly, you can ensure a smooth and responsive experience for your Odoo users.

Leveraging the Features of Odoo's ORM

Odoo's Object-Relational Mapping (ORM) provides powerful tools for interacting with your database. Understanding these tools can greatly enhance your ability to write efficient, readable, and maintainable code. Here are some key features of Odoo's ORM and how to use them:

  1. Recordsets: In Odoo, methods on models are executed on recordsets, which are collections of records. A method can be executed on a single record or multiple records at once. This allows for highly efficient batch operations. For example, you can write employees.write({'department_id': new_department.id}) to update the department for a set of employees in a single database operation.

    # Fetch all employees in the Sales department
    sales_employees = self.env['hr.employee'].search([('department_id.name', '=', 'Sales')])
    # Update the salary of all Sales department employees
    sales_employees.write({'salary': 5000})
    

    In this example, write() is called on a recordset of multiple employees, updating all of them in a single database operation.

  2. Computed Fields: Odoo's ORM allows you to define computed fields, which are fields whose value is calculated based on other fields. Computed fields can be stored or non-stored, and can depend on values of other fields in the same model or in related models. This feature can be used to keep calculated data readily available without needing to compute it on the fly each time it's needed.

    class Employee(models.Model):
        _name = 'hr.employee'
        # Define a field for the employee's age
        age = fields.Integer()
        # Define a computed field for whether the employee is eligible for a senior discount
        senior_discount_eligible = fields.Boolean(compute='_compute_senior_discount_eligible')
        @api.depends('age')
        def _compute_senior_discount_eligible(self):
            for record in self:
                record.senior_discount_eligible = record.age >= 65
    

    In this example, the senior_discount_eligible field is computed based on the age field. Whenever an employee's age changes, their senior_discount_eligible field will be automatically updated.

  3. Related Fields:  Related fields provide a way to automatically fetch and store the value of a field on a related record. This allows for easier access to fields from related models, and can improve performance by reducing the need for additional database queries.

    class Employee(models.Model):
        _name = 'hr.employee'
        # Define a related field for the name of the employee's department
        department_name = fields.Char(related='department_id.name')
    

    In this example, the department_name field will automatically fetch and store the name of the employee's department. This allows for easy access to the department name without needing to fetch the department record.

  4. Onchange Methods:Onchange methods are used to update the UI dynamically based on changes to certain fields. They can be used to provide immediate feedback to the user or to compute default values for certain fields based on the values of other fields.

    class SaleOrder(models.Model):
        _name = 'sale.order'
        customer_id = fields.Many2one('res.partner')
        delivery_address = fields.Char()
        @api.onchange('customer_id')
        def _onchange_customer_id(self):
            self.delivery_address = self.customer_id.address
    

    In this example, whenever the customer of a sale order is changed, the delivery address is automatically updated to match the address of the new customer.

  5. Constraints: Constraints in Odoo's ORM provide a way to enforce certain conditions on the data. You can define Python constraints, which are executed in the server when a record is created or updated, and SQL constraints, which are enforced directly in the database.

    class Employee(models.Model):
        _name = 'hr.employee'
        age = fields.Integer()
        @api.constrains('age')
        def _check_age(self):
            for record in self:
                if record.age < 16:
                    raise ValidationError("Employees must be at least 16 years old.")
    

    In this example, a constraint is defined to ensure that an employee's age is at least 16.

  6. Inheritance: Odoo's ORM supports several forms of inheritance, including classical inheritance, delegation inheritance, and extension inheritance. This allows for highly modular and reusable code.

    class SaleOrder(models.Model):
        _inherit = 'sale.order'
        delivery_date = fields.Date()
    

    In this example, the sale.order model is extended to add a new delivery_date field. This field will be available on all sale orders, alongside the fields defined in the original model.

Working with Computed Fields in Odoo

Computed fields in Odoo are fields whose value is calculated based on other fields. They are defined by providing a compute function, which calculates the field's value. Computed fields can be stored or non-stored, and can depend on values of other fields in the same model or in related models.

Here's how to define a computed field:

class Employee(models.Model):
    _name = 'hr.employee'
    age = fields.Integer()
    retirement_eligible = fields.Boolean(compute='_compute_retirement_eligible')
    @api.depends('age')
    def _compute_retirement_eligible(self):
        for record in self:
            record.retirement_eligible = record.age >= 65

In this example, the retirement_eligible field is computed based on the age field. The @api.depends decorator is used to specify which fields the computation depends on.

Here are some considerations for using computed fields:

  1. When to Store Computed Fields: By default, computed fields are not stored in the database. They are computed on the fly each time they are accessed. However, you can choose to store them by setting store=True. Stored computed fields are computed only when one of the fields they depend on changes. They are useful if the computation is expensive and the field is accessed frequently.

  2. Dependencies: It's important to correctly specify the dependencies of your computed fields using the @api.depends decorator. This allows Odoo to know when to recompute the fields. If you don't correctly specify the dependencies, your computed fields may not be updated when they should be.

  3. Inverse Function: If you want your computed field to be editable, you can define an inverse function using inverse=. The inverse function is called when the computed field is written to. It should update the fields that the computed field depends on. For example:

class Employee(models.Model):
    _name = 'hr.employee'
    birthdate = fields.Date()
    age = fields.Integer(compute='_compute_age', inverse='_inverse_age', store=True)
    @api.depends('birthdate')
    def _compute_age(self):
        for record in self:
            record.age = relativedelta(date.today(), record.birthdate).years
    def _inverse_age(self):
        for record in self:
            end_of_year = date(date.today().year, 12, 31)
            record.birthdate = end_of_year - relativedelta(years=record.age)

In this example, the age field is computed based on the birthdate field, but it's also editable. When the age field is written to, the birthdate field is updated accordingly.

Remember, computed fields in Odoo are a powerful feature that allows you to define fields whose values are calculated dynamically. Use them wisely to keep your data consistent and your code clean and efficient.

Conclusion

In conclusion, Odoo's ORM provides powerful features that make it a highly effective tool for building and managing business applications. Whether you're performing complex queries, optimizing database performance, monitoring system health, or leveraging computed fields, understanding the workings of Odoo's ORM is crucial. However, mastering these concepts and applying them effectively requires considerable experience and expertise.

That's where we come in. At Discourse Digital, we specialize in Odoo development and management. Our team of experts is deeply familiar with Odoo's ORM and its many features, and we know how to use them to build robust, efficient, and scalable Odoo applications. We can help you optimize your queries, choose the right fields to index, monitor your system's performance, effectively use computed fields, and much more.

Whether you're just starting out with Odoo or you're looking to optimize an existing Odoo system, we're here to help. With Discourse Digital, you can rest assured that your Odoo system is in capable hands. Contact us today to learn more about how we can help you get the most out of Odoo.


Sign in to leave a comment
Adding Products to a Customer's Cart via URL: Creating an Odoo Community Addon via Bitnami on Google Cloud
Quickly Add Powerful Functionality to Your Odoo ERP