When your source query is a union, like

$query1 = db_select('node', 'n')->fields('n', array('nid', 'vid', 'title'));
$query2 = db_select('node', 'n')->fields('n', array('nid', 'vid', 'title'));
$query1->union($query2, "ALL");

$this->source = new MigrateSourceSQL($query1);

You may receive the error message when importing:

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns

if you have your migration source option map_joinable set to TRUE (i.e., Migrate will join the map table directly to your query). This is because the map table fields get added to the first query in the union, so the number of fields don't match. This is easily worked around by wrapping the union in a subquery:

$query1 = db_select('node', 'n')->fields('n', array('nid', 'vid', 'title'));
$query2 = db_select('node', 'n')->fields('n', array('nid', 'vid', 'title'));
$query1->union($query2, "ALL");

// wraps the 2 unions into 1 subselected table 'x'
$queryx = db_select($query1,'x')->fields('x', array('nid', 'vid', 'title')); 

$this->source = new MigrateSourceSQL($queryx);

See #1673226: MigrateSourceSQL breaks when SelectQueryInterface query contains union (and not wrapped in a subquery) for more context. Thanks to arscan for identifying the problem and the work-around.

Comments

joelstein’s picture

joelstein commented

You may also need to specify fields in order for the migration to work:

$query_x = db_select($query1, 'x')->fields('x');
pdcarto’s picture

pdcarto commented
  1. I could not get mapJoinable to work:
    // Has no effect!
$this->source->setMapJoinable(FALSE);
  2. Be sure to set your source schema table alias to the alias you are using for the wrapped subquery. For example, if you are giving your subquery the alias of "x", then your MigrateSQLMap construction would be something like this: 
        $this->map = new MigrateSQLMap($this->machineName, array(
      'An_ID' => array(
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'A description',
        'alias' => 'x', // <--------- same as the subquery alias
      )
      ....