Raw SQL vs Odoo ORM: When to Use Each Approach in Production

# Uses ORM to aggregate
orders = self.env['sale.order'].search([
    ('state', '=', 'done'),
    ('order_date', '>=', cutoff_date),
])
revenue = sum(o.amount_total for o in orders)  # Loads 2M records!

# Uses raw SQL
cr = self.env.cr
cr.execute("""
    SELECT SUM(amount_total) as revenue
    FROM sale_order
    WHERE state = 'done'
    AND order_date >= %s
""", (cutoff_date,))
revenue = cr.fetchone()[0]  # Gets single number

# RIGHT - Use ORM
customer = self.env['res.partner'].create({
    'name': 'Acme Inc',
    'email': 'contact@acme.com',
    'country_id': self.env.ref('base.us').id,
})

# ORM handles:
# ✓ Field validation
# ✓ Onchange triggers
# ✓ Constraints (@api.constrains)
# ✓ Computed fields (profit margin auto-calculates)
# ✓ Access control (users can only see their customers)
# ✓ Audit trail (who created it, when)

# ORM: 0.5 seconds
customers = self.env['res.partner'].search([
    ('country_id', '=', 'US'),
    ('customer_rank', '>', 0),
])
for customer in customers:
    process_customer(customer)

# SQL: 0.4 seconds (slightly faster, but negligible)

# WRONG - ORM approach
orders = self.env['sale.order'].search([
    ('state', '=', 'done'),
])
# This loads 2,000,000 order objects into memory! ❌
revenue = sum(o.amount_total for o in orders)

# RIGHT - SQL approach
cr = self.env.cr
cr.execute("""
    SELECT 
        SUM(amount_total) as revenue,
        COUNT(*) as order_count,
        DATE(order_date) as order_date
    FROM sale_order
    WHERE state = 'done'
    GROUP BY DATE(order_date)
    ORDER BY order_date DESC
""")
for row in cr.fetchall():
    date, revenue, count = row
    print(f"date: revenue (count orders)")

# ❌ DANGEROUS - SQL injection vulnerability
search_keyword = "Product'; DROP TABLE sale_order; --"
cr = self.env.cr
cr.execute(
    "SELECT id, name FROM product_product WHERE name LIKE '" + search_keyword + "'"
)
# An attacker could delete your entire table!

# ✅ SAFE - SQL injection proof
search_keyword = "Product"
cr = self.env.cr
cr.execute(
    "SELECT id, name FROM product_product WHERE name ILIKE %s",
    (search_keyword,)  # Separate from query
)
# The database treats search_keyword as data, never code

# Bad way (string concatenation)
query = "SELECT * FROM sale_order WHERE id = " + order_id  # Vulnerable!

# Good way (parameterized)
query = "SELECT * FROM sale_order WHERE id = %s"
cr.execute(query, (order_id,))  # Safe!

# The %s is a placeholder. The database driver safely inserts the value.

# Multiple parameters
cr.execute(
    "SELECT * FROM sale_order WHERE state = %s AND customer_id = %s",
    ('done', customer_id)
)

# With dates
cutoff_date = fields.Date.today()
cr.execute(
    "SELECT * FROM sale_order WHERE order_date >= %s",
    (cutoff_date,)
)

# With LIKE (text search)
search = "%organic%"  # Add wildcards in the value, not query
cr.execute(
    "SELECT * FROM product_product WHERE name ILIKE %s",
    (search,)
)

# ❌ Slow - loads thousands of records
orders = self.env['sale.order'].search([
    ('state', '=', 'done'),
    ('order_date', '>=', start_date),
    ('order_date', '<=', end_date),
])

# Manually aggregate
sales_by_region = {}
for order in orders:
    region = order.partner_id.state_id.name  # Extra query per order!
    if region not in sales_by_region:
        sales_by_region[region] = 0
    
    # Only count if no excluded categories
    excluded_categories = ['discontinued', 'archived']
    valid_lines = [l for l in order.order_line 
                   if l.product_id.categ_id.name not in excluded_categories]
    
    total = sum(l.price_total for l in valid_lines)
    sales_by_region[region] += total

# ✅ Fast - single database query
cr = self.env.cr
cr.execute("""
    SELECT 
        rp.state_id,
        rs.name as region,
        SUM(sol.price_total) as total_sales,
        COUNT(DISTINCT so.id) as order_count
    FROM sale_order so
    JOIN res_partner rp ON so.partner_id = rp.id
    JOIN res_country_state rs ON rp.state_id = rs.id
    JOIN sale_order_line sol ON so.id = sol.order_id
    JOIN product_product pp ON sol.product_id = pp.id
    JOIN product_category pc ON pp.categ_id = pc.id
    WHERE so.state = %s
    AND so.order_date BETWEEN %s AND %s
    AND pc.name NOT IN ('discontinued', 'archived')
    GROUP BY rp.state_id, rs.name
    ORDER BY total_sales DESC
""", ('done', start_date, end_date))

results = cr.fetchall()
for region_id, region_name, total_sales, order_count in results:
    print(f"region_name: total_sales (order_count orders)")

# Uses ORM - safe but slow for 100K records
for old_customer in old_database.customers:
    self.env['res.partner'].create({
        'name': old_customer['name'],
        'email': old_customer['email'],
        'phone': old_customer['phone'],
        # ... 20 more fields
    })

# Time: 2+ minutes for 100K records

# Direct insert - fastest for bulk data
cr = self.env.cr
records = []
for old_customer in old_database.customers:
    records.append((
        old_customer['name'],
        old_customer['email'],
        old_customer['phone'],
        # ... 20 more fields
    ))

cr.executemany("""
    INSERT INTO res_partner (name, email, phone, ...)
    VALUES (%s, %s, %s, ...)
""", records)
self.env.cr.commit()

# Time: 10 seconds for 100K records

# ❌ WRONG - Overkill for simple search
cr = self.env.cr
cr.execute("SELECT id FROM sale_order WHERE state = %s", ('done',))
order_ids = [row[0] for row in cr.fetchall()]

# ✅ RIGHT - Use ORM
orders = self.env['sale.order'].search([('state', '=', 'done')])
order_ids = orders.ids

# ❌ WRONG - SQL injection vulnerability
customer_id = request.POST.get('customer_id')
cr.execute(f"SELECT * FROM sale_order WHERE customer_id = customer_id")

# ✅ RIGHT - Parameterized
customer_id = request.POST.get('customer_id')
cr.execute("SELECT * FROM sale_order WHERE customer_id = %s", (customer_id,))

# ❌ WRONG - Bypasses validation and triggers
cr = self.env.cr
cr.execute("UPDATE sale_order SET state = %s WHERE id = %s", ('done', order_id))

# ✅ RIGHT - Use ORM
order = self.env['sale.order'].browse(order_id)
order.state = 'done'  # Triggers validations, computed fields, etc.