How to Speed Up Odoo Data Import Having Many2Many using PostgreSQL

Importing external data into Odoo often presents challenges, especially with Many-to-Many (M2M) relationships. These complexities can hinder efficiency and create bottlenecks in the data integration process.

In this blog, we will walk through a solution developed to tackle this issue—a PostgreSQL-triggered function that dynamically handles M2M mappings. This approach not only resolves M2M errors but also speeds up the process by 6x, making it a game-changer for Odoo developers and administrators.

The solution leverages a PostgreSQL trigger function that processes M2M columns during data import. The function is triggered after an insert operation, dynamically handling M2M column mappings by:

Detecting M2M columns in the imported data.
Splitting comma-separated values in these columns into arrays.
Mapping each value to its corresponding ID in the target table.
Inserting the IDs into the relational mapping table.
Cleaning up temporary columns after processing.

This approach ensures that M2M relationships are correctly established without requiring extensive manual intervention.

Explanation of the process_m2m_mapping Function

This function is a PostgreSQL trigger function that processes many-to-many (M2M) relationships based on dynamically configured mappings. When a row in the triggering table is inserted or updated, the function maps the values in m2m__% columns to related tables and inserts the mappings into corresponding mapping tables.

CREATE OR REPLACE FUNCTION process_m2m_mapping()
RETURNS TRIGGER AS $$

The function dynamically processes M2M columns during INSERT or UPDATE operations, ensuring seamless mappings.
A TRIGGER that allows for manipulation of the NEW row before it is inserted or updated.

DECLARE
      col record;
      value_array text[];
      single_value text;
      id1 integer;
      id2 integer;
      dynamic_sql text;
      mapping_config jsonb;
      column_type text;
      field_config jsonb;

col: Holds metadata for each m2m__% column in the table during iteration.
value_array: Array of comma-separated values extracted from an m2m__% column.
single_value: Individual value from the value_array.
id1: ID of the current row in the triggering table.
id2: ID of a related row in the data_table.
dynamic_sql: Holds column values dynamically extracted using SQL.
mapping_config: JSONB configuration containing mapping information for m2m__% columns.
column_type: Data type (text) of the name column in the related table.
field_config: JSONB configuration for a specific m2m__% column being processed.

mapping_config := TG_ARGV[0]::jsonb;

TG_ARGV[0]: Trigger argument containing the JSONB configuration for mapping m2m__% columns.
The mapping_config is parsed as JSONB. This configuration defines how each m2m__% column should be processed.

FOR col IN (
     SELECT column_name
      FROM information_schema.columns
      WHERE table_name = TG_TABLE_NAME::text
      AND column_name LIKE 'm2m__%'
) LOOP

Iterates through all columns in the triggering table whose names start with the prefix m2m__%.
Uses PostgreSQL system catalog information_schema.columns to identify relevant columns.

field_config := mapping_config->col.column_name;

Extracts the configuration for the current m2m__% column from the mapping_config.
Skips the column if no configuration exists.

EXECUTE format('SELECT $1.%I', col.column_name) USING NEW INTO dynamic_sql;

Dynamically retrieves the value of the current m2m__% column from the NEW row.
Stores the column value in dynamic_sql.

IF dynamic_sql IS NOT NULL THEN

Processes the column only if it contains a non-NULL value.

id1 := NEW.id;

Retrieves the id of the row being processed (NEW.id).

EXECUTE format(
     'SELECT data_type
      FROM information_schema.columns
      WHERE table_name = %L
      AND column_name = ''name''',
     field_config->>'data_table'
) INTO column_type;

Dynamically determines whether the name column in the related table (data_table) is of type jsonb or text.

value_array := string_to_array(dynamic_sql, ',');

Splits the m2m__% column’s value into an array of individual elements based on commas.

FOREACH single_value IN ARRAY value_array LOOP

Loops through each value in the value_array.
For jsonb Columns

EXECUTE format(
     'SELECT id FROM %I WHERE (name->>''en_US'' = %L OR name->>''fr_FR'' = %L)',
     field_config->>'data_table',
    TRIM(single_value),
    TRIM(single_value)
) INTO id2;

Checks if the name column contains a value matching single_value for keys en_US or fr_FR.
For text Columns

EXECUTE format(
      'SELECT id FROM %I WHERE name = %L',
     field_config->>'data_table',
     TRIM(single_value)
) INTO id2;

Searches for a matching row based on a plain-text name column.

EXECUTE format(
     'INSERT INTO %I (%I, %I)
     VALUES (%L, %L)
     ON CONFLICT (%I, %I) DO NOTHING',
     field_config->>'mapping_table',
     field_config->>'column1',
     field_config->>'column2',
     id1, id2,
     field_config->>'column1',
     field_config->>'column2'
);

Inserts the relationship between id1 (current table) and id2 (related table) into the mapping table.
ON CONFLICT DO NOTHING ensures duplicate relationships are not inserted.

RETURN NEW;

The function returns the modified NEW row so the INSERT or UPDATE operation can proceed.

The function processes any column in the triggering table with the prefix m2m__%, which indicates an M2M relationship. For each of these columns, the function retrieves its value, splits it into individual elements, and maps those elements to rows in a related table. Once the mapping is established, the function inserts the relationships into a designated mapping table. This dynamic approach eliminates the need for hardcoding specific mappings for each column, making the function reusable across various use cases.

The function relies on a JSONB configuration passed as a trigger argument (TG_ARGV[0]). This configuration specifies the mapping details for each m2m__% column, including the related table, mapping table, and column relationships. During execution, variables like col (column metadata), value_array (split values), id1 (current row ID), and id2 (related row ID) are initialized to dynamically handle the mappings. The function iterates through all columns in the triggering table that match the m2m__% prefix. For each column, it retrieves its configuration from the JSONB mapping and checks if the column contains a value. If a value exists, it is split into individual elements using the string_to_array function, allowing the function to process each entry separately.

For each value, the function dynamically queries the related table to find the corresponding row ID (id2). If the related table’s name column is of type jsonb, the function checks multiple language keys (e.g., en_US, fr_FR) for a match. If the column is plain text, it performs a direct comparison. This flexibility ensures compatibility with multilingual and simple-text data structures.

This PostgreSQL-triggered function transforms how Many-to-Many relationships are handled in Odoo data imports. By automating and optimizing M2M mappings, this approach not only saves time but also simplifies complex integrations. Try implementing this in your Odoo environment to experience its transformative impact firsthand!

How to Speed Up Odoo Data Import Having Many2Many using PostgreSQL

Explanation of the process_m2m_mapping Function

Category

Related Post