SQL Execution
The cursor for the current database transaction is represented by the cr attribute on environments.
It allows you to run SQL directly rather than through the ORM, which is useful for queries that are difficult to define through the ORM. (e.g., complex joins) or for performance reasons:
self.env.cr.execute("some_sql", params)
While all models are using the same cursor and the Environment includes a variety of caches, changing the database in raw SQL requires invalidating these caches or else further the model usage would become incoherent. Clearing caches is required when using CREATE, UPDATE, or DELETE in SQL, but not when using SELECT (which simply reads the database).
Caches can be cleared using the invalidate cache() function.
invalidate_cache(fnames=None, ids=None)
Invalidate the record caches after some records have been modified. If both fnames and ids are None, the entire cache is cleared.
Parameters
- fnames – The list of modified fields, or None for all fields.
- ids – The list of modified record ids, or None for all.
Creating Records
New records are created for a certain model using the create() function. The values from the list of dicts vals list and, if necessary, those from the default get() function are used to initialize the new records.
Parameters
Field values as a list of dictionaries for the model:
[{'field_name': field_value, ...}, ...]
Eg:
partner = self.env['res.partner'].create({
'name':'Test Partner',
'is_company':True
})
An AccessError will be raised
- If the user is not required to grant access to the requested item.
- If the user tries to bypass access rules for creating the requested object.
A ValidationError will be raised
- If a user attempts to enter a value that is invalid for a field that is not a selection.
A UserError will be raised
- If the operation results in the creation of a loop in an object hierarchy (such as setting an object as its own parent)
Updating Records
It may occasionally be necessary to update records by changing the values of some of their fields.
We can use the update() method of a model for this.
A field value can be assigned to a record in one of three methods.
1. Assigning values directly to the attribute representing the field of the record.
- We can assign a value to a field directly.
self.test_boolean = True
self is the object of the record of which the record’s field value is updating.test_boolean is a boolean field.
2.update() method
- To change the value of a field, we can use the update() method.
self.update({
'start_date': fields.Date.today(),
'test_boolean': True
})
start date and test_boolean is the field which we need to update. We can pass to the update method a dictionary that maps the field names to the values you want to set.
3.write() method
- We can provide the update method with a dictionary that associates the field names with the values you want to set.
self.write({
'start_date': fields.Date.today(),
'test_boolean': True
})
- The last two options require one database call for each record and each field, whereas this method updates all records with the specified values in a single database operation and is applicable to recordsets of any size. It has some restrictions, though: If the records are not yet in the database, it does not work. When writing relational fields, also requires a special format similar to the one used by the create() method.
(0, 0, { values }) link to a new record that needs to be created with the given values dictionary.
(1, ID, { values }) update the linked record with id = ID (write values on it).
(2, ID) remove and delete the linked record with id = ID (calls unlink on ID, which will delete the object completely and the link to it as well).
(3, ID) cut the link to the linked record with id = ID (delete the relationship between the two objects but do not delete the target object itself).
(4, ID) link to existing record with id = ID (adds a relationship).
(5) unlink all (like using (3, ID) for all linked records).
(6, 0, [IDs]) replace the list of linked IDs (like using (5) then (4, ID) for each ID in the list of IDs).
Searching Records
For business logic methods to work, records must be searched based on certain criteria. In Odoo, we can accomplish this by using the search() method.
Let's look at an example: in this case, we're searching for partners whose 'company type' is 'company.'
domain = [('company_type','=','company')]
partner = self.env['res.partner'].search(domain)
>>>res.partner(1,2,3,6,9,10,13,15,16,17)
The domain will be returned by the search method. A domain is a collection of criteria, with each criterion consisting of a triple (either a list or a tuple) of (field_name, operator, value).
● field_name (str)
A current model's field name, or a relationship traversal through a Many2one using dot-notation,
e.g. 'street' or 'partner_id.country'
● operator (str)
a comparison operator that compares the field_name to the value, Valid operators are:
= equals to
!=not equals to
>greater than
>=greater than or equal to
< less than
<= less than or equal to
=? equals to or unset (if the value is either None or False, returns true; otherwise, it acts like =)
=like - field_name is matched to the value pattern. In the pattern, an _underscore represents (matches) any single character; the percent sign %, which matches any string of 0 or more characters.
like - matches the field_name against %value% pattern. Similar to =like, but before matching, wraps the value in '%.'
not like - it doesn’t match against the %value% pattern.
ilike - case insensitive like.
not ilike - case insensitive not like.
=ilike - case insensitive =like.
not in-is unequal to all of the items from value.
child_of-Is a child (descendant) of a value record (value can be either one item or a list of items).
Takes the semantics of the model into account (i.e., following the relationship field named by _parent_name).
parent_of - Is a parent (ascendant) of a value record (value can be either one item or a list of items).
Takes the semantics of the model into account (i.e., following the relationship field named by _parent_name).
value
With the operator, the variable type must be comparable to the named field.
Domain criteria can be combined using prefix logical operators:
'&' - logical AND
'|'- logical OR
'!'- logical NOT
Note:
Mostly to negate combinations of criteria. Individual criterion generally has a negative form (e.g., = -> !=, < -> >=), which is simpler than negating the positive.
Example:
To search for partners named ABC, from Belgium or Germany, whose language is not English:
[('name','=','ABC'),
('language.code','!=','en_US'),
'|',('country_id.code','=','be'),
('country_id.code','=','de')
This domain is interpreted as
(name is 'ABC')
AND (language is NOT english)
AND (country is Belgium OR Germany)
In the search method, the other keyword arguments that match.
•offset=N: This is used to skip the first N records that match the query. Its default is 0.
• limit=N: This indicates that N records will be returned. By default, there is no limit.
• order=sort_specification: By default, the order is the _order attribute of the model class.
• count=boolean: If True, it will return the number of records in the recordset. The default is False
Note:
The search() method with the count attribute and search_count() function both give the same result.
Filtering Records
When we already have a recordset and only need a subset of it to satisfy certain requirements, we will occasionally use the filtered() method to remove the relevant entries from the recordset.
The filtered() function accepts parameters in the form of functions or dot-separated lists of field names. It will return the records that satisfy the condition, which may be empty.
# only keep records whose company is the current user's
records.filtered(lambda r: r.company_id == user.company_id)
# only keep records whose partner is a company
records.filtered("partner_id.is_company")
Sorting Records
A recordset can be sorted using the sorted() method.
- The key is returned along with a self-ordered Recordset.
- The order attribute can be used in the function to sort the returned recordset if the recordset was retrieved using the search() method.
partner = self.env['res.partner'].search([(),order='id'])
All of the partners will be returned, sorted by record 'id' in ascending order.
- The _order attribute of the model can also be used to sort the model's records.
- Use the sorted() method to sort a recordset.
partners = self.env['res.partner'].search([('company_type','=','company')])
partners.sorted(key='id')
Internally, the sorted() method retrieves the data from the field specified as the key argument. It then returns a sorted recordset by using Python's native sorted method.
Reverse=True, another optional input, returns a recordset in reverse order.
partners = self.env['res.partner'].search([('company_type','=','company')])
partners.sorted(key='id', reverse=True)