API Platform Distinct ORM Filter

DistinctFilter.php

<?php

declare(strict_types=1);

namespace App\Doctrine\Filter;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;

use function count;
use function explode;
use function implode;
use function in_array;

final class DistinctFilter extends AbstractContextAwareFilter
{
    /**
     * @param array<mixed> $properties
     */
    public function __construct(
        ManagerRegistry $managerRegistry,
        ?RequestStack $requestStack = null,
        public string $distinctParameterName = 'distinct',
        LoggerInterface $logger = null,
        array $properties = null
    ) {
        parent::__construct($managerRegistry, $requestStack, $logger, $properties);
    }

    /**
     * @param array<mixed> $context
     */
    public function apply(
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        string $operationName = null,
        array $context = []
    ): void {
        if (!isset($context['filters'][$this->distinctParameterName])) {
            return;
        }

        $value = $context['filters'][$this->distinctParameterName];
        $allowed = [];
        if (isset($this->properties[$this->distinctParameterName])) {
            $allowed = (array) $this->properties[$this->distinctParameterName];
        }
        if (!in_array($value, $allowed, true)) {
            return;
        }

        $this->filterProperty(
            $this->distinctParameterName,
            $value,
            $queryBuilder,
            $queryNameGenerator,
            $resourceClass,
            $operationName,
            $context
        );
    }

    /**
     * @param array<mixed> $context
     * @param string|null $value
     */
    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        string $operationName = null,
        array $context = []
    ): void {
        if ($value === null) {
            return;
        }

        $lastAlias = $queryBuilder->getRootAliases()[0];
        $joins = explode('.', $value);
        $count = count($joins);
        $groupBy = null;

        foreach ($joins as $index => $joinPart) {
            $currentAlias = $joinPart;
            if ($index === $count - 1) {
                $groupBy = "{$lastAlias}.{$currentAlias}";
            } else {
                $join = "{$lastAlias}.{$currentAlias}";
                if (!in_array($currentAlias, $queryBuilder->getAllAliases(), true)) {
                    $queryBuilder->leftJoin($join, $currentAlias);
                }
            }
            $lastAlias = $currentAlias;
        }

        if ($groupBy) {
            $queryBuilder->select($groupBy)->groupBy($groupBy)->setMaxResults(10);
        }
    }

    /**
     * @return array<mixed>
     */
    public function getDescription(string $resourceClass): array
    {
        $fields = $this->properties[$this->distinctParameterName] ?? [];
        return [
            $this->distinctParameterName => [
                'property' => $this->distinctParameterName,
                'type' => 'string',
                'required' => false,
                'swagger' => [
                    'description' => 'Distinct filter on possible fields: ' . implode(', ', (array) $fields)
                ],
            ]
        ];
    }
}

GroupExtension.php

<?php

declare(strict_types=1);

namespace App\Doctrine\Extension;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Extension\ContextAwareQueryResultCollectionExtensionInterface;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\QueryBuilder;

use function array_key_exists;

final class GroupExtension implements ContextAwareQueryResultCollectionExtensionInterface
{
    private bool $supportsResult = false;

    /**
     * @param array<mixed> $context
     */
    public function applyToCollection(
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        string $operationName = null,
        array $context = []
    ): void {
        if (!array_key_exists('filters', $context) || !array_key_exists('distinct', $context['filters'])) {
            return;
        }

        if ($queryBuilder->getMaxResults() !== null && $queryBuilder->getDQLPart('groupBy') !== []) {
            $this->supportsResult = true;
        }
    }

    /**
     * @param array<mixed> $context
     */
    public function supportsResult(string $resourceClass, string $operationName = null, array $context = []): bool
    {
        return $this->supportsResult;
    }

    /**
     * @param array<mixed> $context
     * @return int|iterable|mixed|string
     */
    public function getResult(
        QueryBuilder $queryBuilder,
        string $resourceClass = null,
        string $operationName = null,
        array $context = []
    ) {
        return $queryBuilder->getQuery()->getResult();
    }
}

services.yaml

    App\Doctrine\Extension\GroupExtension:
        tags:
            - { name: api_platform.doctrine.orm.query_extension.collection, priority: -18 }

Usage.md

Entity

use App\Doctrine\Filter\DistinctFilter;

#[ApiFilter(
    DistinctFilter::class,
    properties: ['distinct' => ['field1', 'field2']] // 2 properties allowed to be a subject of the filter
)]

Add in the URL

?distinct=field1

In this example query will be run with with field1 property (configuration above allows fields field1 and field2).

Query

This filter is not actually adding DISTINCT to a query. It forces SELECT and GROUP BY on chosen field so in the end results will be groupped. To make sure that SELECT and GROUP BY do not contain additional fields (that might be added by other filters) this filter forces fetching the results. That is why you must configure it with priority -18 (or anything before pagination).