Custom MigrateMap to sync by field or property, e.g. SKU

Question

Existing situation: MigrateSQLMap

Normally with migrate API, a migration will use MigrateSQLMap to identify imported rows with existing entities.

This means: A database table named like migrate_map_* will be created for each migration. The * part of the table name is based on the migration machine name. This table associates primary key of the source data (e.g. a primary key column of a csv file) to entity ids.

Whenever a row is to be imported, Migrate API will look into the respective migrate_map_* table, and check if a row with this id was already imported, and if the imported entity still exists. If it does, it will update this existing entity, and update the value last_imported in the migrate map table. If it does not, it will create a new entity, and create a new entry in the migrate_map_* table.

What I want instead: Map to existing entities.

Now what if instead of creating new entities, I would like to update existing entities which were previously created with another mechanism, unknown to Migrate API? E.g. these might have been created manually, or with a home-grown migration script.

In my case, these entities are commerce products, which were created with a custom script which is to be replaced with Migrate API.

The idea is:

1. Compute an SKU from the imported csv row.
2. Find out if a product with this SKU already exists.
   • If yes, update.
   • If no, create a new product.
3. If possible, save the import timestamp somewhere.

Solutions I can think of so far

I can imagine two possible solutions.

1. A custom migrate map class SkuMigrateMap extends MigrateMap.
   Seeing how many abstract methods MigrateMap has, this could be quite a lot of work.
2. Use MigrateSQLMap, but prefill the table based on existing products.

Question

Is there an existing or recommended solution for this somewhere?

Answer 1

The map class doesn't (and shouldn't) know anything about either your source or your destination - its job is simply tracking the relationships between their keys.

I've done this in the migration's prepareRow(), doing an explicit lookup for a matching destination object, and if found saving that destination ID to the map and returning FALSE to prevent further processing. Look at how migrate_d2d maps user accounts based on email:

// On initial import, if this email address already exists, just map to the
// existing uid and don't allow it to be deleted. When updating, we can
// take the existing uid from the map instead of querying for it. Make sure
// when updating an imported user that we don't apply this logic.
if (empty($row->migrate_map_destid1)) {
  $new_uid = db_select('users', 'u')
             ->fields('u', array('uid'))
             ->condition('mail', $row->mail)
             ->execute()
             ->fetchField();
  // If we haven't mapped the admin account based on email address, map it
  // directly to the destination admin account.
  if (!$new_uid && $row->uid == 1) {
    $new_uid = 1;
  }
}
elseif ($row->migrate_map_needs_update == MigrateMap::STATUS_IGNORED &&
        $row->migrate_map_rollback_action == MigrateMap::ROLLBACK_PRESERVE) {
  $new_uid = $row->migrate_map_destid1;
}

if (!empty($new_uid)) {
  $hash = isset($row->migrate_map_hash) ? $row->migrate_map_hash : NULL;
  $this->map->saveIDMapping($row, array($new_uid), MigrateMap::STATUS_IGNORED,
    MigrateMap::ROLLBACK_PRESERVE, $hash);
  $this->rollbackAction = MigrateMap::ROLLBACK_PRESERVE;
  return FALSE;
}

Answer 2

I found that in my case I had to do it a little differently than Mike Ryan suggests. This is not a right vs wrong, it simply depends on the intended behavior.

The problem was that with Mike Ryan's solution the existing legacy products would have been left as they are and not updated.

The solution below is actually easier. It does not require to create a migrate mapping from this user code. Instead all you do is set $row->migrate_map_destid1 = $existing_product_id.

Note that all the lines dealing with revision messages are not really necessary, but I find them useful.

Also note that this solution is not setting $this->rollbackAction = MigrateMap::ROLLBACK_PRESERVE. This would prevent the updated legacy products from being rolled back. More on this further below, step II.

Step I: Modifications to the migration class.

class MyMigration extends MigrationBase {

  /**
   * @var int[]
   *   Format: $[$sku] = $product_id
   */
  private $productIdsBySku;

  /**
   * @param array $arguments
   */
  function __construct(array $arguments) {
    [..]
    $this->productIdsBySku = db_select('commerce_product', 'cp')
      ->fields('cp', array('sku', 'product_id'))
      ->execute()
      ->fetchAllKeyed();
    [..]

    // The following only makes sense if you continue reading further below, step II.
    $this->destination = new MyMigrateDestination();
  }

  function prepareRow($row) {

    if (!parent::prepareRow($row)) {
      return FALSE;
    }

    $sku = $this->rowBuildSku($row);

    if (isset($this->productIdsBySku[$sku])) {
      // Product with same sku exists.
      $existing_product_id = $this->productIdsBySku[$sku];
      if (empty($row->migrate_map_destid1)) {
        // Product exists, but is NOT in the migrate map.
        $row->migrate_map_destid1 = $existing_product_id;
        $row->revision_log_message = 'Migrate: Update legacy product.';

      }
      // Possibly comparing string vs integer, so using == and not ===.
      elseif ($row->migrate_map_destid1 == $existing_product_id) {
        // Product exists, and is in the migrate map, with correct id.
        $row->revision_log_message = 'Migrate: Update already-imported product.';
      }
      else {
        // Crazy case: Product is mapped, but to wrong id.
        // This means that something went wrong in the past.
        // @todo Log this problem?
        return FALSE;
      }
    }
    else {
      // Product with this SKU does not exist yet.
      if (empty($row->migrate_map_destid1)) {
        // This is really a new product.
        $row->revision_log_message = 'Migrate: Create new product.';
      }
      else {
        // The migrate map suggests this is an update. But the product to be
        // updated does not exist. Weird.
        // Let migrate module decide what to do.
        $row->revision_log_message = 'Migrate: Create product, which was already mapped.';
      }
    }

    return TRUE;
  }

  function prepare(\stdClass $product, \stdClass $row) {
    [..]
    if (isset($row->revision_log_message)) {
      $product->log = $row->revision_log_message;
    }
    [..]
  }

Now, this solution still has a problem.

On rollback, the updated legacy products will be deleted instead of reverting to the old pre-migrate revision.

Step II: Custom migrate destination class.

Solution: A custom migrate destination class with an overridden bulkRollback method. (Actually in the code above I cheated, and it already contains the code to add this destination in the migration class.)

This overridden bulkRollback() decides for each product (entity) to be rolled back whether it should be deleted or an old revision restored.

It uses a heuristic to find an old revision, but this only works in my own special case. You likely need to do something else! More about this further below, step III.

class MyMigrateDestination extends \MigrateDestinationEntityAPI {
  [..]

  /**
   * @param int[]|string[] $ids
   *
   * @return bool
   */
  public function bulkRollback(array $ids) {
    migrate_instrument_start(__METHOD__);
    $this->prepareRollback($ids);

    $q = db_select('commerce_product_revision', 'cpr');
    $q->condition('cpr.product_id', $ids);

    // @todo You need to be creative and find a different criterion here.
    $q->condition('cpr.type', 'product');

    $q->fields('cpr', array('product_id', 'revision_id'));
    $q->orderBy('cpr.revision_timestamp');
    // This gets the youngest revision.
    $rvids_by_id = $q->execute()->fetchAllKeyed();

    migrate_instrument_start('revert revisions');
    foreach ($rvids_by_id as $product_id => $revision_id) {
      $entity = entity_revision_load('commerce_product', $revision_id);
      if (FALSE === $entity) {
        unset($rvids_by_id[$product_id]);
        continue;
      }
      $entity->revision = TRUE;
      $entity->log = "Revert via migrate rollback.";
      entity_save('commerce_product', $entity);
    }
    migrate_instrument_stop('revert revisions');

    $ids_to_delete = array_diff($ids, array_keys($rvids_by_id));

    migrate_instrument_start('entity_delete_multiple');
    $deletion_success = FALSE !== entity_delete_multiple($this->entityType, $ids_to_delete);
    migrate_instrument_stop('entity_delete_multiple');

    $this->completeRollback($ids);
    migrate_instrument_stop(__METHOD__);

    return $deletion_success;
  }

}

Part III: Criterion for finding the old pre-migrate revision.

Now there is one piece of homework left for the esteemed reader.

The query above relies on a specificity of my personal use case. In my personal own use case, the migration would change the bundle of legacy entities. This makes it easy to distinguish between revisions before and after the migration was introduced.

    $q->condition('cpr.type', 'product');

However, even with this specificity, this only works because I "hacked" the commerce_product_revision table by adding a "bundle" column, using a module I cooked up for this purpose. I suspect that this is a rather exotic scenario.

Now you need to figure out another way to distinguish product revisions from before and after the migration, and work it into the query.