Migrate Module: How to construct a map on a UNION query I am trying to create a hierarchical taxonomy from two different tables in the source database, one containing the parent entries, and the other containing the children.  Here is my query:

    $query1 = $this-&gt;getConnection()
      -&gt;select('envirolib_area', 'a')
      -&gt;fields('a', array('areaname', 'areaname_japanese', 'sort_order', 'acronym', 'areaid'));
    $query1-&gt;addExpression(':empty','parentid',array(':empty'=&gt;0));
    $query2 = $this-&gt;getConnection()
      -&gt;select('envirolib_task', 't')
      -&gt;fields('t', array('taskname','taskname_japanese','sort_order','acronym','taskid'));
      $query2-&gt;addField('t','areaid');
    $query1-&gt;union($query2, 'UNION ALL');

    $queryx = $this-&gt;getConnection()
      -&gt;select($query1,'x')-&gt;fields('x', array('areaname','areaname_japanese', 'sort_order', 'acronym', 'areaid', 'parentid'));


The envirolib_area table has a primary key of areaid, and the envirolib_task table has a primary key of taskid.  The envirolib_area table doesn't have a taskid column, and in my union I am actually calling a.areaid and t.taskid areaid, with t.areaid called parentid and 0 inserted in that column for rows from the envirolib_area table.

the addition of $queryx was necessary to stave of errors about unven numbers of queries in a union, which seems to be caused by the mapping method joining onto the query (thought the explanation I read, claimed that shouldn't happen if 'map_joinable' =&gt; FALSE as it is in my case.

So here is my attempt at MigrateSQLMap:

$this-&gt;map = new MigrateSQLMap($this-&gt;machineName,
  array(
    'areaid' =&gt; array('type' =&gt; 'int',
                       'length' =&gt; 3,
                       'not null' =&gt; TRUE,
                       'description' =&gt; 'Area ID',
                       'alias' =&gt; 'a'
                      ),
    'taskid' =&gt; array('type' =&gt; 'int',
                        'length' =&gt; 3,
                        'not null' =&gt; TRUE,
                        'description' =&gt; 'Parent ID',
                        'alias' =&gt; 't')
    ),
  MigrateDestinationNode::getKeySchema()
);


When I try to run the migration I get a wall of errors like this:

Undefined property: stdClass::$taskid File /Users/bob/ultrabob.prv/www/sites/all/modules/migrate/includes/migration.inc, line 943(file: /Users/bob/ultrabob.prv/www/sites/all/modules/migrate/includes/migration.inc, line 943)
Could not save to map table due to NULL value for key field taskid


How should I set up the MigrateSQLMap in a case like this?  Would it make more sense to migrate this taxonomy in two migrations, one for each table?
Either do 2 migrations. Or create a derivative table of your term data to easily set the $query for the migration before running the migration task.

You can do this by writing a custom php script to be called by drush php-script or by using the preImport migrate hook to build/rebuild the derivative table.