1

I am using Migrate API to import products from a CSV file that is updated every week or so.

Every time the CSV file is updated,

  • new products may be introduced
  • existing products may be modified
  • old products may disappear

Migrate currently does not seem to have a mechanism to remove or unpublish orphan content from the site, where the source record no longer exists.

See e.g.

What kind of solution or workaround could be used to deal with this old content?

"Deal with" could mean one of the following:

  • Delete "old" entities from the site.
  • Unpublish "old" entities.
  • Have a property or field that tells us when an entity was last seen in a migrate source record. This would allow e.g. to filter such entities in Views.

Notes

Please consider that migrations do not always process the complete source data. E.g. you could use the --limit parameter, or manually stop the migration, before all source rows are processed. Of course the typical nightly or weekly migration will process the complete source data. But manual migration runs often do not.

Also consider that a migration will usually not update rows that are unchanged. If we would store a "last import date" in a field, this would cause a lot more entity_save() operations, so it would significantly slow down migrations.

  • I hesitate to post this as an actual answer (I might convert this comment if it helps), but how about using the Flag module to flag each entity whenever you're processing your CSV file (possibly with an extra flag field to store the date of the processing of the CSV file). And then you create a view to list the entities that were flagged "before the last CSV file was processed". – Pierre.Vriens Jun 10 '17 at 6:36
  • @Pierre.Vriens I'm not sure if this really helps. Why the flag module? In my understanding, it is designed to store information per entity and per user, whereas in this case we want to store something per entity. – donquixote Jun 10 '17 at 17:32
  • If you'd use a "global" flag it would NOT be "per user". And by using the flag module, you could take advantage of the Rules module to make all kinds of magic happen related to your specific requirements. – Pierre.Vriens Jun 10 '17 at 19:19
  • If you are convinced of the solution, feel free to post it as an answer! I somehow feel I will do something else myself, but it might still help others. And this will create a dedicated place for discussion. – donquixote Jun 10 '17 at 20:08
1

Here is the solution that I came up with.

TLDR: Drush command to compare source data vs existing products, and activate or deactivate products accordingly.

Use case / goal

I am focusing on a specific use case, to make this easier to explain. Something similar could be done for other use cases.

Commerce products are imported with a migration. Both source and destination have one numeric primary key. The migrate map is MigrateSQLMap, so there is a migrate_map_* table in the database with destid1 and sourceid1.

Goal: Change the "status" property of commerce products which are no longer in the source data.

Considerations

A migration run can be incomplete. In such a case, we do not want remaining products to be deactivated.

Updating an entity (commerce product) in Drupal takes time. We do not want to update every entity just to change a timestamp.

We have to assume that the entire list of products can be quite big, so it can be problematic to have them all in memory at once. However, in this solution, I assume that having the ids (source id mapped to destination id) in memory is still acceptable.

Solution: Custom drush command

Create a custom drush command that does the following:

Query the database, join the commerce_product AS cp table with the migrate_map_MYMIGRATION AS mm table. For each row we want mm.sourceid1, mm.destid1 and cp.status. Load the complete list into an array as $maps[$row->status][$row->sourceid1] = $row->destid1.

Assign the two parts of the array to new variables $inactiveIdsMap = $maps[0]; and $activeIdsMap = $maps[1];.

Create two additional associative arrays, each with format $[$sourceid] = $destid:

  • $idsToActivateMap = [];. Start value: empty array.
  • $idsToDeactivateMap = $activeIdsMap. Start value: All ids for active products.

You may want to unset($activeIdsMap); to free memory. (We could have reused this variable instead of declaring a new one, but I like having variable names that are accurate throughout a variables's life.)

Iterate through the source data. You can get the migrate source from the migration. It is a bit tricky but it can be made to work.

For each source row:

  • If the id exists in $inactiveIdsMap, add the source + dest id to the $idsToActivateMap array.
  • In any case, unset this entry from the $idsToDeactivateMap array.

Now deactivate all to-be-deactivated products, and activate all to-be-activated products.

You ideally do this in chunks, so e.g. you load 20 products at once with commerce_product_load_multiple(), set each one's status, and save them individually with commerce_product_save().

This drush command can be executed after the migration.

Memory impact?

You can measure memory use with memory_get_usage() or memory_get_peak_usage(). If you use memory_get_usage(), you need to call it in the place where you expect memory usage to be highest. Memory pretty print: http://codeaid.net/php/convert-size-in-bytes-to-a-human-readable-format-(php)

The way to check for the impact of this specific operation is to run the command with a subset of data. E.g. put a ->range() on the select query for loading the migrate map. This should make all arrays smaller.

Why? Alternatives?

Another solution that I found tempting at first was to maintain a "lastseen" timestamp in a separate table.

However, this would mean either having to update each product on every migration run, or at least reindex the search index each time, because we would want to filter by "lastseen".

With the solution presented here, in the average case, only few products have to be updated.

It turned out that the memory impact was very manageable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.