Relations between Odoo Models are represented by relational fields. We can have three different types of relations: many-to-one, one-to-many, and many-to-many. Looking at the Library Books example, we see that each book can have one Publisher, so we can have a many-to-one relation between books and publishers.
Looking at it from the Publishers point of view, each Publisher can have many Books. So, the previous many-to-one relation implies a one-to-many reverse relation.
Finally, there are cases where we can have a many-to-many relation. In our example, each book can have several (many) Authors. And inversely, each Author can have written many books. Looking at it from either side, this is a many-to-many relation.
We will reuse the my_module addon module from Chapter 3, Creating Odoo Modules.
Odoo uses the Partner model, res.partner, to represent persons, organizations, and addresses. So, we should use it for authors and publishers. We will edit the models/library_book.py file to add these fields:
- Add to
Library Booksthe many-to-one field for the book's publisher:class LibraryBook(models.Model): # ... publisher_id = fields.Many2one( 'res.partner', string='Publisher', # optional: ondelete='set null', context={}, domain=[], ) - To add the one-to-many field for a publisher's books, we need to extend the partner model. For simplicity, we will add that to the same Python file:
class ResPartner(models.Model): _inherit = 'res.partner' book_ids = fields.One2many( 'library.book', 'publisher_id', string='Published Books')
- The many-to-many relation between books and authors was already created, but let's revisit it:
class LibraryBook(models.Model): # ... author_ids = fields.Many2many( 'res.partner', string='Authors') - The same relation, but from authors to books, should be added to the Partner model:
class ResPartner(models.Model): # ... book_ids = fields.Many2many( 'library.book', string='Authored Books', # relation='library_book_res_partner_rel' # optional )
Now, upgrade the addon module, and the new fields should be available in the Model. They won't be visible in views until they are added to them, but we can confirm their addition by inspecting the Model fields in Settings | Technical | Database Structure | Models.
Many-to-one fields add a column to the database table of the model storing the database ID of the related record. At the database level, a foreign key constraint will also be created for it, ensuring that the stored IDs are a valid reference to a record in the related table. No database index is created for these relation fields, but this should often be considered; it can be done by adding the attribute index=True.
We can see that there are four more attributes we can use for many-to-one fields:
The ondelete attribute determines what happens when the related record is deleted. For example, what happens to Books when their Publisher record is deleted? The default is 'set null', setting an empty value on the field. It can also be 'restrict', which prevents the related record from being deleted, or 'cascade', which causes the linked record to also be deleted.
The last two (context and domain) are also valid for the other relational fields. They are mostly meaningful on the client side and at the model level act just as default values to be used in the client-side views.
contextadds variables to the client context when clicking through the field to the related record's view. We can, for example, use it to set default values on that view.domainis a search filter used to limit the list of related records available for selection when choosing a value for our field.
Both context and domain are explained in more detail in Chapter 8, Backend Views.
One-to-many fields are the reverse of many-to-one relations, and although they are added to models just like other fields, they have no actual representation in the database. They are instead programmatic shortcuts and enable views to represent these lists of related records.
Many-to-many relations also don't add columns in the tables for the models. This type of relation is represented in the database using an intermediate relation table, with two columns to store the two related IDs. Adding a new relation between a Book and an Author creates a new record in the relation table with the ID for the Book and the ID for the Author.
Odoo automatically handles the creation of this relation table. The relation table name is, by default, built using the name of the two related models plus a _rel suffix. But, we can override it using the relation attribute. A case to keep in mind is when the two table names are large enough for the automatically generated database identifiers to exceed the PostgreSQL limit of 63 characters.
As a rule of thumb, if the names of the two related tables exceed 23 characters, you should use the relation attribute to set a shorter name. In the next section, we will go into more detail on this.
The Many2one fields support an additional auto_join attribute. It is a flag that allows the ORM to use SQL joins on this field. Because of this, it bypasses the usual ORM control such as user access control and record access rules. On a specific case, it can solve a performance issue, but it is advised to avoid using it.
We have seen the shortest way to define the relational fields. For completeness, these are the attributes specific to this type of field:
The One2many field attributes are as follows:
comodel_name: This is the target model identifier and is mandatory for all relational fields, but it can be defined position-wise without the keywordinverse_name: This applies only toOne2manyand is the field name in the target model for the inverseMany2onerelationlimit: This applies toOne2manyandMany2manyand sets an optional limit on the number of records to read that are used at the user interface level
The Many2many field attributes are as follows:
comodel_name: (as defined earlier)relation: This is the name to use for the table supporting the relation, overriding the automatically defined namecolumn1: This is the name for theMany2onefield in the relational table linking to this modelcolumn2: This is the name for theMany2onefield in the relational table linking to thecomodel
For
Many2many relations, in most cases the ORM will take perfect care of the default values for these attributes. It is even capable of detecting inverse Many2many relations, detecting the already existing relation table and appropriately inverting the
column1 and column2 values.
But there are two cases where we need to step in and provide our own values for these attributes. One is the case where we need more than one Many2many relation between the same two models. For this to be possible, we must provide ourselves with a relation table name that is different from the first relation. The other case is when the database names of the related tables are long enough for the automatically generated relation name to exceed the 63 character PostgreSQL limit for database object names.
The relation table's automatic name is <model1>_<model2>_rel. But this relation table also creates an index for its primary key with the following identifier:
<model1>_<model2>_rel_<model1>_id_<model2>_id_key
It also needs to meet the 63 characters limit. So, if the two table names combined exceed a total of 63 characters, you will probably have trouble meeting the limits and will need to manually set the relation attribute.