I’m working on migrating connected database tables from MySQL into Drupal and running into issues with maintaining relationships between entities. The main tables I’m dealing with are tracks and composers, plus a linking table that connects them together.
The individual records for tracks and composers are importing successfully, but the connections between them aren’t being established properly. Here’s the migration configuration I’m using:
id: tracks
label: Import Music Tracks
migration_group: music_migrate_group
migration_dependencies:
required:
- composers
source:
plugin: table
key: migrate_db
table_name: tracks
id_fields:
track_id:
type: integer
process:
title: track_name
field_release_year: release_year
field_composers:
plugin: sub_process
source: track_id
process:
target_id:
plugin: migration_lookup
migration: composers
source:
plugin: db_query
key: migrate_db
query: "SELECT composer_id FROM track_composer WHERE track_id = :track_id"
placeholders:
track_id: '@track_id'
destination:
plugin: entity:node
default_bundle: track
What could be causing the relationship mapping to fail during the migration process?
Had this exact issue migrating a content database last year. The migration_lookup plugin fails silently when it can’t find matching records, especially with complex queries. Check your migrate_map tables first - unmapped composer records might be causing null lookups. Also verify your composers migration actually finished and all records have proper destination IDs. In my case, some source records had data inconsistencies that broke the lookup. Try adding debug logging to see what values are getting passed to migration_lookup. Another thing - make sure composers migration runs completely before tracks starts, even with the dependency declared. Timing matters.
The problem’s with how you’re handling the database query in the sub_process plugin. The @track_id placeholder isn’t resolving properly there. Don’t nest db_query inside sub_process - create a separate source plugin that joins your tables directly instead. Try something like SELECT t.*, GROUP_CONCAT(tc.composer_id) as composer_ids FROM tracks t LEFT JOIN track_composer tc ON t.track_id = tc.track_id GROUP BY t.track_id then use the explode plugin to split composer_ids before running migration_lookup. I’ve had much better luck with this approach for many-to-many relationships in migrations.
your sub_process setup’s probably the problem. switch from migration_lookup to entity_lookup for the target_id mapping - it’s way better at handling drupal entity references. also double-check that your track_composer linking table data is actually accessible during the migration.