Perfoming joins in selectΒΆ

New in version 7.0.

Starting with OpenERP 7.0, an auto_join attribute is added on many2one and one2many fields. The purpose is to allow the automatic generation of joins in select queries. This attribute is set to False by default, therefore not changing the default behavior. Please note that we consider this feature as still experimental and should be used only if you understand its limitations and targets.

Without _auto_join, the behavior of expression.parse() is the same as before. Leafs holding a path beginning with many2one or one2many fields perform a search on the relational table. The result is then used to replace the leaf content. For example, if you have on res.partner a domain like [('bank_ids.name', 'like', 'foo')] with bank_ids linking to res.partner.bank, 3 queries will be performed :

  • 1 on res_partner_bank, with domain [('name', '=', 'foo')], that returns a list of res.partner.bank ids (bids)
  • 1 on res_partner, with a domain ['bank_ids', 'in', bids)], that returns a list of res.partner ids (pids)
  • 1 on res_partner, with a domain [('id', 'in', pids)]

When the auto_join attribute is True on a relational field, the destination table will be joined to produce only one query.

  • the relational table is accessed using an alias: '"res_partner_bank" as res_partner__bank_ids. The alias is generated using the relational field name. This allows to have multiple joins with different join conditions on the same table, depending on the domain.
  • there is a join condition between the destination table and the main table: res_partner__bank_ids."partner_id"=res_partner."id"
  • the condition is then written on the relational table: res_partner__bank_ids."name" = 'foo'

This manipulation is performed in expression.parse(). It checks leafs that contain a path, i.e. any domain containing a ‘.’. It then checks whether the first item of the path is a many2one or one2many field with the auto_join attribute set. If set, it adds a join query and recursively analyzes the remaining of the leaf, using the same behavior. If the remaining path also holds a path with auto_join fields, it will add all tables and add every necessary join conditions.

Chaining joins allows to reduce the number of queries performed, and to avoid having too long equivalent leaf replacement in domains. Indeed, the internal queries produced by this behavior can be very costly, because they were generally select queries without limit that could lead to huge (‘id’, ‘in’, [...]) leafs to analyze and execute.

Some limitations exist on this feature that limits its current use as of version 7.0. This feature is therefore considered as experimental, and used to speedup some precise bottlenecks in OpenERP.

List of known issues and limitations:

  • using auto_join bypasses the business logic; no name search is performed, only direct matches between ids using join conditions
  • ir.rules are not taken into account when analyzing and adding the join conditions

List of already-supported corner cases :

  • one2many fields having a domain attribute. Static domains as well as dynamic domain are supported
  • auto_join leading to functional searchable fields

Typical use in OpenERP 7.0:

  • in mail module: notification_ids field on mail_message, allowing to speedup the display of the various mailboxes
  • in mail module: message_ids field on mail_thread, allowing to speedup the display of needaction counters and documents having unread messages