Writing Effective Domain Filters in Odoo: Complex Query Patterns

Domain Filter Essentials

1.Prefix Notation: Operators BEFORE operands (&, |, ! come first)

2.Operators: =, !=, >, <, >=, <=, in, not in, like, ilike, child_of

3.Relational Fields: Dot notation (partner_id.country_id.code)

4.Computed Fields: Must have store=True to be searchable

5.Performance: Index frequently-searched fields, use limit

Quick Answer

Domain filters are Odoo's query language. Basic syntax: [('field', 'operator', value)]. Multiple conditions = AND by default. Logical operators (prefix notation): & (AND), | (OR), ! (NOT)—operators come BEFORE operands. Common operators: = (equals), != (not equals), >, <, >=, <=, in (list match), not in, like/ilike (text search, case-insensitive), child_of (hierarchical). Relational fields: Use dot notation: partner_id.country_id.code (traverse Many2one). Computed fields: MUST have store=True to be searchable. Performance: Index frequently-searched fields (index=True), use limit to avoid huge result sets. Wrong domains = too many/zero records, slow queries, logic errors = $24k-$60k wasted productivity.

The Domain Filter Problem

Your D2C brand's sales team needs to find orders for a specific customer in a specific country that are over $5,000, placed in the last 30 days, and NOT yet shipped.

Simple Domain

Basic Domain Filter

[('state', '=', 'sale')]

Complex Domain

Complex Multi-Condition Domain

['&',
    ('partner_id.country_id', '=', 'US'),
    ('amount_total', '>', 5000),
    '&',
    ('create_date', '>', datetime.now() - timedelta(days=30)),
    ('state', '!=', 'done')
]

We've implemented 150+ Odoo systems. The ones where developers master domain filters? Reports run in seconds. Dropdowns filter intelligently. Search is instant. The ones who don't? Performance crawls. Users get frustrated. Reports timeout. That's $24,000-$60,000 in wasted productivity and system frustration.

Domain Syntax Basics

Basic Structure

Domain Syntax

domain = [
    (field_name, operator, value),
]

Example

Simple Equality Filter

domain = [('state', '=', 'sale')]  # Find orders with state = 'sale'

Multiple Conditions (AND by Default)

Multiple Conditions - Implicit AND

domain = [
    ('state', '=', 'sale'),
    ('amount_total', '>', 1000),
]
# Means: state = 'sale' AND amount_total > 1000

All Domain Operators

Comparison Operators

Operator	Meaning	Example
=	Equals	`('state', '=', 'sale')`
!=	Not equals	`('state', '!=', 'draft')`
>	Greater than	`('amount_total', '>', 1000)`
<	Less than	`('qty_available', '<', 10)`
>=	Greater than or equal	`('create_date', '>=', cutoff_date)`
<=	Less than or equal	`('amount_total', '<=', 5000)`
in	Value in list	`('state', 'in', ['draft', 'sent'])`
not in	Value not in list	`('state', 'not in', ['cancel', 'done'])`
like	Text contains (case-sensitive)	`('name', 'like', 'Organic%')`
ilike	Text contains (case-insensitive)	`('name', 'ilike', 'organic')`
child_of	Hierarchical (includes children)	`('department_id', 'child_of', dept_id)`

Logical Operators

AND (Default, No Symbol Needed)

AND - Both Conditions Must Be True

# Both conditions must be true
domain = [
    ('state', '=', 'sale'),
    ('amount_total', '>', 1000),
]
# Same as: '&', ('state', '=', 'sale'), ('amount_total', '>', 1000)

OR (Use | Prefix)

OR - At Least One Condition Must Be True

# At least one condition must be true
domain = [
    '|',
    ('state', '=', 'draft'),
    ('state', '=', 'sent'),
]
# Means: state = 'draft' OR state = 'sent'

NOT (Use ! Prefix)

NOT - Negate a Condition

# Negate a condition
domain = [
    '!',
    ('state', '=', 'draft'),
]
# Means: state != 'draft' (same as using !=)

Complex Combinations

(Field1=A OR Field2=B) AND Field3=C

domain = [
    '&',
    '|',
    ('field1', '=', 'A'),
    ('field2', '=', 'B'),
    ('field3', '=', 'C'),
]

Key Insight: Prefix Notation

Odoo uses PREFIX notation, not infix. Operators come BEFORE their operands.

Prefix vs Infix Notation

# WRONG (Infix - like normal math)
# ('field1', '=', 'A') OR ('field2', '=', 'B') AND ('field3', '=', 'C')

# RIGHT (Prefix - Odoo style)
[
    '&',
    '|',
    ('field1', '=', 'A'),
    ('field2', '=', 'B'),
    ('field3', '=', 'C'),
]

Real D2C Examples (Complex Patterns)

Example 1: High-Value Orders in Last 30 Days (Not Yet Shipped)

Requirement: Find orders where: Amount > $5,000, Created in last 30 days, NOT shipped yet

High-Value Recent Orders

from datetime import datetime, timedelta

cutoff_date = datetime.now() - timedelta(days=30)

domain = [
    ('amount_total', '>', 5000),
    ('create_date', '>=', cutoff_date),
    ('state', '!=', 'done'),  # Not shipped
]

# Usage
high_value_orders = self.env['sale.order'].search(domain)

Example 2: Active Customers in Specific Countries with Tags

Requirement: Find customers where: Country is US OR Canada OR Mexico, Active (not archived), Has "VIP" OR "Wholesale" tag

Multi-Country VIP Customers

domain = [
    '&',
    ('active', '=', True),
    '&',
    ('country_id', 'in', ['US', 'CA', 'MX']),  # US, Canada, Mexico
    '|',
    ('tag_ids', 'ilike', 'VIP'),
    ('tag_ids', 'ilike', 'Wholesale'),
]

# Usage
customers = self.env['res.partner'].search(domain)

Example 3: Inventory Low Stock with Expensive Items

Requirement: Find products where: Stock < reorder level, Price > $100, NOT discontinued, Category is "Electronics" OR "Apparel"

Low Stock Expensive Products

domain = [
    '&',
    '&',
    '&',
    ('qty_available', '<', 10),  # Low stock (assume reorder level = 10)
    ('list_price', '>', 100),
    ('state', '!=', 'discontinued'),
    '|',
    ('categ_id', 'ilike', 'Electronics'),
    ('categ_id', 'ilike', 'Apparel'),
]

low_stock_expensive = self.env['product.product'].search(domain)

Example 4: Orders with Undelivered Items (Partial Shipments)

Requirement: Find orders where: State = 'sale' (confirmed), At least one line item hasn't been fully delivered, Amount > $500

Partial Shipment Orders

domain = [
    '&',
    '&',
    ('state', '=', 'sale'),
    ('amount_total', '>', 500),
    ('order_line', 'any', [
        ('qty_delivered', '<', 'product_qty')
    ]),
]

partial_orders = self.env['sale.order'].search(domain)

Example 5: Invoices Overdue (Unpaid and Past Due Date)

Requirement: Find invoices where: State = 'open' (unpaid), Due date < today, Amount > $100

Overdue Invoices

from datetime import date

domain = [
    '&',
    '&',
    ('state', '=', 'open'),
    ('invoice_date_due', '<', date.today()),
    ('amount_residual', '>', 100),  # Still owes money
]

overdue_invoices = self.env['account.move'].search(domain)

Advanced Pattern: Dynamic Domains with Computed Fields

Problem: You need to filter based on a computed field value.

Computed Field with store=True

class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    x_profit_margin = fields.Float(
        compute='_compute_profit_margin',
        store=True,  # IMPORTANT: Must store to search/filter
        help='Profit margin percentage'
    )
    
    @api.depends('amount_total', 'amount_cost')
    def _compute_profit_margin(self):
        for record in self:
            if record.amount_total > 0:
                margin = ((record.amount_total - record.amount_cost) / record.amount_total) * 100
                record.x_profit_margin = margin
            else:
                record.x_profit_margin = 0

Now you can filter by the computed field:

Filter by Computed Field

# Find high-margin orders
domain = [
    ('x_profit_margin', '>', 40),  # Profit margin > 40%
]

high_margin = self.env['sale.order'].search(domain)

Key: The computed field MUST have store=True to be searchable.

Pattern: Relational Field Domains

Many2one Domain (Filter by Related Record's Field)

Dot Notation Traverses Relationships

# Find orders from customers in US
domain = [
    ('partner_id.country_id.code', '=', 'US'),
]

# Dot notation traverses relationships
# partner_id -> Many2one to res.partner
# country_id -> Many2one to res.country
# code -> Field on res.country

us_orders = self.env['sale.order'].search(domain)

Many2many Domain (Filter by Related Records)

Many2many Tag Filtering

# Find products with "organic" tag
domain = [
    ('tag_ids', 'ilike', 'organic'),
]

# Or with multiple matches
domain = [
    ('tag_ids', 'in', [tag1_id, tag2_id, tag3_id]),
]

organic_products = self.env['product.product'].search(domain)

Hierarchical Domain (Parent-Child Relationships)

child_of Operator

# Find all employees under a specific department (including subdepartments)
domain = [
    ('department_id', 'child_of', department_id),
]

all_dept_employees = self.env['hr.employee'].search(domain)

# Or find employees whose manager is a specific person
domain = [
    ('parent_id', 'child_of', manager_id),
]

direct_reports = self.env['hr.employee'].search(domain)

Pattern: Context-Based Dynamic Domains

Problem: Domain needs to change based on current user or context.

Dynamic Domain in Python

class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    partner_id = fields.Many2one(
        'res.partner',
        domain=lambda self: self._get_partner_domain(),
        string='Customer'
    )
    
    def _get_partner_domain(self):
        """Filter partners by current user's country."""
        user_country = self.env.user.partner_id.country_id
        if user_country:
            return [('country_id', '=', user_country.id)]
        return []  # No filter if user has no country

Performance Optimization

Problem: Domain with 100,000 records is slow.

Solution: Use Indexed Fields

Add index=True to Frequently-Searched Fields

class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    # Add index=True to frequently-searched fields
    customer_id = fields.Many2one(index=True)
    state = fields.Selection(index=True)
    create_date = fields.Datetime(index=True)

Fast vs Slow Queries

Indexed vs Unindexed Field Searches

# FAST (indexed fields)
domain = [
    ('state', '=', 'sale'),
    ('create_date', '>', cutoff_date),
]

# SLOW (searching in unindexed field)
domain = [
    ('notes', 'ilike', 'urgent'),  # No index on notes
]

Use Limit to Avoid Loading Huge Datasets

Limit Result Set Size

# Get only first 100 records
orders = self.env['sale.order'].search(
    domain,
    limit=100,
    order='create_date DESC'
)

Action Items: Master Domain Filters

Learn the Operators

❏ Master basic operators (=, !=, >, <, in, like, ilike)

❏ Learn logical operators (&, |, !)

❏ Practice relational field notation (dots for traversal)

❏ Understand hierarchical operators (child_of, parent_of)

Build Complex Domains

❏ Start simple, build up complexity

❏ Test each condition separately first

❏ Use prefix notation consistently

❏ Validate domain logic before deployment

Optimize Performance

❏ Index frequently-searched fields

❏ Use limit to avoid huge result sets

❏ Cache computed fields (store=True)

❏ Test domains on large datasets

Frequently Asked Questions

What is prefix notation and why does Odoo use it?

Prefix notation means operators come BEFORE operands. Example: ['&', '|', ('field1', '=', 'A'), ('field2', '=', 'B'), ('field3', '=', 'C')] = (field1=A OR field2=B) AND field3=C. The & comes first, then |, then conditions. Odoo uses this because it's unambiguous—no need for parentheses to define order of operations. Common mistake: Writing infix notation like normal math. Always put logical operators (&, |, !) BEFORE the conditions they operate on.

How do I filter by a field in a related record (Many2one)?

Use dot notation to traverse relationships. Example: [('partner_id.country_id.code', '=', 'US')] finds orders where partner_id (Many2one to res.partner) → country_id (Many2one to res.country) → code field = 'US'. You can chain multiple levels: field.related_field.nested_field. This works for Many2one fields. For Many2many, use ('tag_ids', 'ilike', 'organic') or ('tag_ids', 'in', [id1, id2]).

Can I filter by a computed field?

Yes, but only if the computed field has store=True. Computed fields without store=True are calculated on-the-fly and don't exist in the database—you can't search them. Example: x_profit_margin = fields.Float(compute='_compute_profit_margin', store=True). Now you can filter: [('x_profit_margin', '>', 40)]. Without store=True, this domain fails because the field isn't stored. Performance note: Storing computed fields uses more database space but enables searching and faster access.

How do I optimize slow domain queries?

Three main optimizations: (1) Index frequently-searched fields: Add index=True to field definitions (state = fields.Selection(index=True)). Database indexes make searches 10-100x faster. (2) Use limit: search(domain, limit=100) to avoid loading huge datasets. (3) Store computed fields: store=True on computed fields caches values. Avoid: Searching unindexed text fields with ilike (very slow on large datasets). Test domains on production-size datasets to catch performance issues early.

Free Domain Optimization Workshop

Stop writing broken, slow domains. We'll teach you every domain operator, show you complex query patterns, help you debug failing domains, optimize slow domains for performance, and design domains for your specific scenarios. Most D2C brands waste hours debugging domains that have simple logic errors. Learning to master domains saves $15,000-$30,000 in unnecessary debugging time.