TYPO3: Finding unused files in fileadmin

You can easily find orphaned files and delete them if necessary. We provide a command for this purpose that you simply need to insert into your site package (the namespace will of course need to be adjusted accordingly).

But beware: Not all used files can be identified with 100% certainty (sometimes assets are used in HTML content or in an external newsletter tool, etc.). The command also does not recognize files that are only used via their folder (e.g., as a file collection) as being used. 
Therefore, as with all destructive actions, you should first check in the dry run whether these files can really be deleted.

Example calls:

# Dryrun (show only affected files) in Folder fileadmin/Images/ with a limit of 1000 files:
./vendor/bin/typo3 in2template:deleteorphanedsysfiles 1 1:/Images/ 1000

# Hotrun (show and delete affected files) in Folder fileadmin/Images/ with a limit of 1000 files:
./vendor/bin/typo3 in2template:deleteorphanedsysfiles 0 1:/General/ 1000

The command as a PHP file:

<?php

declare(strict_types=1);

namespace In2code\In2template\Command;

use Doctrine\DBAL\Exception as DbalException;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use TYPO3\CMS\Core\Core\Bootstrap;
use TYPO3\CMS\Core\Core\Environment;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\DataHandling\DataHandler;
use TYPO3\CMS\Core\Resource\Folder;
use TYPO3\CMS\Core\Resource\ResourceFactory;
use TYPO3\CMS\Core\Resource\ResourceStorage;
use TYPO3\CMS\Core\Resource\StorageRepository;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * Find orphaned files and delete them
 *  E.g. in 3:/General/
 *      # Dryrun:
 *      ./vendor/bin/typo3 in2template:deleteorphanedsysfiles 1 3:/General/ 99
 *
 *      # Hotrun:
 *      ./vendor/bin/typo3 in2template:deleteorphanedsysfiles 0 3:/General/ 99
 */
#[AsCommand(
    name: 'in2template:deleteorphanedsysfiles',
    description: 'Deletes orphaned sys_file records in a certain folder',
)]
class DeleteOrphanedSysFilesCommand extends Command
{
    private const string TABLE_SYS_FILE = 'sys_file';
    private const string TABLE_SYS_FILE_REFERENCE = 'sys_file_reference';
    private const int DEFAULT_LIMIT = 100;

    /**
     * @var array<int, ResourceStorage>
     */
    private array $storageCache = [];

    public function __construct(
        private readonly ConnectionPool $connectionPool,
        private readonly StorageRepository $storageRepository,
        private readonly ResourceFactory $resourceFactory,
    ) {
        parent::__construct();
    }

    public function configure(): void
    {
        $this->addArgument('dryrun', InputArgument::REQUIRED, 'Run command as dry run (1 or 0)');
        $this->addArgument(
            'storageAndPath',
            InputArgument::REQUIRED,
            'Storage and folder path in format "storage:/path/" (e.g. "3:/General/")'
        );
        $this->addArgument('limit', InputArgument::OPTIONAL, 'Limits the deleted files per run', self::DEFAULT_LIMIT);
    }

    public function execute(InputInterface $input, OutputInterface $output): int
    {
        $result = Command::FAILURE;
        $dryRun = (bool)$input->getArgument('dryrun');
        $storageAndPath = $input->getArgument('storageAndPath');
        $limit = (int)$input->getArgument('limit');

        if (is_string($storageAndPath) === false || $storageAndPath === '') {
            $output->writeln('storageAndPath must be a non empty string (e.g. "3:/General/").');
        } else {
            $folder = $this->resolveFolder($storageAndPath);
            $storageId = $folder->getStorage()->getUid();
            $folderPath = $folder->getIdentifier();

            if ($limit > self::DEFAULT_LIMIT) {
                $output->writeln('Be careful increasing that number otherwise this might take a while.');
            }

            try {
                $orphanedSysFiles = $this->findOrphanedSysFiles($folderPath, $storageId, $limit);
                $this->outputOrphanedSysFiles($output, $orphanedSysFiles);

                if ($dryRun === true) {
                    $result = Command::SUCCESS;
                } else {
                    $result = $this->processFileDeletion($output, $orphanedSysFiles);
                }
            } catch (\Throwable $exception) {
                $output->writeln('Error: ' . $exception->getMessage());
            }
        }

        return $result;
    }

    /**
     * @return array<int, array{uid: int, identifier: string, storage: int}>
     * @throws DbalException
     */
    private function findOrphanedSysFiles(string $folderPath, int $storageId, int $limit): array
    {
        $normalizedPath = $this->normalizeFolderPath($folderPath);
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_SYS_FILE);
        $queryBuilder
            ->select('sf.uid', 'sf.identifier', 'sf.storage')
            ->from(self::TABLE_SYS_FILE, 'sf')
            ->leftJoin(
                'sf',
                self::TABLE_SYS_FILE_REFERENCE,
                'sfr',
                $queryBuilder->expr()->eq('sf.uid', $queryBuilder->quoteIdentifier('sfr.uid_local'))
            )
            ->where(
                $queryBuilder->expr()->like(
                    'sf.identifier',
                    $queryBuilder->createNamedParameter($normalizedPath . '/%')
                ),
                $queryBuilder->expr()->eq(
                    'sf.storage',
                    $queryBuilder->createNamedParameter($storageId, Connection::PARAM_INT)
                ),
                $queryBuilder->expr()->isNull('sfr.uid')
            )
            ->setMaxResults($limit);

        return $queryBuilder->executeQuery()->fetchAllAssociative();
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $sysFiles
     */
    private function outputOrphanedSysFiles(OutputInterface $output, array $sysFiles): void
    {
        foreach ($sysFiles as $orphanedSysFile) {
            $output->writeln($orphanedSysFile['uid'] . ': ' . $orphanedSysFile['identifier']);
        }

        $output->writeln(sprintf('Found %s orphaned file(s)', count($sysFiles)));
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $orphanedSysFiles
     */
    private function processFileDeletion(OutputInterface $output, array $orphanedSysFiles): int
    {
        $output->writeln('Start deleting files...');

        $orphanedSysFilesAmount = count($orphanedSysFiles);
        $failedSysFiles = $this->deleteSysFiles($orphanedSysFiles);
        $deletedSysFilesAmount = $orphanedSysFilesAmount - count($failedSysFiles);

        $output->writeln('Deleted ' . $deletedSysFilesAmount . ' out of ' . $orphanedSysFilesAmount . ' files.');

        $result = Command::FAILURE;
        if ($orphanedSysFilesAmount === $deletedSysFilesAmount) {
            $result = Command::SUCCESS;
        }

        return $result;
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $sysFiles
     * @return array<int, array{uid: int, identifier: string, storage: int}>
     */
    private function deleteSysFiles(array $sysFiles): array
    {
        $failedSysFiles = [];

        Bootstrap::initializeBackendAuthentication();
        $dataHandler = GeneralUtility::makeInstance(DataHandler::class);
        $dataHandler->start([], []);

        foreach ($sysFiles as $sysFile) {
            $uid = $sysFile['uid'] ?? null;
            $identifier = $sysFile['identifier'] ?? null;
            $storageId = $sysFile['storage'] ?? null;

            if ($uid === null || $identifier === null || $storageId === null) {
                $failedSysFiles[] = $sysFile;
                continue;
            }

            $absoluteFilePath = $this->getAbsoluteFilePath($identifier, $storageId);

            if ($absoluteFilePath === '') {
                $failedSysFiles[] = $sysFile;
                continue;
            }

            if (file_exists($absoluteFilePath)) {
                unlink($absoluteFilePath);
            }

            $dataHandler->deleteRecord(self::TABLE_SYS_FILE, $uid, true, true);
        }

        return $failedSysFiles;
    }

    private function getAbsoluteFilePath(string $identifier, int $storageId): string
    {
        $absolutePath = '';

        if (array_key_exists($storageId, $this->storageCache) === false) {
            $storage = $this->storageRepository->findByUid($storageId);
            if ($storage !== null) {
                $this->storageCache[$storageId] = $storage;
            }
        }

        if (array_key_exists($storageId, $this->storageCache)) {
            $basePath = $this->storageCache[$storageId]->getConfiguration()['basePath'] ?? '';

            if ($basePath !== '') {
                $basePath = str_starts_with($basePath, '/') ? $basePath : '/' . $basePath;
                $basePath = str_ends_with($basePath, '/') ? substr($basePath, 0, -1) : $basePath;
                $identifier = str_starts_with($identifier, '/') ? $identifier : '/' . $identifier;

                $absolutePath = Environment::getPublicPath() . $basePath . $identifier;
            }
        }

        return $absolutePath;
    }

    private function resolveFolder(string $storageAndPath): Folder
    {
        $resource = $this->resourceFactory->retrieveFileOrFolderObject($storageAndPath);

        if ($resource instanceof Folder === false) {
            throw new \Exception(
                'The given path "' . $storageAndPath . '" does not resolve to a folder.',
                1738764000
            );
        }

        return $resource;
    }

    private function normalizeFolderPath(string $folderPath): string
    {
        $normalizedPath = str_starts_with($folderPath, '/') ? $folderPath : '/' . $folderPath;
        $normalizedPath = str_ends_with($normalizedPath, '/') ? substr($normalizedPath, 0, -1) : $normalizedPath;

        return $normalizedPath;
    }
}