(v2.7) How to configure search filters for associations on resources with composite identifiers

[toby-griffiths]

We have the resources CustomerOrder & CustomerOrderLine. The CustomerOrderLine has composite keys that consist of the order Id + line number. The CustomerOrderLine line also has an association with TicketType, for which we would like to filter results based on. We're having to use a custom search filter because we're using ULIDs and we need to convert the string values to binary strings ourselves (API Platform doesn't do this automatically), on the GET /customer_order_lines endpoint to retrieve all lines associated with a ticket type.

The problem we have is that FilterEagerLoadingExtension, because our custom filter adds a JOIN to the query, and CustomerOrderLine has a composite key, is replacing the query WHERE clause with one that is o.orderId IN (SELECT IDENTITY(o_a2.order) FROM App\Entity\Tenant\Common\CustomerOrderLine o_a2 INNER JOIN o_a2.ticketType ticketType_a3 WHERE ticketType_a3.id = :id_p1) because the resource (order line) has a composite key. So instead of getting the matching order lines were getting all the order lines for any order that contains a matching order line.

We've solved this by replacing the composite key with an ID column, but thought I'd ask if anyone is aware of this issue, and of the best way to resolve it?

Here is our custom filter…

<?php
declare(strict_types=1);

namespace App\ApiPlatform\Bridge\Doctrine\Common\Filter;

use ApiPlatform\Core\Api\IriConverterInterface;
use ApiPlatform\Core\Bridge\Doctrine\Common\Filter\SearchFilterInterface;
use ApiPlatform\Core\Bridge\Doctrine\Common\Filter\SearchFilterTrait;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryBuilderHelper;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Core\Exception\InvalidArgumentException;
use App\Exception\DeveloperException;
use DateTimeInterface;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\PropertyAccess\PropertyAccessorInterface;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;
use Symfony\Component\Uid\Ulid;

use function array_map;
use function assert;
use function count;
use function sprintf;

class SearchUlidFieldFilter extends AbstractContextAwareFilter implements SearchFilterInterface
{
    use SearchFilterTrait;


    /**
     * This is needed by \ApiPlatform\Core\Bridge\Doctrine\Common\Filter\SearchFilterTrait.
     */
    public const DOCTRINE_INTEGER_TYPE = Types::INTEGER;


    public function __construct(
        ManagerRegistry $managerRegistry,
        IriConverterInterface $iriConverter,
        PropertyAccessorInterface $propertyAccessor,
        ?RequestStack $requestStack = null,
        LoggerInterface $logger = null,
        array $properties = null,
        NameConverterInterface $nameConverter = null
    ) {
        parent::__construct(
            $managerRegistry,
            $requestStack,
            $logger,
            $properties,
            $nameConverter
        );

        $this->iriConverter = $iriConverter;
        $this->propertyAccessor = $propertyAccessor;
    }


    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        string $operationName = null
    ): void {
        if (
            null === $value
            || !$this->isPropertyEnabled($property, $resourceClass)
            || !$this->isPropertyMapped($property, $resourceClass, true)
        ) {
            return;
        }

        $alias = $queryBuilder->getRootAliases()[0];
        $field = $property;

        /** @var list<string>|null $values */
        $values = $this->normalizeValues((array)$value, $property);
        if (null === $values) {
            return;
        }

        $associations = [];
        if ($this->isPropertyNested($property, $resourceClass)) {
            [$alias, $field, $associations] =
                $this->addJoinsForNestedProperty($property, $alias, $queryBuilder, $queryNameGenerator, $resourceClass);
        }

        $metadata = $this->getNestedMetadata($resourceClass, $associations);

        // metadata doesn't have the field, nor an association on the field
        if (!$metadata->hasAssociation($field)) {
            return;
        }

        /** @var list<Ulid> $values */
        $associationFieldIdentifier = 'id';
        $associationResourceClass = $metadata->getAssociationTargetClass($field);
        $doctrineTypeField = $this->getDoctrineFieldType($associationFieldIdentifier, $associationResourceClass);
        assert('ulid' === $doctrineTypeField, 'We should only be dealing with ULID fields with this filter.');

        $values = $this->convertUlidsToDbValues(
            $resourceClass,
            $doctrineTypeField,
            $values
        );

        if (!$this->hasValidValues($values, $doctrineTypeField)) {
            $this->logger->notice('Invalid filter ignored', [
                'exception' => new InvalidArgumentException(
                    sprintf(
                        'Values for field "%s" are not valid according to the doctrine type.',
                        $field
                    )
                ),
            ]);

            return;
        }

        $associationAlias = QueryBuilderHelper::addJoinOnce(
            $queryBuilder,
            $queryNameGenerator,
            $alias,
            $field
        );
        $associationField = $associationFieldIdentifier;

        $this->addWhereClause(
            $queryBuilder,
            $queryNameGenerator,
            $associationAlias,
            $associationField,
            $values
        );
    }


    protected function addWhereClause(
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $alias,
        string $field,
        array $values
    ): void {
        $valueParameter = ':' . $queryNameGenerator->generateParameterName($field);
        $aliasedField = sprintf('%s.%s', $alias, $field);

        if (1 === count($values)) {
            $queryBuilder
                ->andWhere($queryBuilder->expr()->eq($aliasedField, $valueParameter))
                ->setParameter($valueParameter, $values[0]);

            return;
        }

        $queryBuilder
            ->andWhere($queryBuilder->expr()->in($aliasedField, $valueParameter))
            ->setParameter($valueParameter, $values);
    }


    /**
     * Copied from SearchFilter.
     */
    protected function getType(string $doctrineType): string
    {
        return match ($doctrineType) {
            Types::ARRAY, Types::JSON => 'array',
            Types::BIGINT, Types::INTEGER, Types::SMALLINT => 'int',
            Types::BOOLEAN => 'bool',
            Types::DATE_MUTABLE,
            Types::TIME_MUTABLE,
            Types::DATETIME_MUTABLE,
            Types::DATETIMETZ_MUTABLE,
            Types::DATE_IMMUTABLE,
            Types::TIME_IMMUTABLE,
            Types::DATETIME_IMMUTABLE,
            Types::DATETIMETZ_IMMUTABLE => DateTimeInterface::class,
            Types::FLOAT => 'float',
            default => 'string',
        };
    }


    protected function getIriConverter(): IriConverterInterface
    {
        return $this->iriConverter;
    }


    protected function getPropertyAccessor(): PropertyAccessorInterface
    {
        return $this->propertyAccessor;
    }


    /**
     * @return string[]
     */
    private function convertUlidsToDbValues(
        string $resourceClass,
        string $doctrineTypeField,
        array $values
    ): array {
        $em = $this->managerRegistry->getManagerForClass($resourceClass);
        assert($em instanceof EntityManagerInterface);

        return array_map(
            function (string $iri) use ($em, $doctrineTypeField): string {
                $ulid = $this->getIdFromValue($iri);
                assert($ulid instanceof Ulid);

                try {
                    return $em
                        ->getConnection()
                        ->convertToDatabaseValue($ulid, $doctrineTypeField);
                } catch (Exception $e) {
                    throw DeveloperException::forUnexpectedException($e);
                }
            },
            $values,
        );
    }
}

And here are skeleton resource classes…

<?php

declare(strict_types=1);

namespace App\Entity\Tenant\Common;

use ApiPlatform\Core\Annotation\ApiFilter;
use ApiPlatform\Core\Annotation\ApiProperty;
use ApiPlatform\Core\Annotation\ApiResource;
use App\ApiPlatform\Bridge\Doctrine\Common\Filter\SearchUlidFieldFilter;
use App\Entity\ApiOnlyEntityInterface;
use App\Entity\Entity;
use App\Repository\Tenant\Common\CustomerOrderRepository;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Serializer\Annotation\Groups;
use Symfony\Component\Uid\Ulid;

#[ORM\Entity(repositoryClass: CustomerOrderRepository::class)]
#[ORM\Table(name: "customer_orders")]
#[ApiResource()]
class CustomerOrder extends Entity implements ApiOnlyEntityInterface
{
    #[ORM\Id]
    #[ORM\Column(name: "id", type: "ulid", unique: true)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: 'doctrine.ulid_generator')]
    #[ApiProperty(identifier: true)]
    #[Groups(['CustomerOrder.list', 'CustomerOrder.write', 'CustomerOrder.myticketorders'])]
    protected ?Ulid $id = null;

    /**
     * @var Collection<int, CustomerOrderLine>
     */
    #[ORM\OneToMany(
        targetEntity: CustomerOrderLine::class,
        mappedBy: 'order',
        cascade: ['all'],
        orphanRemoval: true,
    )]
    #[ApiProperty]
    #[Groups(['CustomerOrder.read', 'CustomerOrder.myticketorders'])]
    #[ApiFilter(SearchUlidFieldFilter::class, properties: [
        'orderLines.ticketType' => 'partial',
    ])]
    private Collection $orderLines;
}

<?php

declare(strict_types=1);

namespace App\Entity\Tenant\Common;

use ApiPlatform\Core\Annotation\ApiFilter;
use ApiPlatform\Core\Annotation\ApiResource;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\SearchFilter;
use App\ApiPlatform\Bridge\Doctrine\Common\Filter\SearchUlidFieldFilter;
use App\Entity\ApiOnlyEntityInterface;
use App\Repository\Tenant\Common\CustomerOrderLineRepository;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Serializer\Annotation\Groups;

#[ORM\Entity(repositoryClass: CustomerOrderLineRepository::class)]
#[ORM\Table(name: "customer_order_lines")]
#[ApiResource()]
class CustomerOrderLine implements ApiOnlyEntityInterface
{
    #[ORM\Id]
    #[ORM\ManyToOne(inversedBy: 'orderLines')]
    #[ApiFilter(SearchUlidFieldFilter::class)]
    #[ApiFilter(SearchFilter::class, properties: [
        'order.status' => 'exact',
    ])]
    #[Groups(['CustomerOrderLine:read'])]
    private CustomerOrder $order;

    #[ORM\Id]
    #[ORM\Column(type: 'integer', length: 4, nullable: false)]
    #[Groups(['CustomerOrder.list', 'CustomerOrder.write', 'CustomerOrder.myticketorders'])]
    private int $lineNo = 1;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(nullable: true)]
    #[ApiFilter(SearchUlidFieldFilter::class)]
    #[Groups(['CustomerOrderLine:read', 'CustomerOrder.list', 'CustomerOrder.myticketorders'])]
    private ?TicketType $ticketType = null;
}

<?php

declare(strict_types=1);

namespace App\Entity\Tenant\Common;

use ApiPlatform\Core\Annotation\ApiFilter;
use ApiPlatform\Core\Annotation\ApiProperty;
use ApiPlatform\Core\Annotation\ApiResource;
use App\ApiPlatform\Bridge\Doctrine\Common\Filter\SearchUlidFieldFilter;
use App\Attributes\ResourceMetadata;
use App\Entity\ApiOnlyEntityInterface;
use App\Entity\Entity;
use App\Repository\Tenant\Common\TicketTypeRepository;
use App\Validator\Tenant\TicketTypeCapacityQuantity;
use App\Validator\Tenant\TicketTypeLockIfSold;
use DateTimeImmutable;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;
use Symfony\Component\Clock\Clock;
use Symfony\Component\Serializer\Annotation\Groups;
use Symfony\Component\Uid\Ulid;
use Symfony\Component\Validator\Constraints as Assert;

#[ORM\Entity(repositoryClass: TicketTypeRepository::class)]
#[ORM\Table(name: "ticket_types")]
#[ApiResource]
class TicketType extends Entity implements ApiOnlyEntityInterface
{
    #[ORM\Id]
    #[ORM\Column(name: "id", type: "ulid", unique: true)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: 'doctrine.ulid_generator')]
    #[ApiProperty(identifier: true)]
    #[Groups(['TicketType:read', 'CustomerOrderLine:read', 'CustomerOrder.myticketorders'])]
    protected ?Ulid $id = null;
}