How to fix migrate query() that's breaking my migration?

Question

So I have a Drupal 2 Drupal (d2d) migration configured as follows:

class RanchoAboutNodeMigration extends RanchoNodeMigration {
  public function __construct(array $arguments) {
    parent::__construct($arguments);

    ...

    // This is an entity reference field
    $this->addFieldMapping('field_attached_images', 'iid')
         ->separator(',')
         ->sourceMigration('Image');
    ...
  }

  protected function query() {
    // Get the default query 
    $query = parent::query();
    $query->leftJoin('image_attach','ia','n.nid = ia.nid');
    $query->addExpression('GROUP_CONCAT(DISTINCT ia.iid)', 'iid');
    $query->addField('ia', 'iid', 'iid');
    $query->addField('ia', 'nid', 'inid');

    return $query;
  }      
}

The query causes two problems:

1. I can only import items one at a time through drush mi or the UI
2. Most important: I'll eventually run into a PDOexception for duplicate rows and then the error "Could not save to map table due to NULL value for key field nid" with migrate attempting to move totally empty rows.

Both items can be addressed by commenting out the line with $query->addExpression(), but then I miss some desired images. When the migration is actually working, everything migrates correctly, including the referenced image(s).

Any thoughts on what's causing this bizarre behavior when things go wrong and how to address it?

Only thing I can think is to create a local query in prepareRow(), but I don't think I've seen anyone else do that before.

Answer 1

Adding a query to prepareRow() is the way to go:

  public function prepareRow($row) {
    if (parent::prepareRow($row) === FALSE) {
      return FALSE;
    }
    $query = Database::getConnection('default', 'd6source')
           ->select('image_attach', 'ia')
           ->fields('ia')
           ->condition('nid',$row->nid);
    $query->addExpression('GROUP_CONCAT(DISTINCT ia.iid)', 'iid');
    $query->groupBy('ia.nid');
    $result = $query->execute();
    foreach ($result as $r) {
      $iid[] = $r->iid;
    }
    $row->iid = implode (",", $iid); 
  }

This has the huge added benefit of not messing up the source row count.