migration_lookup process plugin can't use specific subset of source keys to use for lookup

      source:
        - field_name
        - entity_type

That's could do to be documented!

The use case is that I need a reference field value, but I don't have the entity ID for those items in the source.

More specifically, I had Commerce License entities, which don't exist in my source D6 DB and so are migrated from custom tables joined to the Ubercart order_product table.

When I migrate Order Items (from the order_product table), I have the order_product ID to use to get a lookup to the migrated License. That's a source key in the License migration, but not the first key.

It occurs to me that if a source migration has keys alpha, beta, gamma, and you want to look up with the gamma key, this might work:

      plugin: migration_lookup
      migration: d7_field
      source:
        - constants/empty
        - constants/empty
        - actual_value

where constants/empty is defined as a constant in the source section of the YAML that's either '' or NULL -- depending on what works with the MigrationLookup plugin. Would that actually work to mean those keys are to be ignored, or would the lookup query for a literal NULL or ''?

If it does work, it's still very fiddly though, and would need documenting.

> When I migrate Order Items (from the order_product table), I have the order_product ID to use to get a lookup to the migrated License. That's a source key in the License migration, but not the first key.

To expand on my use case in reply to your question on slack:

IIRC my source keys in my license migration are:

- the source custom table's primary key - this is enough to be unique for this migration, but other dependent migrations won't have this ID to query by
- OG group node ID (which the license grants membership of) - mostly used for checking the data after import. not unique
- User ID (together with the group node ID forms a unique key)
- Commerce order item ID - this is also enough to be unique for this migration

And my dependent migration is doing Commerce Order items. The order items have a reference field to the license. I specifically added the order item ID key to the licenses migration so I could use it in the lookup, as otherwise I'd need some fiddly queries to get the other source key values.

Here's another use case for this, in Commerce Migrate's Ubercart D6 migrations:

We have a billing profile migration which uses orders as its source, but queries to get only the most recent order for each user. So not every souce order will produce a destination billing profile.

When it comes to the order migration, there is a lookup to find the value for the entity reference field that points to the billing profile.

Currently, there is a bug because this is using the order ID for the lookup -- but not every source order ID has a destination billing profile ID, so the lookup will often fail. (#2969268: migrated orders can fail to get a billing profile).

One way to fix this which would be fairly elegant would be to use the order's uid as the lookup value. But from a data structure point of view, it makes sense to keep the billing profile migration's sourceid1 as the source order ID, since that is the primary key of the source table.

So for the lookup, we would add a 2nd source ID to the migration map, which would be the uid.

We'd then want the order migration's billing_profile field to do a migration lookup to the billing_profile migration, using the uid value as the source...

And that's where this bug comes in -- we can't do that.

This is actually *really* close to being doable. Doing a lookup query on the map with named source IDs is supported, as the docs for MigrateIdMapInterface::lookupDestinationIds() say:

   * This can look up a subset of source keys if only some are provided, and
   * will return all destination keys that match.

and the code for the Sql map implementation clearly supports it:

    foreach ($this->sourceIdFields() as $field_name => $db_field) {
      if ($is_associative) {
        // Associative $source_id_values can have fields out of order.
        if (isset($source_id_values[$field_name])) {
          $conditions[$db_field] = $source_id_values[$field_name];
          unset($source_id_values[$field_name]);
        }
      }

However, if I do this in my migration:

  my_destination:
    plugin: migration_lookup
    migration:
      - my_lookup_migration
    source:
      my_key_name: my_source_field

then the $value received by MigrationLookup::transform() is merely a numeric array -- the 'my_key_name' part is lost.

Losing that key though is fundamental to how Migrate works -- it's done in MigrateExecutable. So I don't think we can change that.

I'm having a look to see whether the lookup plugin could be tweaked so the source_ids configuration has keys...

Figured it out.

Here's a patch.

Hmm the docs should be tweaked to say that you can use 'source_ids' without 'migrations', just to specify the IDs.

No, that fixed something else. It still doesn't allow you to specify *which* of the migration's source keys to use. So with this example:

  public function getIds() {
    return [
      'type' => [
        'type' => 'integer',
        'alias' => 'cm',
      ],
      'id' => [
        'type' => 'integer',
        'alias' => 'cm',
      ],
    ];

I can't specify in my migration that I will be passing in the value for the 'id' source key only.

That's because of how this code works:

      if (isset($this->configuration['source_ids'][$migration_id])) {
        $configuration = ['source' => $this->configuration['source_ids'][$migration_id]];
        $value = $this->processPluginManager
          ->createInstance('get', $configuration, $this->migration)
          ->transform(NULL, $migrate_executable, $row, $destination_property);
      }

Here $value will always be a numeric array -- that what Get::transform returns.

      if ($destination_ids = $migration->getIdMap()->lookupDestinationId($source_id_values[$migration_id])) {

So we can never pass keys to lookupDestinationId() to tell it which IDs we are passing values for.

> it's about allowing lookup with using only a subset of keys defined in getIds().

Exactly.

Currently, the only kind of subset you can do is a slice starting at the beginning.

Thanks for the test fix and the review!

> + * map, for each lookup migration if more than one is involved. For this, use

That's correct as a singular.

Other points addressed in the new patch.

> @joachim, could you kindly draw a picture of what we're trying to achieve in the use case you shared in #6 and add it to the handbook page?

Sorry, I have no idea what a picture of that should look like.

That example is maybe no longer appropriate, as there is work happening on another Commerce Migrate issue to change that migration to work differently.

It's also a rather complicated example to illustrate this functionality, which is perhaps better expressed like this:

- sometimes you want to use a lookup migration for a value, but instead of that migration's primary key, the value you have available is another field, which is also unique for that migration

Removing

> 1. The ability to specify multiple source keys

from the IS and the title, as this is now possible:

        $lookup_value = array_values($row->getMultiple($this->configuration['source_ids'][$lookup_migration_id]));

Updated IS again.

There's a backwards compatibility & DX problem we haven't spotted.

We add support for this:

 *       source_ids:
 *         users:
 *           key3: author

but remove support for this, without keys:

 *       source_ids:
 *         users:
 *           author

That's a BC break, and it's also not good DX if you want to specify all the lookup keys.