How Odoo ORM search() Works. From Domain to Raw SQL

Introduction

Odoo ORM is often described as “high-level” or “Pythonic”. That description hides the real mechanism: search() is a domain compiler that translates symbolic constraints into SQL. It does not walk relations dynamically. It does not iterate Python objects. It constructs a query plan.

Understanding this translation is mandatory if you care about correctness, performance, or predictability.

Example

self.env['sale.order'].search(
    [('partner_id.name', '=', self.receiver_name)],
    limit=1
)

This is not a simple filter. It is a relational domain traversal with implicit behavior you did not write.

What search() Actually Does

At a high level, search() executes the following phases:

1. Domain parsing
2. Relational path resolution
3. Context-based filtering
4. SQL generation
5. Query execution (recognition phase)
6. Lazy recordset instantiation

1. Domain Parsing

The domain:

[('partner_id.name', '=', value)]

Is parsed into an expression tree:

• Left operand: partner_id.name
• Operator: =
• Right operand: value

The ORM identifies:

• partner_id as a many2one
• name as a column on the related model

2. Relational Path Resolution

partner_id.name means:

• Base model: sale.order
• Foreign key: partner_id
• Target table: res_partner
• Target column: name

This cannot be evaluated on sale_order alone.

3. The SQL Shape (Simplified)

One valid form of the generated SQL:

SELECT id FROM sale_order so
JOIN res_partner rp
    ON so.partner_id = rp.id
WHERE rp.name = %s
  AND so.active = true
  AND so.company_id IN (%s)
LIMIT 1;

4. What search() Returns

search() returns:

• A recordset of IDs
• Not full records
• Not loaded fields

Actual field data is fetched lazily on access.

Conclusion

Odoo ORM search() is not magic. It is a constrained SQL generator with security, context, and abstraction layered on top. Relational domains are powerful, but they are not free. Every dot in a domain (a.b.c) is a potential join, subquery, or planner cost.