Skip to content
Home » ORM SQL Queries Odoo

ORM SQL Queries Odoo

ORM SQL Queries Odoo

Tutorial & Best Practices

ORM SQL Queries Odoo empower developers to balance the ease of object-relational mapping with the power of raw SQL. In this tutorial, you will learn how to use ORM SQL Queries Odoo effectively, and you will see clear code examples, performance tips, and advanced patterns. Moreover, you will find outgoing links to official documentation for deeper dives.

What Are ORM SQL Queries in Odoo?

First, It combine two database interaction methods: the Odoo ORM and raw SQL queries. Therefore, you can choose the right tool for each task. Furthermore, you maintain code consistency and performance by balancing these approaches.

Odoo ORM vs Raw SQL Queries

  • Odoo ORM uses Python methods like search(), create(), and write(). It prevents SQL injection and caches results.
  • Raw SQL Queries run directly via env.cr.execute(). They suit complex reports and bulk operations where speed matters.

When to Use ORM SQL Queries in Odoo

  • Use ORM for most CRUD work and business logic. It ensures security and readability.
  • Use Raw SQL for heavy data analysis, complex joins, or when you need database views. It delivers maximum speed.

Setting Up Your Odoo Development Environment

To apply effectively, first set up your environment properly.

Installing Odoo 18

  1. Clone the Odoo repository
   git clone https://github.com/odoo/odoo.git --depth 1 --branch 18.0
   cd odoo
  1. Install dependencies
   python3 -m pip install -r requirements.txt
  1. Configure PostgreSQL
   sudo -u postgres createuser --createdb --login odoo18
   sudo -u postgres createdb --owner=odoo18 odoo18db
  1. Run Odoo
   ./odoo-bin -c ./debian/odoo.conf

Next, enable developer mode from the Settings app to access advanced features.

Creating Your First Custom Module

Then, scaffold a simple module named example_orm_sql.

cd addons
mkdir example_orm_sql
cd example_orm_sql
mkdir models views
touch __init__.py __manifest__.py models/__init__.py views/__init__.py

Edit __manifest__.py:

{
    'name': 'Example ORM SQL Tutorial',
    'version': '1.0',
    'category': 'Tools',
    'depends': ['base', 'sale', 'point_of_sale'],
    'data': ['views/example_views.xml'],
    'installable': True,
}

Working with Odoo ORM SQL Queries in Odoo

After setup, start using it in your models and controllers.

Basic ORM Operations

First, open models/example_models.py:

from odoo import models, fields

class ExampleOrder(models.Model):
    _name = 'example.order'
    _description = 'Example Order'

    name         = fields.Char('Reference', required=True)
    partner_id   = fields.Many2one('res.partner', string='Customer')
    order_date   = fields.Date('Order Date')
    amount_total = fields.Monetary('Total Amount', currency_field='currency_id')
    currency_id  = fields.Many2one('res.currency', default=lambda self: self.env.company.currency_id.id)

Then, perform CRUD with ORM:

# Create a record
order = env['example.order'].create({
    'name': 'EX-2025-001',
    'partner_id': partner.id,
    'order_date': fields.Date.today(),
    'amount_total': 1200.00,
})

# Read records
orders = env['example.order'].search([('amount_total', '>', 1000)])
for o in orders:
    print(o.name, o.amount_total)

# Update a record
order.write({'amount_total': 1300.00})

# Delete a record
order.unlink()

Filtering with Domains

Second, apply domains for complex filters:

# Find orders this month with total > 500
from datetime import date
start = date.today().replace(day=1)
orders_month = env['example.order'].search([
    ('order_date', '>=', start),
    ('amount_total', '>', 500),
])

Moreover, you can combine OR & AND:

orders_special = env['example.order'].search([
    '|',
    ('amount_total', '>', 1000),
    ('partner_id.country_id.code', '=', 'US'),
])

Writing Raw SQL Queries in Odoo

Sometimes, you need raw power. Thus, turn to raw SQL for performance.

Using env.cr to Execute SQL

First, get the cursor and run SQL:

class ExampleReport(models.Model):
    _name = 'example.report'
    _auto = False

    def _select(self):
        return """
            SELECT
                row_number() OVER () AS id,
                so.id AS sale_order_id,
                so.date_order AS date,
                rp.name AS customer,
                SUM(sol.price_total) AS total
            """

    def _from(self):
        return """
            FROM sale_order_line sol
            JOIN sale_order so ON so.id = sol.order_id
            JOIN res_partner rp ON rp.id = so.partner_id
        """

    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._select()}
                {self._from()}
                GROUP BY so.id, so.date_order, rp.name
            )
        """)

Then, reading raw SQL results:

self.env.cr.execute("SELECT country_id, COUNT(*) FROM res_partner GROUP BY country_id")
for country_id, count in self.env.cr.fetchall():
    print(country_id, count)

Creating Database Views

Second, map a view to an Odoo model by setting _auto=False:

class PartnerStats(models.Model):
    _name = 'partner.stats'
    _description = 'Partner Statistics'
    _auto = False

    country_id = fields.Many2one('res.country', string='Country')
    total_partners = fields.Integer('Total Partners')

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(f"""
            CREATE VIEW {self._table} AS
            SELECT
                row_number() OVER () AS id,
                p.country_id,
                COUNT(*) AS total_partners
            FROM res_partner p
            GROUP BY p.country_id
        """)

Combining ORM and Raw SQL in Odoo

Balance the strengths of each method by combining them.

Unified Sales & PoS View Example

Next, you build a unified view of Sale Orders and PoS Orders. Place this in models/pos_sale_order.py:

from odoo import models, fields, tools

class PosSaleOrder(models.Model):
    _name = 'pos.sale.order'
    _auto = False
    _description = 'Pos & Sale Order'

    product_id = fields.Many2one('product.product', string='Product')
    partner_id = fields.Many2one('res.partner',   string='Customer')
    quantity   = fields.Float(    string='Quantity')
    price_unit = fields.Float(    string='Price Unit')
    date       = fields.Date(     string='Date')
    type       = fields.Selection([('sale','Sale'),('pos','Point of Sale')], string='Type')

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(f"""
            CREATE VIEW {self._table} AS
            SELECT
              row_number() OVER () AS id,
              sol.product_id,
              so.partner_id,
              sol.product_uom_qty AS quantity,
              sol.price_unit,
              so.date_order AS date,
              'sale' AS type
            FROM sale_order_line sol
            JOIN sale_order so ON so.id = sol.order_id
            WHERE so.state IN ('sale','done')
            UNION ALL
            SELECT
              pol.product_id,
              po.partner_id,
              pol.qty AS quantity,
              pol.price_unit,
              po.date_order AS date,
              'pos' AS type
            FROM pos_order_line pol
            JOIN pos_order po ON po.id = pol.order_id
        """)

Then define the view XML in views/pos_sale_order_views.xml:

<odoo>
  <data>
    <record id="view_pos_sale_order_tree" model="ir.ui.view">
      <field name="name">pos.sale.order.tree</field>
      <field name="model">pos.sale.order</field>
      <field name="arch" type="xml">
        <tree string="Combined Sales & PoS">
          <field name="type"/>
          <field name="date"/>
          <field name="product_id"/>
          <field name="partner_id"/>
          <field name="quantity"/>
          <field name="price_unit"/>
        </tree>
      </field>
    </record>
    <record id="action_pos_sale_order" model="ir.actions.act_window">
      <field name="name">Combined Sales & PoS</field>
      <field name="res_model">pos.sale.order</field>
      <field name="view_mode">tree</field>
    </record>
    <menuitem id="menu_pos_sale_order"
              name="Sales & PoS"
              parent="sale.sale_menu_root"
              action="action_pos_sale_order"/>
  </data>
</odoo>

As a result, users can view both transaction types in one place.

Best Practices for ORM SQL Queries in Odoo

Ensuring Security

First, avoid SQL injection by never concatenating untrusted input into SQL strings. Instead, use parameterized queries:

self.env.cr.execute(
    "SELECT * FROM sale_order WHERE partner_id = %s", (partner_id,)
)

Next, always validate user input and escape parameters properly.

Optimizing Performance

  • Use Domains instead of search() for simple filters.
  • Batch Writes: call write() on recordsets rather than in loops.
  • Leverage SQL Views for reporting to offload heavy aggregation to the database.

Moreover, add database indexes via _sql_constraints or raw SQL migrations:

_sql_constraints = [
    ('sale_order_date_idx', 'INDEX(date_order)', 'Date index for orders')
]

Maintaining Code Quality

  • Keep model definitions clear by grouping related fields.
  • Document your raw SQL with comments.
  • Write tests for both ORM methods and raw SQL functions.
  • Use consistent naming conventions in _name and table names.

Advanced Topics

Indexing and EXPLAIN Plans

First, analyze slow queries with PostgreSQL’s EXPLAIN ANALYZE. Then, add indexes:

CREATE INDEX idx_sale_order_partner ON sale_order(partner_id);

Next, revisit ORM search patterns to leverage indexed fields.

Batch Operations and Caching

Moreover, use Odoo’s read_group() for group-by operations via ORM:

data = env['sale.order'].read_group(
    domain=[('date_order', '>=', start_date)],
    fields=['partner_id', 'amount_total:sum'],
    groupby=['partner_id']
)

Additionally, implement caching for repeated queries:

from odoo.tools import cache

@cache('my_key_prefix', keys=['user.id'])
def expensive_report(self):
    # heavy computation
    return result

Resources & Further Reading

Conclusion

In this tutorial, you mastered this by exploring both ORM methods and raw SQL. Moreover, you learned when to apply each technique, saw clear code samples, and discovered best practices for security, performance, and maintainability. Finally, you built a unified sales and PoS view. Now, you can leverage techinque with confidence in your next project!

Discover more from teguhteja.id

Subscribe to get the latest posts sent to your email.

Tags:

Leave a Reply

WP Twitter Auto Publish Powered By : XYZScripts.com

Subscribe