Object-Relational Mapping (ORM)¶

Netforce provides a powerful and efficient ORM that directly interfaces with PostgreSQL. The ORM handles all database operations through the base Model class and provides an intuitive Python interface for database manipulation.

Overview¶

The Netforce ORM has several key characteristics:

Direct SQL Generation - Each method generates and executes raw PostgreSQL queries
JSON-RPC Compatible - All CRUD operations use JSON-serializable parameters for remote calls
Browse Records - Memory objects for navigating relationships with dot notation
Lazy Loading - Efficient loading of related data with built-in caching
Multi-Company Support - Built-in data isolation for multi-tenant applications
Access Control - Integrated permission checking at the ORM level

Core CRUD Operations¶

Create Records¶

The create() method inserts new records into the database:

# Create single record
contact_id = get_model("contact").create({
    "name": "John Doe",
    "email": "john@example.com", 
    "is_customer": True
})

# Create multiple records
product_ids = get_model("product").create([
    {"name": "Product A", "sale_price": 100.00},
    {"name": "Product B", "sale_price": 200.00}
])

Key Features: - Automatic timestamp and user tracking (create_time, create_uid, write_time, write_uid) - Password field encryption - Multi-company field handling - One2Many and Many2Many relationship processing - Custom field support - Audit logging

Read Records¶

The read() method retrieves record data from the database:

# Read specific fields
data = get_model("contact").read([1, 2, 3], ["name", "email", "phone"])
# Returns: [{"id": 1, "name": "John", "email": "john@example.com", "phone": "123"}, ...]

# Read all stored fields
data = get_model("contact").read([1, 2, 3])

# Read with function fields
data = get_model("sale.order").read([1], ["amount_total", "line_count"])

Key Features: - Selective field loading - Function field computation - Multi-company field resolution - Translation support - Custom field loading - Related field eager loading

Update Records¶

The write() method updates existing records:

# Update single field
get_model("contact").write([1, 2], {"phone": "+1-555-0123"})

# Update multiple fields
get_model("contact").write([1], {
    "phone": "+1-555-0123",
    "email": "newemail@example.com",
    "active": True
})

# Update with optimistic locking
get_model("contact").write([1], {"phone": "123"}, check_time="2023-01-01 10:00:00")

Key Features: - Automatic write timestamp and user tracking - Optimistic locking with check_time - Translation field handling - Multi-company field updates - Password encryption - History recording (if enabled)

Delete Records¶

The delete() method removes records from the database:

# Delete records
get_model("contact").delete([1, 2, 3])

Key Features: - Cascade deletion handling - Reference integrity checks - Audit logging - Auto-sync for offline systems - Permission checks

Search Records¶

The search() method finds records matching criteria:

# Basic search
ids = get_model("contact").search([
    ["is_customer", "=", True],
    ["active", "=", True]
])

# Complex search with OR logic
ids = get_model("contact").search([
    "|",
    ["email", "ilike", "%@gmail.com"],
    ["email", "ilike", "%@yahoo.com"]
])

# Search with ordering and limits
ids = get_model("account.invoice").search([
    ["type", "=", "out"],
    ["date_invoice", ">=", "2023-01-01"]
], order="date_invoice desc", limit=10)

# Count only
count = get_model("contact").search([
    ["is_customer", "=", True]
], count=True)

Search Condition Syntax¶

Basic Structure¶

Search conditions use a nested list structure where: - Default behavior: List of clauses uses AND conjunction: [clause1, clause2, ...] - OR disjunction: Use ["or", clause1, clause2, ...] - Nesting: Conditions can be nested: [clause1, ["or", clause2, clause3]]

Clause Format¶

Each clause follows the format: ["field", "operator", "value"]

Comparison Operators¶

Operator	Description	Example
`=`	Equals	`["name", "=", "John"]`
`!=`	Not equals	`["active", "!=", False]`
`<`, `>`, `<=`, `>=`	Comparison	`["amount", ">", 100]`
`~=`	Equals if not null	`["optional_field", "~=", "value"]`

List and Pattern Operators¶

Operator	Description	Example
`in`	Value in list	`["state", "in", ["draft", "open"]]`
`not in`	Value not in list	`["status", "not in", ["cancelled"]]`
`between`	Range (inclusive)	`["date", "between", ["2023-01-01", "2023-12-31"]]`
`like`	Pattern match (case sensitive)	`["name", "like", "%John%"]`
`ilike`	Pattern match (case insensitive)	`["name", "ilike", "%doe%"]`
`=like`	Exact pattern match	`["code", "=like", "CUST_001"]`
`=ilike`	Exact pattern match (case insensitive)	`["email", "=ilike", "john@example.com"]`
`not ilike`	Pattern not match	`["name", "not ilike", "%test%"]`

Hierarchical Operators¶

Operator	Description	Example
`child_of`	Include children in hierarchy	`["category_id", "child_of", 5]`
`not child_of`	Exclude children	`["category_id", "not child_of", 3]`
`child_of<`	Children only (exclude self)	`["parent_id", "child_of<", 1]`
`parent_of`	Include parents in hierarchy	`["category_id", "parent_of", [10, 20]]`

Null Handling¶

# Check for null values
[["field", "=", None]]           # field IS NULL
[["field", "!=", None]]          # field IS NOT NULL

# Conditional equality (equals if not null)
[["optional_field", "~=", "value"]]  # (value IS NULL OR field = value)

Logical Operators and Nesting¶

AND Logic (Default)¶

# These are equivalent
[["is_customer", "=", True], ["active", "=", True]]
[["and", ["is_customer", "=", True], ["active", "=", True]]]

OR Logic¶

# Simple OR
[["or", ["email", "ilike", "%gmail%"], ["email", "ilike", "%yahoo%"]]]

# Multiple OR conditions  
[["or", 
    ["name", "=", "John"], 
    ["name", "=", "Jane"], 
    ["name", "=", "Bob"]
]]

NOT Logic¶

# Negate a condition
[["not", ["state", "=", "cancelled"]]]

# Negate complex condition
[["not", ["or", ["active", "=", False], ["archived", "=", True]]]]

Complex Nested Logic¶

# (A AND B) OR (C AND D)
[["or",
    [["is_customer", "=", True], ["active", "=", True]],
    [["is_supplier", "=", True], ["approved", "=", True]]
]]

# A AND (B OR C) AND D
[
    ["field_a", "=", "value_a"],
    ["or", ["field_b", "=", "value_b"], ["field_c", "=", "value_c"]],
    ["field_d", "=", "value_d"]
]

# NOT (A OR B) AND C
[
    ["not", ["or", ["deleted", "=", True], ["archived", "=", True]]],
    ["active", "=", True]
]

Advanced Examples¶

Customer Search with Multiple Criteria¶

# Find active customers with Gmail email or high credit limit
condition = [
    ["is_customer", "=", True],
    ["active", "=", True],
    ["or",
        ["email", "ilike", "%@gmail.com"],
        ["credit_limit", ">", 10000]
    ]
]

Date Range with Status Filter¶

# Invoices from 2023 that are open or paid
condition = [
    ["type", "=", "out"],
    ["date_invoice", "between", ["2023-01-01", "2023-12-31"]],
    ["or",
        ["state", "=", "open"],
        ["state", "=", "paid"]
    ]
]

Hierarchical Category Search¶

# Products in electronics category or its subcategories
condition = [
    ["active", "=", True],
    ["category_id", "child_of", electronics_category_id]
]

Complex Business Logic¶

# Find orders that need attention:
# (overdue invoices) OR (large pending orders) OR (VIP customer orders)
condition = [
    ["or",
        # Overdue invoices
        [["state", "=", "confirmed"], ["due_date", "<", today]],
        # Large pending orders  
        [["state", "=", "draft"], ["amount_total", ">", 50000]],
        # VIP customer orders
        ["customer_id.vip", "=", True]
    ]
]

Relationship Field Searches¶

Access related model fields using dot notation:

# Customer's company information
[["contact_id.company_id.name", "ilike", "%Corporation%"]]

# Invoice lines with specific products
[["lines.product_id.category_id.name", "=", "Electronics"]]

# Multi-level relationships
[["customer_id.company_id.country_id.code", "=", "US"]]

Performance Considerations¶

Efficient Searches¶

# Good: Specific conditions first
[["type", "=", "out"], ["date", ">=", "2023-01-01"], ["amount", ">", 1000]]

# Good: Use indexes
[["state", "=", "open"]]  # If state is indexed

# Good: Limit OR conditions
[["or", ["status", "=", "A"], ["status", "=", "B"]]]  # Better than many ORs

Avoid Performance Issues¶

# Avoid: Leading wildcards
[["name", "like", "%suffix"]]  # Cannot use index

# Better: Non-leading wildcards  
[["name", "like", "prefix%"]]  # Can use index

# Avoid: Too many OR conditions
[["or", clause1, clause2, ..., clause50]]  # Slow

# Better: Use IN operator
[["field", "in", [val1, val2, ..., val50]]]  # Faster

Browse Records and Dot Notation¶

The browse() method returns BrowseRecord objects that provide intuitive access to record data and relationships:

Basic Browse Usage¶

# Browse single record
contact = get_model("contact").browse(1)
print(contact.name)          # Access field value
print(contact.email)         # Lazy loading

# Browse multiple records  
contacts = get_model("contact").browse([1, 2, 3])
for contact in contacts:
    print(f"{contact.name}: {contact.email}")

Dot Notation for Relationships¶

The power of browse records is in navigating relationships:

# Access Many2One relationships
invoice = get_model("account.invoice").browse(1)
print(invoice.contact_id.name)           # Customer name
print(invoice.contact_id.email)          # Customer email
print(invoice.contact_id.company_id.name) # Customer's company name

# Access One2Many relationships
order = get_model("sale.order").browse(1)
for line in order.lines:                 # Iterate over order lines
    print(f"Product: {line.product_id.name}")
    print(f"Quantity: {line.qty}")
    print(f"Price: {line.unit_price}")

# Deep relationship traversal
for line in order.lines:
    product = line.product_id
    category = product.category_id
    print(f"Line: {line.qty} x {product.name} ({category.name})")

BrowseRecord Implementation¶

Here's how the dot notation works internally:

class BrowseRecord(object):
    def __getattr__(self, name):
        # Called when accessing obj.field_name
        return self[name]

    def __getitem__(self, name):
        # Load field value from cache or database
        if name not in cache:
            # Load missing fields for related records
            missing_ids = [id for id in related_ids if not cached]
            data = model.read(missing_ids, [name])

            # Convert relationship fields to BrowseRecord objects
            if field_type == "Many2One":
                value = BrowseRecord(relation_model, field_value, ...)
            elif field_type in ("One2Many", "Many2Many"):  
                value = BrowseList(relation_model, field_values, ...)

        return cached_value

Lazy Loading and Caching¶

Browse records implement efficient lazy loading:

# Only loads 'name' field initially
contact = get_model("contact").browse(1)
print(contact.name)

# Loads 'email' field when first accessed  
print(contact.email)

# Related records are cached for efficiency
print(contact.company_id.name)    # Loads company data
print(contact.company_id.address) # Uses cached company data

Method Calls on Browse Records¶

Browse records can call model methods:

invoice = get_model("account.invoice").browse(1)

# Call methods on the record
invoice.validate()           # Calls validate([1])
invoice.post()              # Calls post([1]) 
total = invoice.calculate_total() # Calls calculate_total([1])

# Access computed properties
print(invoice.amount_total)  # Function field
print(invoice.state)        # Current state

BrowseList for Collections¶

When browsing multiple records or accessing One2Many/Many2Many fields, you get a BrowseList object:

# Browse multiple records returns BrowseList
contacts = get_model("contact").browse([1, 2, 3])
print(f"Found {len(contacts)} contacts")

# Iterate over BrowseList
for contact in contacts:
    print(f"Contact: {contact.name}")

# Access by index
first_contact = contacts[0]
last_contact = contacts[-1]

# Access by ID
contact = contacts.by_id(2)

# Slice operations
first_two = contacts[:2]

# One2Many fields return BrowseList
order = get_model("sale.order").browse(1)
order_lines = order.lines  # BrowseList of order lines

for line in order_lines:
    print(f"Product: {line.product_id.name}, Qty: {line.qty}")

# Call methods on all records in the list
contacts.update_last_contact_date()  # Calls method on all IDs

BrowseList Features: - Behaves like a Python list (iteration, indexing, slicing) - Contains BrowseRecord objects for each item - Provides by_id() method for ID-based lookup - Supports method calls on the entire collection - Lazy loading for efficient memory usage

Search and Browse Combined¶

Common patterns combine search and browse:

# search_browse: search then browse results
customers = get_model("contact").search_browse([
    ["is_customer", "=", True],
    ["active", "=", True]
])

for customer in customers:
    print(f"Customer: {customer.name}")
    print(f"Total Sales: {customer.total_sales}")

    # Access related invoices
    for invoice in customer.invoices:
        print(f"  Invoice {invoice.number}: ${invoice.amount_total}")

# search_read: search then read (returns dictionaries)
customer_data = get_model("contact").search_read([
    ["is_customer", "=", True]
], ["name", "email", "total_sales"])

for data in customer_data:
    print(f"Customer: {data['name']} - {data['email']}")

JSON-RPC Compatibility¶

All CRUD methods use JSON-serializable parameters, making them perfect for remote calls:

# These calls can be made remotely via JSON-RPC
params = [
    "contact",           # model name
    "create",           # method name  
    [{                  # arguments (JSON-serializable)
        "name": "John Doe",
        "email": "john@example.com"
    }],
    {},                 # options
    {"user_id": 1, "token": "abc123"}  # context
]

# Browse records are NOT JSON-serializable (memory objects only)
# So browse() cannot be called remotely - only locally
contact = get_model("contact").browse(1)  # Local only

Performance Optimizations¶

Eager Loading¶

Mark fields for eager loading to reduce queries:

class SaleOrder(Model):
    _name = "sale.order"
    _fields = {
        "contact_id": fields.Many2One("contact", "Customer", eager_load=True),
        "lines": fields.One2Many("sale.order.line", "order_id", "Lines"),
    }

# When browsing, contact data is loaded immediately
order = get_model("sale.order").browse(1)
print(order.contact_id.name)  # No additional query needed

Batch Operations¶

Process multiple records efficiently:

# Instead of individual creates
for data in customer_list:
    get_model("contact").create(data)  # Multiple queries

# Use batch create
get_model("contact").create(customer_list)  # Single query

# Batch browse operations
contacts = get_model("contact").browse([1, 2, 3, 4, 5])
# Loads all records in single query when first field accessed
names = [c.name for c in contacts]

Field Selection¶

Only load fields you need:

# Load all fields (slow)
invoices = get_model("account.invoice").read([1, 2, 3])

# Load specific fields only (fast)  
invoices = get_model("account.invoice").read([1, 2, 3], 
    ["number", "date_invoice", "amount_total"])

Multi-Company Support¶

The ORM automatically handles multi-company data:

# Set active company context
access.set_active_company(1)

# All operations are automatically filtered by company
customers = get_model("contact").search([["is_customer", "=", True]])
# Only returns customers for company 1

# Multi-company fields are automatically isolated
product = get_model("product").browse(1)
print(product.list_price)  # Company-specific price

Error Handling¶

The ORM provides structured error handling:

try:
    contact_id = get_model("contact").create({
        "name": "John Doe",
        "email": "invalid-email"  # Invalid format
    })
except ValidationError as e:
    print(f"Validation error: {e}")
    print(f"Field errors: {e.error_fields}")

try:
    get_model("contact").delete([1])
except Exception as e:
    if "foreign key" in str(e):
        print("Cannot delete: record is referenced by other records")

Advanced Features¶

Custom Fields¶

Access dynamically created fields:

# Custom fields are automatically loaded when accessed
contact = get_model("contact").browse(1)
print(contact.custom_field_1)  # Custom field value

Audit Logging¶

Automatic audit trail for all changes:

# All create/write/delete operations are automatically logged
contact_id = get_model("contact").create({"name": "John"})
get_model("contact").write([contact_id], {"email": "john@example.com"})

# View audit logs
logs = get_model("audit.log").search_browse([
    ["model", "=", "contact"],
    ["record_id", "=", contact_id]
])
for log in logs:
    print(f"{log.action} by {log.user_id.name} at {log.date}")

Constraints and Validation¶

Database and Python constraints:

class Contact(Model):
    _name = "contact"

    # SQL constraints
    _sql_constraints = [
        ("unique_email", "unique (email)", "Email must be unique"),
        ("positive_credit_limit", "check (credit_limit >= 0)", 
         "Credit limit cannot be negative")
    ]

    # Python constraints
    _constraints = ["check_email_format"]

    def check_email_format(self, ids):
        for contact in self.browse(ids):
            if contact.email and "@" not in contact.email:
                raise ValidationError("Invalid email format")

Best Practices¶

# Good: Use browse for relationship navigation
order = get_model("sale.order").browse(1)
customer_name = order.contact_id.name

# Avoid: Multiple read operations  
order_data = get_model("sale.order").read([1], ["contact_id"])
contact_data = get_model("contact").read([order_data[0]["contact_id"]], ["name"])
customer_name = contact_data[0]["name"]

2. Batch Operations When Possible¶

# Good: Batch create
get_model("contact").create([
    {"name": "Customer A"},
    {"name": "Customer B"},  
    {"name": "Customer C"}
])

# Avoid: Individual creates in loop
for name in ["Customer A", "Customer B", "Customer C"]:
    get_model("contact").create({"name": name})

3. Select Fields Explicitly for Large Datasets¶

# Good: Select needed fields only
invoices = get_model("account.invoice").search_read([
    ["state", "=", "open"]
], ["number", "amount_total", "due_date"])

# Avoid: Loading all fields for large datasets
invoices = get_model("account.invoice").search_read([
    ["state", "=", "open"]
])  # Loads all fields

4. Use Search Efficiently¶

# Good: Use specific conditions
recent_invoices = get_model("account.invoice").search([
    ["date_invoice", ">=", "2023-01-01"],
    ["type", "=", "out"]
])

# Avoid: Loading all then filtering
all_invoices = get_model("account.invoice").search([])
recent_invoices = [i for i in all_invoices if ...]  # Filter in Python

Summary¶

The Netforce ORM provides:

Direct SQL Operations - Efficient database queries
Intuitive API - Pythonic interface for database operations
Browse Records - Object-oriented access with dot notation
Relationship Navigation - Seamless traversal of related data
Lazy Loading - Automatic optimization with caching
JSON-RPC Compatible - Remote procedure call support
Multi-Company Ready - Built-in multi-tenancy
Access Control - Integrated permission system

This combination makes the Netforce ORM both powerful and easy to use, whether you're building simple CRUD applications or complex business systems with intricate relationships.

Next Steps¶

Learn about Models for defining model structure
Explore API Reference for JSON-RPC usage
Check Security for access control patterns
Review Multi-Company for multi-tenant development