Delete or unpublish content from old migration, if source rows no longer exist 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.


Migrate module FAQ: "A negative number means the current source has fewer records than we have cumulatively imported from the source. This could mean that records have been deleted on the source side (there is no built-in support for deleting destination records based on the absence of the corresponding source record)."
Issue #2809433: Migrate support for deleting items no longer in the incoming data, an issue currently in "Needs work".


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.
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-&gt;status][$row-&gt;sourceid1] = $row-&gt;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 -&gt;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.