Search code examples
pythonsqlmysqlpostgresqlodoo

Sequential SQL Updates in Odoo(Python) and Potential Conflicts

I have code in an Odoo module with two sequential SQL update statements on the gym_membership table. The first updates the state to 'confirmed' for qualifying memberships, and the second sets the state to 'draft' for a different set. I want clarification on their execution and potential conflicts.

Code:

self.env.cr.execute("UPDATE gym_membership SET state = 'confirmed' WHERE id IN (SELECT gym_membership_id FROM sale_order WHERE gym_membership_id IN (SELECT id FROM gym_membership WHERE state = 'finished' AND is_sessions_generated = False AND membership_type IN ('class','private')))")
self.env.cr.execute("UPDATE gym_membership SET state = 'draft' WHERE state = 'finished' AND is_sessions_generated = False AND membership_type IN ('class','private')")

Clarification:

  1. How these SQL queries will be executed sequentially in Odoo.
  2. Potential conflicts between updates by the first and second queries.
  3. Best practices for executing sequential SQL operations in Odoo.
Solution
    1. How these SQL queries will be executed sequentially in Odoo.

    It's the same cursor in your example so the first Query will be executed then the next and so on. It's already sequential.

    1. Potential conflicts between updates by the first and second queries.

    Because it's the same cursor there shouldn't be a conflict, because the state of the data for the second query is already the state after the first. So the second query could potentially update data already updated by the first query, but that's the way of sequentially executed queries.

    1. Best practices for executing sequential SQL operations in Odoo.

    Avoid SQL queries as much as possible, because you're bypassing Odoo's security features completely. Usually only very bad generated queries by Odoo should be optimized by using them, and even then only after trying to optimize the table itself (e. g. by special indeces).

    1. How to avoid SQL Queries in this case

    Use the ORM as much as possible. for your example:

    # first query in ORM
sale_domain = [
    ("gym_membership_id.state", "=", "finished"),
    ("gym_membership_id.is_sessions_generated", "=", False),
    ("gym_membership_id.membership_type", "in", ("class", "private")),
]
memberships_sale = self.env["sale.order"].search(sale_domain).mapped("gym_membership_id")
memberships_sale.write({"state": "confirmed"})

# second query in ORM
membership_domain = [
    ("state", "=", "finished"),
    ("is_sessions_generated", "=", False),
    ("membership_type", "in", ("class", "private")),
]
memberships = self.env["gym.membership"].search(membership_domain))
memberships.write({"state": "draft"})