Odoo SQL CTE. Common Table Expressions (CTEs) in Odoo SQL queries provide developers with powerful tools for optimizing database performance and writing cleaner, more maintainable code. In this comprehensive guide, we’ll explore how to effectively implement CTEs in your Odoo modules, particularly when working with complex data scenarios like commission calculations. By mastering these SQL optimization techniques, you’ll significantly improve query readability and execution speed in your Odoo applications.
Understanding Common Table Expressions in Odoo Development
Odoo SQL CTE. Before diving into implementation details, let’s first understand what Common Table Expressions are and why they’re particularly valuable in Odoo development contexts.
What Are Common Table Expressions?
Common Table Expressions, often abbreviated as CTEs, function as temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE SQL statement. Think of them as named temporary tables that exist only for the duration of the query execution.
The basic syntax looks like this:
WITH cte_name AS (
SELECT column1, column2, ...
FROM table
WHERE condition
)
SELECT * FROM cte_name;
This approach offers several advantages, especially when:
- You need to reference the same subquery multiple times
- You want to break down complex queries into logical, manageable chunks
- You’re working with recursive queries (though we won’t cover that advanced use case today)
Why Use CTEs in Odoo?
Odoo’s ORM (Object-Relational Mapping) handles many database interactions automatically, but for complex scenarios, writing custom SQL queries becomes necessary. When developing custom Odoo modules that require sophisticated data retrieval or manipulation, CTEs can help you:
- Improve code readability by breaking complex queries into logical sections
- Enhance performance for queries that would otherwise require multiple nested subqueries
- Create more maintainable code that’s easier to debug and modify later
Now, let’s see how to implement CTEs in an Odoo environment through a practical example.
Implementing CTEs in Odoo: A Commission Calculation Example
Odoo SQL CTE – Let’s examine a real-world scenario where CTEs prove especially useful: calculating sales commissions in Odoo. This typically involves joining multiple tables, filtering by date ranges, and potentially handling complex business rules.
The Base Structure: Using _table_query
In Odoo, when you need to create a model backed by a custom query instead of a physical table, you can use the _table_query property. This is particularly useful for report-like models or data aggregations.
Here’s our commission calculation example that leverages a CTE:
@property
def _table_query(self):
users = self.env.context.get('commission_user_ids', [])
if users:
users = self.env['res.users'].browse(users).exists()
teams = self.env.context.get('commission_team_ids', [])
if teams:
teams = self.env['crm.team'].browse(teams).exists()
return """
WITH {self._commission_lines_query(users=users, teams=teams)}
SELECT
ROW_NUMBER() OVER (ORDER BY era.date_from DESC, era.id) AS id,
era.id AS target_id,
cl.user_id AS user_id,
cl.team_id AS team_id,
cl.property_id AS property_id,
cl.currency_id AS currency_id,
cl.company_id AS company_id,
cl.plan_id AS plan_id,
cl.related_res_model AS related_res_model,
cl.related_res_id AS related_res_id,
cl.date AS date
FROM commission_lines cl
JOIN sale_commission_plan_target era
ON cl.plan_id = era.plan_id
AND cl.date >= era.date_from
AND cl.date <= era.date_to
"""
Let’s break down this code:
- First, we retrieve user IDs and team IDs from the Odoo context, which allows for filtering the commission data based on specific users or teams.
- We use
browse()andexists()to ensure we’re working with valid record sets. - Then, we define our SQL query using a CTE, which is referenced by
{self._commission_lines_query(users=users, teams=teams)}. - The main query selects from the commission lines and joins with the target table, filtering by date ranges.
Implementing the _commission_lines_query Method
The code above references a method called _commission_lines_query which would generate the SQL for the CTE. Let’s implement this method to complete our example:
def _commission_lines_query(self, users=None, teams=None):
query = """
commission_subquery AS (
SELECT
cl.id,
cl.user_id,
cl.team_id,
cl.property_id,
cl.currency_id,
cl.company_id,
cl.plan_id,
cl.related_res_model,
cl.related_res_id,
cl.date
FROM commission_lines cl
WHERE 1=1
"""
# Add filters for specific users if provided
if users:
user_ids = ','.join(str(user.id) for user in users)
query += f" AND cl.user_id IN ({user_ids})"
# Add filters for specific teams if provided
if teams:
team_ids = ','.join(str(team.id) for team in teams)
query += f" AND cl.team_id IN ({team_ids})"
query += ")"
return query
This method builds a CTE named commission_subquery that filters the commission lines based on the provided users and teams. The main query then uses this CTE to join with the target table and apply additional filtering.
Creating a Complete Odoo Model with CTE Support
Now Odoo SQL CTE, let’s put everything together to create a complete Odoo model that utilizes CTEs for commission calculations:
from odoo import models, fields, api
class CommissionReport(models.Model):
_name = 'commission.report'
_description = 'Commission Report'
_auto = False # This indicates that we're not creating a physical table
target_id = fields.Many2one('sale.commission.plan.target', string='Target')
user_id = fields.Many2one('res.users', string='Salesperson')
team_id = fields.Many2one('crm.team', string='Sales Team')
property_id = fields.Many2one('property.model', string='Property')
currency_id = fields.Many2one('res.currency', string='Currency')
company_id = fields.Many2one('res.company', string='Company')
plan_id = fields.Many2one('commission.plan', string='Commission Plan')
related_res_model = fields.Char('Related Model')
related_res_id = fields.Integer('Related Record ID')
date = fields.Date('Date')
def _commission_lines_query(self, users=None, teams=None):
query = """
commission_subquery AS (
SELECT
cl.id,
cl.user_id,
cl.team_id,
cl.property_id,
cl.currency_id,
cl.company_id,
cl.plan_id,
cl.related_res_model,
cl.related_res_id,
cl.date
FROM commission_lines cl
WHERE 1=1
"""
# Add filters for specific users if provided
if users:
user_ids = ','.join(str(user.id) for user in users)
query += f" AND cl.user_id IN ({user_ids})"
# Add filters for specific teams if provided
if teams:
team_ids = ','.join(str(team.id) for team in teams)
query += f" AND cl.team_id IN ({team_ids})"
query += ")"
return query
@property
def _table_query(self):
users = self.env.context.get('commission_user_ids', [])
if users:
users = self.env['res.users'].browse(users).exists()
teams = self.env.context.get('commission_team_ids', [])
if teams:
teams = self.env['crm.team'].browse(teams).exists()
return """
WITH {self._commission_lines_query(users=users, teams=teams)}
SELECT
ROW_NUMBER() OVER (ORDER BY era.date_from DESC, era.id) AS id,
era.id AS target_id,
cl.user_id AS user_id,
cl.team_id AS team_id,
cl.property_id AS property_id,
cl.currency_id AS currency_id,
cl.company_id AS company_id,
cl.plan_id AS plan_id,
cl.related_res_model AS related_res_model,
cl.related_res_id AS related_res_id,
cl.date AS date
FROM commission_subquery cl
JOIN sale_commission_plan_target era
ON cl.plan_id = era.plan_id
AND cl.date >= era.date_from
AND cl.date <= era.date_to
"""
@api.model
def init(self):
"""Initialize the report view by creating or updating the view"""
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(f"""
CREATE OR REPLACE VIEW {self._table} AS ({self._table_query})
""")
In this complete model:
- We define a non-physical model (
_auto = False) that will be backed by a SQL view - We define all the necessary fields that correspond to the columns in our query
- We implement
_commission_lines_queryto build the CTE - We implement
_table_queryto build the main query that uses the CTE - We implement
init()to create or update the SQL view when the module is installed or updated
Advanced CTE Techniques in Odoo
Now Odoo SQL CTE that we’ve covered the basics, let’s explore some advanced techniques for using CTEs in Odoo.
Using Multiple CTEs in a Single Query
You can define multiple CTEs in a single query, which is useful when you need to break down a complex query into several logical steps:
@property
def _table_query(self):
return """
WITH
users_cte AS (
SELECT id, name, email
FROM res_users
WHERE active = TRUE
),
teams_cte AS (
SELECT id, name, user_id AS team_leader_id
FROM crm_team
WHERE active = TRUE
),
commission_cte AS (
SELECT
cl.id,
cl.user_id,
cl.team_id,
cl.amount
FROM commission_lines cl
WHERE cl.state = 'confirmed'
)
SELECT
ROW_NUMBER() OVER (ORDER BY u.id, t.id) AS id,
u.id AS user_id,
u.name AS user_name,
t.id AS team_id,
t.name AS team_name,
COALESCE(SUM(c.amount), 0) AS total_commission
FROM users_cte u
LEFT JOIN commission_cte c ON u.id = c.user_id
LEFT JOIN teams_cte t ON c.team_id = t.id
GROUP BY u.id, u.name, t.id, t.name
"""
This query uses three CTEs to break down the query into:
- A list of active users
- A list of active sales teams
- A list of confirmed commission lines
The main query then joins these CTEs and calculates the total commission for each user and team combination.
Parameterized CTEs for Security
When using CTEs with parameters, it’s important to handle SQL injection vulnerabilities. Instead of directly interpolating values into the SQL string, use parameterized queries:
def _commission_lines_query(self, users=None, teams=None):
query = """
commission_subquery AS (
SELECT
cl.id,
cl.user_id,
cl.team_id,
cl.amount
FROM commission_lines cl
WHERE 1=1
"""
params = []
if users:
user_ids = [user.id for user in users]
query += " AND cl.user_id IN %s"
params.append(tuple(user_ids))
if teams:
team_ids = [team.id for team in teams]
query += " AND cl.team_id IN %s"
params.append(tuple(team_ids))
query += ")"
return query, params
@api.model
def get_commission_data(self, users=None, teams=None):
# Build the query and parameters
cte_query, cte_params = self._commission_lines_query(users=users, teams=teams)
# Build the full query
query = f"""
WITH {cte_query}
SELECT
ROW_NUMBER() OVER (ORDER BY era.date_from DESC, era.id) AS id,
era.id AS target_id,
cl.user_id,
cl.team_id,
cl.amount
FROM commission_subquery cl
JOIN sale_commission_plan_target era
ON cl.plan_id = era.plan_id
AND cl.date >= era.date_from
AND cl.date <= era.date_to
"""
# Execute the query with parameters
self.env.cr.execute(query, cte_params)
return self.env.cr.fetchall()
This approach safely handles user input without risking SQL injection attacks.
Performance Considerations When Using CTEs in Odoo
While CTEs offer many benefits, it’s important to consider performance implications:
When CTEs Can Improve Performance
CTEs can significantly improve performance in the following scenarios:
- Avoiding redundant computations: If the same subquery would be executed multiple times, a CTE can compute it once and reuse the result.
- Simplifying complex queries: Breaking a complex query into logical parts can help the database optimizer generate a more efficient execution plan.
- Replacing recursive queries: For hierarchical data structures (like bills of materials or organizational charts), recursive CTEs can be more efficient than multiple recursive function calls.
When CTEs Might Reduce Performance
There are also scenarios where CTEs might not be the most efficient approach:
- Small, simple queries: For simple queries, the overhead of creating a temporary result set might outweigh the benefits.
- Materialization issues: In some database systems, CTEs are materialized (computed fully before being used), which could be inefficient if only a small portion of the data is needed.
Performance Testing and Optimization
Always test the performance of your queries with realistic data volumes. Some tips for optimizing CTE performance:
- Use EXPLAIN ANALYZE: This PostgreSQL command helps you understand how your query is executed and where bottlenecks might occur.
- Add indexes: Ensure that columns used in JOIN conditions and WHERE clauses are properly indexed.
- Limit data early: Apply filters in the CTE to reduce the amount of data processed.
-- Example of using EXPLAIN ANALYZE to test query performance
EXPLAIN ANALYZE
WITH commission_subquery AS (
SELECT * FROM commission_lines WHERE date >= '2023-01-01'
)
SELECT * FROM commission_subquery JOIN sale_commission_plan_target ON ...;
Practical Use Cases for CTEs in Odoo
Let’s explore Odoo SQL CTE additional real-world scenarios where CTEs can be beneficial in Odoo development:
Use Case 1: Sales Dashboard with Complex Metrics
Creating a dashboard that displays various sales metrics often requires complex calculations and joining multiple tables:
@property
def _table_query(self):
return """
WITH
monthly_sales AS (
SELECT
date_trunc('month', date_order) AS month,
user_id,
SUM(amount_total) AS total_sales
FROM sale_order
WHERE state in ('sale', 'done')
GROUP BY date_trunc('month', date_order), user_id
),
monthly_targets AS (
SELECT
date_trunc('month', date_from) AS month,
user_id,
target_amount
FROM sales_targets
)
SELECT
ROW_NUMBER() OVER (ORDER BY ms.month, ms.user_id) AS id,
ms.month,
ms.user_id,
ms.total_sales,
mt.target_amount,
CASE
WHEN mt.target_amount > 0
THEN (ms.total_sales / mt.target_amount) * 100
ELSE 0
END AS achievement_percentage
FROM monthly_sales ms
LEFT JOIN monthly_targets mt
ON ms.month = mt.month AND ms.user_id = mt.user_id
"""
This query calculates monthly sales per user, compares them with targets, and calculates achievement percentages—all in a clear, structured manner.
Use Case 2: Inventory Valuation
Calculating inventory valuation often involves complex logic for cost calculation:
@property
def _table_query(self):
return """
WITH
stock_moves AS (
SELECT
product_id,
location_dest_id,
SUM(CASE WHEN location_dest_id IN (SELECT id FROM stock_location WHERE usage = 'internal')
THEN product_qty ELSE 0 END) AS incoming_qty,
SUM(CASE WHEN location_id IN (SELECT id FROM stock_location WHERE usage = 'internal')
THEN product_qty ELSE 0 END) AS outgoing_qty
FROM stock_move
WHERE state = 'done'
GROUP BY product_id, location_dest_id
),
product_costs AS (
SELECT
product_id,
AVG(price_unit) AS avg_cost
FROM stock_move
WHERE state = 'done'
GROUP BY product_id
)
SELECT
ROW_NUMBER() OVER (ORDER BY p.id) AS id,
p.id AS product_id,
p.name AS product_name,
COALESCE(SUM(sm.incoming_qty - sm.outgoing_qty), 0) AS on_hand_qty,
pc.avg_cost,
COALESCE(SUM(sm.incoming_qty - sm.outgoing_qty), 0) * pc.avg_cost AS valuation
FROM product_product p
LEFT JOIN stock_moves sm ON p.id = sm.product_id
LEFT JOIN product_costs pc ON p.id = pc.product_id
GROUP BY p.id, p.name, pc.avg_cost
"""
This query calculates the current stock level and valuation for each product, using CTEs to break down the complex calculation into logical steps.
Integrating CTEs with Odoo’s ORM
Odoo SQL CTE. While direct SQL queries are powerful, integrating them with Odoo’s ORM allows you to leverage both approaches. Here’s how to create a hybrid approach:
Using CTEs in search_read and Custom Methods
You can use CTEs in custom methods that combine SQL efficiency with ORM convenience:
@api.model
def get_commission_summary(self, start_date, end_date):
# Use a CTE for the complex calculation
query = """
WITH commission_data AS (
SELECT
user_id,
SUM(amount) AS total_commission
FROM commission_lines
WHERE date >= %s AND date <= %s
GROUP BY user_id
)
SELECT user_id, total_commission
FROM commission_data
ORDER BY total_commission DESC
"""
self.env.cr.execute(query, (start_date, end_date))
results = self.env.cr.dictfetchall()
# Enrich the data using ORM
for result in results:
user = self.env['res.users'].browse(result['user_id'])
result['user_name'] = user.name
result['user_email'] = user.email
return results
This method uses a CTE for efficient data aggregation, then uses the ORM to enrich the results with additional user information.
Creating a Helper Model for CTE Queries
You can create a dedicated model to handle complex CTE queries:
class SQLHelper(models.AbstractModel):
_name = 'sql.helper'
_description = 'SQL Helper for Complex Queries'
@api.model
def execute_cte_query(self, cte_query, main_query, params=None):
"""Execute a query with CTEs and return the results
Args:
cte_query (str): The CTE part of the query (WITHOUT the 'WITH' keyword)
main_query (str): The main part of the query that uses the CTE
params (tuple): Query parameters for security
Returns:
list: Query results as dictionaries
"""
full_query = f"WITH {cte_query} {main_query}"
self.env.cr.execute(full_query, params or ())
return self.env.cr.dictfetchall()
You can then use this helper model throughout your Odoo modules:
def get_sales_data(self):
cte_query = """
monthly_sales AS (
SELECT
date_trunc('month', date_order) AS month,
user_id,
SUM(amount_total) AS total_sales
FROM sale_order
WHERE state in ('sale', 'done')
GROUP BY date_trunc('month', date_order), user_id
)
"""
main_query = """
SELECT
month,
user_id,
total_sales
FROM monthly_sales
ORDER BY month DESC, total_sales DESC
"""
return self.env['sql.helper'].execute_cte_query(cte_query, main_query)
Best Practices for Using CTEs in Odoo
To make the most of CTEs in your Odoo development, follow these best practices:
1. Maintain Clear Documentation
Always document your CTEs and explain what each part of the query does:
def _commission_lines_query(self, users=None, teams=None):
"""
Builds a CTE for commission lines with optional user and team filtering.
Args:
users (recordset): Optional res.users recordset to filter by
teams (recordset): Optional crm.team recordset to filter by
Returns:
str: SQL query string for the commission_subquery CTE
"""
# Query implementation...
2. Use Consistent Naming Conventions
Adopt a consistent naming convention for your CTEs to make the code more readable:
- Use descriptive names that indicate what the CTE contains
- Consider prefixing CTEs with the data type they represent (e.g.,
users_cte,sales_cte) - For multiple CTEs that build on each other, consider using sequential naming (e.g.,
step1_raw_data,step2_aggregated_data)
3. Break Down Complex Queries Appropriately
Use multiple CTEs to break down complex logic:
@property
def _table_query(self):
return """
WITH
-- Step 1: Get base data
base_data AS (
SELECT * FROM sales_data WHERE state = 'confirmed'
),
-- Step 2: Calculate totals per customer
customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM base_data
GROUP BY customer_id
),
-- Step 3: Rank customers by total amount
customer_ranks AS (
SELECT
customer_id,
total_amount,
RANK() OVER (ORDER BY total_amount DESC) AS rank
FROM customer_totals
)
-- Main query: Get final results
SELECT
ROW_NUMBER() OVER (ORDER BY rank) AS id,
cr.customer_id,
c.name AS customer_name,
cr.total_amount,
cr.rank
FROM customer_ranks cr
JOIN res_partner c ON cr.customer_id = c.id
WHERE cr.rank <= 10 -- Get top 10 customers
"""
4. Consider Performance Impact
Always test your CTEs with realistic data volumes:
- Use EXPLAIN ANALYZE to understand query execution
- Consider adding indexes to support your queries
- Test with larger datasets to ensure scalability
5. Balance SQL and ORM
Find the right balance between raw SQL queries with CTEs and Odoo’s ORM:
- Use CTEs for complex calculations and aggregations
- Use the ORM for simple CRUD operations and for accessing related records
- Consider hybrid approaches that combine both for the best balance of performance and maintainability
Debugging and Troubleshooting CTE Issues in Odoo
When working with CTEs in Odoo, you might encounter various issues. Here’s how to effectively debug and troubleshoot them:
1. Validating Your SQL Syntax
The first step is to ensure your SQL syntax is correct:
def test_query(self):
"""Test the query outside the Odoo framework"""
try:
query = self._table_query
self.env.cr.execute("EXPLAIN " + query)
return True
except Exception as e:
_logger.error("Query validation error: %s", e)
return False
2. Using Logging for Debugging
Add logging statements to track the execution of your queries:
import logging
_logger = logging.getLogger(__name__)
@api.model
def get_commission_data(self):
query = self._table_query
_logger.debug("Executing query: %s", query)
try:
self.env.cr.execute(query)
results = self.env.cr.dictfetchall()
_logger.debug("Query returned %d results", len(results))
return results
except Exception as e:
_logger.error("Query execution error: %s", e)
raise
3. Simplifying for Troubleshooting
When facing issues with complex CTEs, simplify them to identify the problematic part:
@property
def _table_query(self):
# Start with a simplified version for troubleshooting
return """
WITH simple_cte AS (
SELECT id, name FROM res_users WHERE active = TRUE
)
SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, id AS user_id, name
FROM simple_cte
"""
Once the simplified version works, gradually add complexity back to identify the issue.
Extending Odoo Reports with CTEs
CTEs can be particularly useful for enhancing Odoo’s reporting capabilities. Here’s how to create a custom report using CTEs:
Creating a Custom Sales Analysis Report
class SalesAnalysisReport(models.Model):
_name = 'sales.analysis.report'
_description = 'Enhanced Sales Analysis'
_auto = False
date = fields.Date('Date')
user_id = fields.Many2one('res.users', 'Salesperson')
team_id = fields.Many2one('crm.team', 'Sales Team')
product_id = fields.Many2one('product.product', 'Product')
product_category_id = fields.Many2one('product.category', 'Product Category')
quantity = fields.Float('Quantity')
price_subtotal = fields.Float('Subtotal')
cost_subtotal = fields.Float('Cost')
margin = fields.Float('Margin')
margin_percent = fields.Float('Margin %')
@property
def _table_query(self):
return """
WITH
sale_details AS (
SELECT
so.date_order::date AS date,
so.user_id,
so.team_id,
sol.product_id,
pp.categ_id AS product_category_id,
sol.product_uom_qty AS quantity,
sol.price_subtotal,
sol.product_uom_qty * pt.standard_price AS cost_subtotal
FROM sale_order_line sol
JOIN sale_order so ON sol.order_id = so.id
JOIN product_product pp ON sol.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
WHERE so.state in ('sale', 'done')
)
SELECT
ROW_NUMBER() OVER () AS id,
date,
user_id,
team_id,
product_id,
product_category_id,
quantity,
price_subtotal,
cost_subtotal,
(price_subtotal - cost_subtotal) AS margin,
CASE
WHEN price_subtotal = 0 THEN 0
ELSE ((price_subtotal - cost_subtotal) / price_subtotal) * 100
END AS margin_percent
FROM sale_details
"""
@api.model
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(f"""
CREATE OR REPLACE VIEW {self._table} AS ({self._table_query})
""")
This report provides a comprehensive analysis of sales performance, including margin calculations, by using a CTE to prepare the base data before applying additional calculations.
Conclusion: Unleashing the Power of CTEs in Odoo
Common Table Expressions represent a powerful tool in the Odoo developer’s toolkit. By breaking complex queries into manageable, logical blocks, CTEs improve both code readability and query performance. Throughout this article, we’ve explored how to implement CTEs in Odoo, from basic usage to advanced techniques and best practices.
Key takeaways include:
- CTEs simplify complex queries by breaking them into logical, named blocks
- They’re particularly useful for reports, dashboards, and complex data analysis
- CTEs can be integrated with Odoo’s ORM for a hybrid approach that leverages the strengths of both
- Performance considerations are important, and CTEs should be tested with realistic data volumes
- Following best practices for naming, documentation, and structure ensures maintainable code
By mastering CTEs in your Odoo development workflow, you’ll create more efficient, readable, and maintainable code—ultimately delivering better solutions for your users.
For additional resources on SQL optimization in Odoo, check out:
- Official Odoo Documentation on PostgreSQL
- PostgreSQL CTE Documentation
- Odoo ORM Performance Best Practices
With the techniques described in this article, you’re now equipped to tackle even the most complex data challenges in your Odoo applications using the power of Common Table Expressions.
Discover more from teguhteja.id
Subscribe to get the latest posts sent to your email.