Easiest way to find out if a paragraph entity is orphaned from the latest revision

This is also useful to have in views queries when the base table is the one of Paragraphs.

We use paragraphs extensively, and I use Node Revision Delete → to delete old revisions, but when I do so, some paragraphs related to old no-longer-existing revisions keep existing and are orphaned.

I created a PHP script which deletes old orphaned paragraphs:

// https://www.drupal.org/project/paragraphs/issues/3358560

global $argv;

if (!array_key_exists(4, $argv)) {
  throw new \Exception('Please use argument delete-orphans or do-not-delete-orphans');
}
if ($argv[4] == 'delete-orphans') {
  $delete = TRUE;
}
elseif ($argv[4] == 'do-not-delete-orphans') {
  $delete = FALSE;
}
else {
  throw new \Exception('Please use argument delete-orphans or do-not-delete-orphans, not ' . $argv[4]);
}

$query = \Drupal::entityQuery('paragraph');
$query->accessCheck(FALSE);
$all_paragraph_ids = $query->execute();
$count = count($all_paragraph_ids);
print_r('There are ' . $count . ' paragraphes' . PHP_EOL);
$where = [];
$paragraph_entities = [];

if ($count) {
  $all_paragraphs = \Drupal::entityTypeManager()->getStorage('paragraph')
    ->loadMultiple($all_paragraph_ids);

  foreach ($all_paragraphs as $paragraph) {
    $parent = $paragraph->getParentEntity();

    if ($parent) {
      $parent_entity_type = get_class($parent);
      $parent_id = $parent->id();
      $parent_revision_id = $parent->getRevisionId();
      $identifier = $parent_entity_type . ':' . $parent_id . ':' . $parent_revision_id;
    }
    else {
      $identifier = 'NULL_PARENT';
    }

    $where[$identifier][$paragraph->id()] = $paragraph;
  }
}

print_r('They are in ' . $all = count($where) . ' locations' . PHP_EOL);
$i = 1;

$orphans = [];

foreach ($where as $identifier => $paragraphs) {
  $node_revision = NULL;
  // Check if location exists
  if ($identifier != 'NULL_PARENT') {
    $parts = explode(':', $identifier);
    $id = $parts[0];
    $content_type = $parts[1];
    $revision_id = $parts[2];
    print_r('Checking location ' . $i . ' of ' . count($where) . PHP_EOL);
    print_r('  Revision ' . $revision_id . ' of ' . $content_type . ' ' . $id . PHP_EOL);

    $node_revision = \Drupal::entityTypeManager()
        ->getStorage('node')
        // Yeah I know this is deprecated but I can't find the replacement.
        ->loadRevision($revision_id);
  }

  if ($node_revision) {
    print_r('Entity revision exists, moving on.' . PHP_EOL);
  }
  else {
    print_r('Entity revision does not exist, adding ' . count($paragraphs) . ' paragraphs to our list of orphans.' . PHP_EOL);
    $orphans += $paragraphs;
  }

  $i++;
}

print_r('We found a total of ' . count($orphans) . ' orphan paragraphs out of ' . $count . ' total paragraphs.' . PHP_EOL);

if (!$delete) {
  print_r('Not deleting orphans.' . PHP_EOL);
}
elseif ($orphans) {
  print_r('Deleting orphans.' . PHP_EOL);
  foreach ($orphans as $paragraph_id => $paragraph) {
    print_r('PERMANENTLY DELETING PARAGRAPH ' . $paragraph_id . PHP_EOL);
    $paragraph->delete();
  }
}
else {
  print_r('Not deleting orphans because there are none.' . PHP_EOL);
}

Then I call it from a bash script:

#!/bin/bash
set -e

if [ "$1" != 'delete-orphans' ] && [ "$1" != 'do-not-delete-orphans' ]; then
  >&2 echo "Please specify:"
  >&2 echo ""
  >&2 echo "    ./scripts/orphan-paragraphs.sh delete-orphans"
  >&2 echo ""
  >&2 echo "or"
  >&2 echo ""
  >&2 echo "    ./scripts/orphan-paragraphs.sh do-not-delete-orphans"
  >&2 echo ""
  exit 1
fi

DELETE_OR_NOT="$1"

/var/www/html/bonjourquebec/vendor/drush/drush/drush \
  php:script ./scripts/lib/orphan-paragraphs.php \
  -- "$DELETE_OR_NOT"

Use at your own risk, and always make a backup of your database first. But for me it's pretty fast, and in prelimary tests reduced my total database size from 3823.98 Mb to 3216.81 Mb, a 15% space saving.

See also /admin/config/system/delete-orphans

You can also run:

drush err:purge