Working with recordsets

We will now explore how the ORM works and learn about the most common operations performed with it. We will use the prompt provided by the shell command to interactively explore how recordsets work.

With self, we can only access the method's recordset. But the self.env environment reference allows us to access any other model. For example, self.env['res.partner'] returns a reference to the Partners model (which is actually an empty recordset). We can then use search() or browse() on it to generate recordsets.

The search() method takes a domain expression and returns a recordset with the records matching those conditions. An empty domain [] will return all records. For more details on domain expressions please refer back to Chapter 6,  Views - Designing the User Interface. If the model has the active special field, by default only the records with active=True will be considered.

A few optional keyword arguments are available, as shown here:

  • order is a string to be used as the ORDER BY clause in the database query. This is usually a comma-separated list of field names.
  • limit sets a maximum number of records to retrieve.
  • offset ignores the first n results; it can be used with limit to query blocks of records at a time.

Sometimes we just need to know the number of records meeting certain conditions. For that we can use search_count(), which returns the record count instead of a recordset. It saves the cost of retrieving a list of records just to count them, so it is much more efficient when we don't have a recordset yet and just want to count the number of records.

The browse() method takes a list of IDs or a single ID and returns a recordset with those records. This can be convenient for the cases where we already know the IDs of the records we want.

Some usage examples of this are shown here:

>>> self.env['res.partner'].search([('name', 'like', 'Ag')])
res.partner(7, 51)
>>> self.env['res.partner'].browse([7, 51])

res.partner(7, 51)

The special case of a recordset with only one record is called a singleton recordset. Singletons are still a recordset, and can be used wherever a recordset is expected. 

But unlike multi-element recordsets, singletons can access their fields using the dot notation, as shown here:

>>> print self.name 
Administrator

In the next example, we can see the same self singleton recordset also behaves as a recordset, and we can iterate it. It has only one record, so only one name is printed out:

>>> for rec in self: 
      print rec.name 
Administrator

Trying to access field values on recordsets with more than one record will error, so this can be an issue in the cases we are not sure if we are working with a singleton recordset. On methods designed to work only with singleton, we can check this using self.ensure_one() at the beginning. It will raise an error if self is not singleton.

Tip

Note that an empty record is also a singleton.

Recordsets implement the active record pattern. This means that we can assign values on them, and these changes will be made persistent in the database. This is an intuitive and convenient way to manipulate data, as shown here:

>>> admin = self.env['res.users'].browse(1)
>>> print admin.name
Administrator
>>> admin.name = 'Superuser'
>>> print admin.name
Superuser

Recordsets also have three methods to act on their data: create(), write(), and unlink().

The create() method takes a dictionary to map fields to values and returns the created record. Default values are automatically applied as expected, which is shown here:

>>> Partner = self.env['res.partner']
>>> new = Partner.create({'name': 'ACME', 'is_company': True})
>>> print new
res.partner(72,)

The unlink() method deletes the records in the recordset, as shown here:

>>> rec = Partner.search([('name', '=', 'ACME')])
>>> rec.unlink()
True

The write() method takes a dictionary to map fields to values. These are updated on all elements of the recordset and nothing is returned, as shown here:

>>> Partner.write({'comment': 'Hello!'})

Using the active record pattern has some limitations; it updates only one field at a time. On the other hand, the write() method can update several fields of several records at the same time by using a single database instruction. These differences should be kept in mind for cases where performance can be an issue.

It is also worth mentioning copy() to duplicate an existing record; it takes that as an optional argument and a dictionary with the values to write on the new record. For example, to create a new user copying from the Demo User:

>>> demo = self.env.ref('base.user_demo')
>>> new = demo.copy({'name': 'Daniel', 'login': 'dr', 'email':''})

Note

Remember that fields with the copy=False attribute won't be copied.

For historical reasons, ORM recordsets handle date and datetime values using their strings representations, instead of actual Python Date and Datetime objects. In the database they are stored in date fields, but datetimes are stored in UTC time.

They map to %Y-%m-%d and %Y-%m-%d %H:%M:%S respectively.

To help handle dates, fields.Date and fields.Datetime provide few functions. For example:

>>> from odoo import fields
>>> fields.Datetime.now()
'2014-12-08 23:36:09'
>>> fields.Datetime.from_string('2014-12-08 23:36:09')
datetime.datetime(2014, 12, 8, 23, 36, 9)

Dates and times are handled and stored by the server in a naive UTC format, which is not time zone aware and may be different from the time zone that the user is working on. Because of this we can make use of a few other functions to help us dealing with this:

To facilitate conversion between formats, both fields.Date and fields.Datetime objects provide these functions:

Recordsets support additional operations on them. We can check whether a record is included or not in a recordset. If x is a singleton recordset and my_recordset is a recordset containing many records, we can use:

The following operations are also available:

Here are some usage examples for these functions:

>>> rs0 = self.env['res.partner'].search([])
>>> len(rs0)  # how many records?
40
>>> starts_A = lambda r: r.name.startswith('A')
>>> rs1 = rs0.filtered(starts_A)
>>> print rs1
res.partner(8, 7, 19, 30, 3)
>>> rs2 = rs1.filtered('is_company')
>>> print rs2
res.partner(8, 7)
>>> rs2.mapped('name')
[u'Agrolait', u'ASUSTeK']
>>> rs2.mapped(lambda r: (r.id, r.name))
[(8, u'Agrolait'), (7, u'ASUSTeK')]
>> rs2.sorted(key=lambda r: r.id, reverse=True)
res.partner(8, 7)

We will surely want to add, remove, or replace the elements in these related fields, and so this leads to the question: how can recordsets be manipulated?

Recordsets are immutable, meaning that their values can't be directly modified. Instead, modifying a recordset means composing a new recordset based on existing ones.

One way to do this is using the supported set operations:

The slice notation can also be used, as shown in these examples:

Note

In Odoo 10, recordset manipulation preserves order. This is unlike previous Odoo versions, where recordset manipulation was not guaranteed to preserve the order, although addition and slicing are known to keep record order.  

We can use these operations to change a recordset by removing or adding elements. Here are some examples:

The relational fields contain recordset values. Many-to-one fields can contain a singleton recordset, and to-many fields contain recordsets with any number of records. We set values on them using a regular assignment statement, or using the create() and write() methods with a dictionary of values. In this last case, a special syntax is used to modify to-many fields. It is the same used in XML records to provide values for relational fields, and is described in Chapter 4 , Module Data, in the section Setting values for relation fields.

As an example, the write() syntax equivalent to the three preceding assignment examples is:

  • self.write([(4, task1.id, None)]) adds the task1 record
  • self.write([(3, task1.id, None)]) removes task1 from the recordset
  • self.write([(3, self.task_ids[-1].id, False)]) removes the last record

As we saw earlier, models can have relational fields: many-to-one, one-to-many, and many-to-many. These field types have recordsets as values.

In the case of many-to-one, the value can be a singleton or an empty recordset. In both cases, we can directly access their field values. As an example, the following instructions are correct and safe:

>>> self.company_id
res.company(1,)
>>> self.company_id.name
u'YourCompany'
>>> self.company_id.currency_id
res.currency(1,)

>>> self.company_id.currency_id.name
u'EUR'

Conveniently, an empty recordset also behaves like singleton, and accessing its fields does not return an error but just returns False. Because of this, we can traverse records using dot notation without worrying about errors from empty values, as shown here:

>>> self.company_id.country_id
res.country()
>>> self.company_id.country_id.name
False

While using the active record pattern, relational fields can be assigned recordsets.

For many-to-one fields, the value assigned must be a single record (a singleton recordset).

For to-many fields, their value can also be assigned with a recordset, replacing the list of linked records, if any, with a new one. Here a recordset with any size is allowed.

While using the create() or write() methods, where values are assigned using dictionaries, relational fields can't be assigned to recordset values. The corresponding ID or list of IDs should be used.

For example, instead of self.write({'user_id': self.env.user}), we should rather use self.write({'user_id': self.env.user.id}).