In our last blog, we covered how to migrate data from a MySQL database into 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:
- Restructuring one-to-many relationships using SQL so that all related data appears in a single row
- 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.
2. Ingredients Table: Lists ingredients associated with each recipe.
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:
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:
<?php
namespace Drupal\test_migrate\Plugin\migrate\process;
use Drupal\migrate\MigrateExecutableInterface;
use Drupal\migrate\ProcessPluginBase;
use Drupal\migrate\Row;
/**
* Recipe Ingredient mapping.
*
* @MigrateProcessPlugin(
* id = "recipe_related_table_fields_aggregator",
* handle_multiples = TRUE
* )
*
* @package Drupal\test_migrate\Plugin\migrate\process
*/
class RecipeRelatedTableFieldsAggregator extends ProcessPluginBase {
/**
* {@inheritdoc}
*/
public function transform($value, MigrateExecutableInterface $migrate_executable, Row $row, $destination_property) {
$recipe_related_fields = [];
foreach ($value as $field_name) {
$field_values = explode("|", $row->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.