Drupal・7 min read・Posted on March 11, 2025 # Migrating relational data into Drupal paragraphs ![Migrating relational data into Drupal paragraphs](/sites/default/files/remediation-media/67cfff99f5998fc4a69de34e_Migrating_relational_data_into_Drupal_paragraphs02.avif) Table of contents - [Part 1: Combining related data with MySQL](#part-1-combining-related-data-with-mysql) - [Understanding the data structure:](#understanding-the-data-structure) - [Creating the migration query](#creating-the-migration-query) - [Part 2: Generating paragraph entities using entity\_generate](#part-2-generating-paragraph-entities-using-entity-generate) - [Structuring data into Drupal paragraph entities](#structuring-data-into-drupal-paragraph-entities) - [Why are paragraphs tricky](#why-are-paragraphs-tricky) - [Solution: Generate paragraphs during parent migration](#solution-generate-paragraphs-during-parent-migration) - [Breaking down the migration: how everything comes together](#breaking-down-the-migration-how-everything-comes-together) - [Bringing it all together](#bringing-it-all-together) - [Conclusion](#conclusion) In our last blog, we covered [how to migrate data from a MySQL database into Drupal](/insights/migration-from-a-mysql-source-database-in-drupal). That approach went without a hitch when transferring data from one source table into Drupal's content types. We're now considering another scenario—**when connected data is stored in several tables.** Consider a recipe as an example, one table may be used to store the title and description, while another stores multiple rows of ingredients for the recipe, each row with information such as ingredient name and quantity. Such relational design is good for storage but makes it difficult for managing content when pieces that are related need to be displayed together. In Drupal, we try to condense this dispersed, composite data into one using paragraphs. Paragraphs provide a malleable solution, enabling us to package complicated data, such as an ingredient list with its additional attributes into a single unit. These paragraphs are subsequently linked to the parent recipe content type, collating all the ingredients with their information into one convenient piece per recipe. To do this, we take two key steps: 1. Restructuring one-to-many relationships using SQL so that all related data appears in a single row 2. Using Drupal’s Migrate API and the entity\_generate plugin to create paragraph entities This blog builds on the previous migration process, showing how to organize and structure data for a smooth transition into Drupal. ## Part 1: Combining related data with MySQL During migration of content into Drupal, data has to be restructured. Take recipes, for instance. In MySQL, the details of a recipe may be stored in one table and the ingredients in another, connected by Recipe ID. This is fine in a relational database but must be modified for Drupal, where each recipe would have its ingredients as paragraph items. If migrated straight, each ingredient is still stored separately but the ingredient usage information is unique to a recipe. ‍ ### Understanding the data structure: Assume two tables**:** 1\. Recipe Table: Contains basic information about each recipe. ‍ Recipe IDNameDescriptionStatusCreated1Chocolate CakeThis is a yummy chocolate cakePublished2024-07-18 ‍ 2\. Ingredients Table: Lists ingredients associated with each recipe. ‍ Ingredient IDRecipe IDIngredient NameQuantity11Flour2 cups21Sugar1 cup31Cocoa Powder1/2 cup41Baking Powder1 tsp ‍ The goal is to migrate this data into a Drupal content type called "Recipe," where each recipe's ingredients will be stored as individual paragraph items. ‍ ### Creating the migration query Now that we understand the data structure, the next step is writing a migration query that consolidates recipe details and their related ingredients into a format Drupal can work with. Since the ingredients are stored separately in a one-to-many relationship, a direct migration won’t group them under the same recipe. To fix this, we need to write a MySQL query that pulls all ingredients for each recipe and presents them as structured data. To do this, we’ll create a **source database plugin** for the Recipe content type in Drupal. This plugin will ensure that: - Each recipe remains a single content entity - Ingredients are grouped correctly as paragraph items within the recipe Let’s dive into the query structure and see how we can transform the data for Drupal’s migration process. #### Step 1: Querying the recipe table We start by selecting fields from the **recipe** table ‍ ``` /** * {@inheritdoc} */ public function query() { // Source data is queried from 'recipe' table. $query = $this->select('recipe', 'r') ->fields('r', [ 'RecipeId', 'Name', 'Description', 'Status', 'Created', ]); return $query; } ``` ‍ #### Step 2: Joining the ingredients table Next, we perform a left join with the **ingredients** table to associate ingredients with their respective recipes: ‍ ``` /** * {@inheritdoc} */ public function query() { // Source data is queried from 'recipe' table. $query = $this->select('recipe', 'r') ->fields('r', [ 'RecipeId', 'Name', 'Description', 'Status', 'Created', ]); // Left join ingredients table. $query->leftJoin('ingredients', 'i', 'i.RecipeId = r.RecipeId'); return $query; } ``` ‍ #### Step 3: Using GROUP\_CONCAT to consolidate data Since multiple rows for ingredients exist for each recipe, we can use MySQL's **GROUP\_CONCAT** function to concatenate ingredient names and quantities into single strings ‍ ``` /** * {@inheritdoc} */ public function query() { // Source data is queried from 'recipe' table. $query = $this->select('recipe', 'r') ->fields('r', [ 'RecipeId', 'Name', 'Description', 'Status', 'Created', ]); // Left join ingredients table. $query->leftJoin('ingredients', 'i', 'i.RecipeId = r.RecipeId'); $query->addExpression("GROUP_CONCAT(i.IngredientName SEPARATOR '|')", 'IngredientName'); $query->addExpression("GROUP_CONCAT(i.Quantity SEPARATOR '|')", 'Quantity'); return $query; } ``` ‍ #### Expected output The output of this query will yield a single row for each recipe along with its associated ingredients: ‍ Recipe IDNameDescriptionStatusCreatedIngredient NameQuantity1Chocolate CakeThis is a yummy chocolate cake.Published2024-07-18Flour | Sugar | Cocoa Powder | Baking Powder2 cups | 1 cup | 1/2 cup | 1 tsp ‍ ## Part 2: Generating paragraph entities using entity\_generate ### Structuring data into Drupal paragraph entities Now that we’ve used SQL’s *GROUP\_CONCAT* to combine recipe and ingredient data into a single row, the next challenge is migrating this structured data into Drupal. Unlike taxonomy terms or basic entity references, paragraphs in Drupal require both an entity ID (*target\_id*) and a revision ID (*target\_revision\_id*). This adds a layer of complexity to the migration process. ### Why are paragraphs tricky Drupal paragraphs are **revisionable identities**, so they record changes over time. In contrast to regular entity references, paragraph migrations involve handling both: **target\_id**: The paragraph entity's unique ID **target\_revision\_id**: The ID of the particular revision being referenced Having both requirements doubles the complexity. The traditional approach tends to need a two-step process—first creating paragraph entities and then referencing them to their parent content. Without both IDs being handled properly, references will be broken, and we end up with missing or uneditable content.To ensure that this migration is smooth, we require a systematic approach that builds paragraph entities with proper references. Let's dissect how we can do it using Drupal's Migrate API. ### Solution: Generate paragraphs during parent migration To streamline the migration process, we can use the **entity\_generate** plugin from the Migrate Plus module. This allows us to create paragraph entities dynamically while migrating the parent Recipe node—eliminating the need for a separate preprocessing step. With this approach, we can: - Generate paragraph entities during the migration process - Automatically link them to the correct recipe using *target\_id* and *target\_revision\_id* - Ensure the structured data remains intact in Drupal This method simplifies the migration workflow, reducing manual steps and ensuring a clean, structured import. Let’s walk through how to implement it in the migration configuration. #### Step 1: defining fields in prepareRow ‍ In the migration’s ***prepareRow*** method, set the concatenated fields as an array to ensure correct formatting for processing into paragraph entities. ‍ ``` /** * {@inheritdoc} */ public function prepareRow(Row $row) { // Prepare Recipe ingredients fields. $recipe_ingredients_fields = [ 'IngredientName', 'Quantity', ]; $row->setSourceProperty('RecipeIngredientsFields', $recipe_ingredients_fields); return parent::prepareRow($row); } ``` ‍ ‍ #### Step 2: Structure data for paragraphs A custom process plugin transforms these arrays into a format compatible with paragraphs: ‍ ``` getSourceProperty($field_name)); foreach ($field_values as $key => $field_value) { $recipe_related_fields[$key][$field_name] = $field_value; } } return $recipe_related_fields; } } ``` ‍ The custom process plugin converts concatenated fields like: ‍ ``` 'Flour|Sugar|Cocoa Powder|Baking Powder', '2 cups|1 cup|1/2 cup|1 tsp' ``` Into an array format as follows: ‍ ``` [ 0 => [ 'IngredientName' => 'Flour', 'Quantity' => '2 cups', ], 1 => [ 'IngredientName' => 'Sugar', 'Quantity' => '1 cup', ], 2 => [ 'IngredientName' => Cocoa Powder, 'Quantity' => '1/2 cup', ], 3 => [ 'IngredientName' => Baking Powder, 'Quantity' => '1 tsp', ], ] ``` ‍ ‍ #### Step 3: Generate paragraphs with entity\_generate In the migration YAML, use ***sub\_process*** and ***entity\_generate*** to create paragraphs and reference them in the Recipe node: ‍ ``` process: _recipe_ingredients: plugin: recipe_related_table_fields_aggregator source: RecipeIngredientsFields field_ingredients: - plugin: sub_process source: '@_recipe_ingredients' process: _ingredient_name: IngredientName _quantity: Quantity target_id: plugin: entity_generate source: IngredientName entity_type: paragraph bundle: recipe_ingredient value_key: field_ingredient_name bundle_key: type values: field_ingredient_name/0/value: '@_ingredient_name' field_quantity/0/value: '@_quantity' _revision_id_value: plugin: entity_value source: '@target_id' entity_type: paragraph field_name: revision_id target_revision_id: '@_revision_id_value/0/value' ``` ‍ ## Breaking down the migration: how everything comes together **Turning a concatenated string into structured data: recipe\_related\_table\_fields\_aggregator** A recipe’s ingredients might be combined into a single string using GROUP\_CONCAT: *"Flour|Sugar|Cocoa Powder", "2 cups|1 cup|1/2 cup"* Since Drupal paragraphs need each ingredient as a separate entry, this **custom process plugin** restructures the data into an array, making it ready for migration: \[ \['name' => 'Flour', 'quantity' => '2 cups'\], \['name' => 'Sugar', 'quantity' => '1 cup'\], \['name' => 'Cocoa Powder', 'quantity' => '1/2 cup'\] \] ‍ **Processing each ingredient separately: sub\_process** Drupal 9+ replaced the iterator plugin with **sub\_process**, which **iterates over the structured ingredient list** and processes each one individually. Instead of treating all ingredients as a single text field, this step ensures each ingredient is handled as a separate paragraph entity. ‍ **Generating paragraphs on the fly: entity\_generate** Rather than migrating paragraphs separately, the entity\_generate plugin from Migrate Plus dynamically creates paragraph entities as part of the parent migration. It: - Assigns each ingredient to a **paragraph bundle** (e.g., ingredient) - Maps the **ingredient name and quantity** to their respective fields - Returns the **target\_id** (paragraph entity ID) for linking **Linking paragraphs to recipes: target\_id and target\_revision\_id** Once the paragraphs are created, they need to be connected to the correct Recipe node. - **target\_id**: The unique ID of the paragraph entity, automatically assigned when saved. - **target\_revision\_id**: Since paragraphs track changes over time, this ID ensures the correct version of each paragraph is referenced. It’s handled using the entity\_value plugin, which fetches the latest revision. ### Bringing it all together By combining these components, the migration process turns relational data into structured Drupal content, while each ingredient is linked to its recipe as a separate paragraph, keeping everything organized and easy to manage. ## Conclusion Moving relational data into Drupal paragraphs becomes complicated when working with revisionable entities requiring both target\_id and target\_revision\_id. If done incorrectly, the references are broken, and content structures are inconsistent. A more efficient method? Reshape the data up front. Employing SQL methods such as GROUP\_CONCAT, we bring together related data in one row for easier manipulation. Afterwards, Drupal's Migrate API and the entity\_generate plugin continue the job, dynamically generating paragraph entities and referencing them back to their parent recipes—all in one organized workflow. This method is seamless, effective, and simple to manage. Rather than migrating paragraphs separately, it is all done in one pass. In my experience, this minimizes errors and keeps content organized, editable, and ready for future modifications. With the proper approach, even intricate relational data slots easily into Drupal's paragraph system, making it a solid option for dynamic content management. Written by Riyas NR Senior Engineer - Backend Editor Ananya Rakhecha, Tech Advocate ## We'd love to talk about your business objectives [ Contact Us ](/contact)