How to delete duplicate path aliases?

Question

Duplicate path aliases are seen as rows in the path_alias table with identical path and alias columns.

Due to old bugs in pathauto and other reasons, like buggy custom code, it is possible to have duplicate rows in the path_alias table. This can make the path_alias enormous for large sites.

Duplicate path aliases serve no purpose whatsoever. You only need one of each.

Answer 1

If there are many duplicates, use the queue system to delete them gradually. Start by enqueuing identified duplicates in an update hook:

// file: my_module.install
/**
 * Enqueue duplicate path aliases for cleanup.
 */
function my_module_update_10001() {
  $query = \Drupal::database()->select('path_alias', 'pa')
    ->fields('pa', ['alias']);
  $query->addExpression('count(alias)');
  $query->groupBy('alias');
  $query->having('COUNT(alias) >= :matches', [':matches' => 2]);
  $results = $query->execute()->fetchAll(\PDO::FETCH_COLUMN);
  \Drupal::logger('my_module')->notice('Enqueuing duplicate path aliases for cleanup');
  foreach ($results as $alias) {
    $queue = \Drupal::queue('my_module_delete_duplicate_path_aliases');
    $queue->createItem($alias);
  }
  return t('Update 10001 completed successfully.');
}

Add the my_module_delete_duplicate_path_aliases queue runner to do the work:

<?php
// file: src/Plugin/QueueWorker/DeleteDuplicatePathAliases.php

namespace Drupal\my_module\Plugin\QueueWorker;

use Drupal\Core\Entity\EntityTypeManagerInterface;
use Drupal\Core\Logger\LoggerChannelFactoryInterface;
use Drupal\Core\Plugin\ContainerFactoryPluginInterface;
use Drupal\Core\Queue\QueueWorkerBase;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Deletes duplicate path aliases.
 *
 * @QueueWorker(
 *   id = "my_module_delete_duplicate_path_aliases",
 *   title = @Translation("Delete duplicate path aliases"),
 *   cron = {"time" = 30}
 * )
 */
class DeleteDuplicatePathAliases extends QueueWorkerBase implements ContainerFactoryPluginInterface {

  /**
   * {@inheritDoc}
   */
  final public function __construct(
    array $configuration,
    $plugin_id,
    $plugin_definition,
    private LoggerChannelFactoryInterface $logger,
    private EntityTypeManagerInterface $entityTypeManager,
  ) {
    parent::__construct($configuration, $plugin_id, $plugin_definition);
  }

  /**
   * {@inheritDoc}
   */
  public static function create(ContainerInterface $container, array $configuration, $plugin_id, $plugin_definition) {
    return new static(
      $configuration,
      $plugin_id,
      $plugin_definition,
      $container->get('logger.factory'),
      $container->get('entity_type.manager'),
    );
  }

  /**
   * {@inheritdoc}
   */
  public function processItem($data) {
    $alias = (string) $data;
    $query = $this->entityTypeManager->getStorage('path_alias')->getQuery();
    $entity_ids = $query
      ->accessCheck(FALSE)
      ->condition('alias', $alias)
      ->sort('id', 'DESC')
      ->execute();

    /** @var \Drupal\path_alias\Entity\PathAlias[] $entities */
    $entities = $this->entityTypeManager->getStorage('path_alias')->loadMultiple($entity_ids);

    $count = 0;
    // Keep the newest unique aliases by language and path, and delete the rest.
    $seen = [];
    foreach ($entities as $entity) {
      if (isset($seen[$entity->language()->getId()][$entity->getPath()])) {
        $entity->delete();
        $count++;
      }
      $seen[$entity->language()->getId()][$entity->getPath()] = TRUE;
    }
    $this->logger->get('my_module')->notice('Deleted ' . $count . ' duplicate path aliases for ' . $alias);
  }

}