Migrate nodereferences with dissimilar keys

I ran into a problem using the Migrate module where I migrated a series of newsletter issues and then imported a series of articles that were related to each newsletter.

Typically, for nodereferences, you can add a dependency of the first migration. If both the newsletter issue table used a primary key and the article table used a foreign key of "issueid" then the mapping is very straight forward.

class IssueMigration extends Migration {
public function __construct() {
parent::__construct();
$query = db_select('issues', 'i');
$query->fields('i', array('issueid', 'dateposted'));
$this->source = new MigrateSourceSQL($query);
}
}

class ArticleMigration extends Migration {
public function __construct() {
parent::__construct();
$this->depedencies = array('Issue');
$query = db_select('articles', 'a');
$query->fields('a', array('articleid', 'issueid'));
$this->source = new MigrateSourceSQL($query);
$this->addFieldMapping('field_issue_ref','issueid')->sourceMigration('Issue');
}
}
?>

Migrate will know to use the actual nid of the imported issue for the node reference by looking at the migrate_map_issues table.

The problem was that in the source data for the issues the primary key was actually "archiveid" and the foreign key column in the articles table was "issueid".

$query->fields('i', array('archiveid', 'dateposted'));
?>

and the articles uses a foreign key column named "issueid",

$query->fields('a', array('articleid','issueid'));
?>

the node reference mapping from migrate doesn't know which to use for the mapping:

$this->addFieldMapping('field_issue_ref', 'issueid')->sourceMigration('NewsletterIssue');
?>

the solution is to make sure that the virtual names of the columns do match. The fields() does not allow for modifying column aliases, but using the individual addField() does.

$query->fields('i', array('dateposted'));
$query->addField('i', 'archiveid', 'issueid');
?>

This is the equivalent of SELECT i.dateposted, i.archiveid AS issueid FROM issues and migrate will match up the keys and get the right nid to use as a reference.