I'm currently struggling with a Symfony EventSubscriber enabling a Doctrine Filter: The Filter query is not applied

I want to trigger a filter conditionally, and I successfully seem to do so. In the following subscriber I activate a filter, and when I check if the filter is active with $this->entityManager->getFilters()->getEnabledFilters() I get confirmed that the filter is now indeed enabled and active. The parameter I set is also successfully set.

But then the issue arises. When I look in the profiler, the filter is not applied at all. The query is not added the where clause as stated in the filter. It is like the filter isn't enabled, but it is shown as if it is.

Important to note: We do use API-Platform. The Filter is applied on a get or getCollection() operation on Entity\Emote.php. At first I thought, maybe the problem is with priorities. But I can't seem to get that verified.

EventSubscriber\EmoteTierFilterSubscriber.php

<?php

namespace App\EventSubscriber;

use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\SecurityBundle\Security;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;
use Symfony\Component\HttpKernel\Event\RequestEvent;
use Symfony\Component\HttpKernel\KernelEvents;

class EmoteTierFilterSubscriber implements EventSubscriberInterface
{
    public function __construct(
        private EntityManagerInterface $entityManager,
        private Security $security,
    ) {
    }

    public static function getSubscribedEvents()
    {
        return [
            KernelEvents::REQUEST => ['activateFilter', -10],
        ];
    }


    public function activateFilter(RequestEvent $event): void
    {
        if (!$event->isMainRequest()) {
            return;
        }

       $user = $this->security->getUser();
       $filter = $this->entityManager->getFilters()->enable('emote_tier_filter');

        if ($user && $tier = $user->getChannelFollowTier()) {
            $filter->setParameter('tier', $tier);
        } else {
            $filter->setParameter('tier', 'NULL');
        }
    }
}

Doctrine\Filter\EmoteTierFilter.php

<?php

namespace App\Doctrine\Filter;

use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;

class EmoteTierFilter extends SQLFilter
{
    public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias): string
    {
        if ($targetEntity->getReflectionClass()->getName() !== 'App\Entity\Emote') {
            return '';
        }

        try {
            $tier = $this->getParameter('tier');
        } catch (\InvalidArgumentException $e) {
            return '';
        }

        if ($tier === 'NULL') {
            return sprintf('(%s.tier IS NULL OR %s.tier = \'0000\')',
                $targetTableAlias,
                $targetTableAlias
            );
        }

        // Cast both sides to integer for comparison
        return sprintf('(%s.tier IS NULL OR %s.tier = \'0000\' OR CAST(%s.tier AS INTEGER) <= %d)',
            $targetTableAlias,
            $targetTableAlias,
            $targetTableAlias,
            (int)$tier
        );
    }
}

doctrine.yaml

doctrine:
  dbal:
    url: "%env(resolve:DATABASE_URL)%"

    # IMPORTANT: You MUST configure your server version,
    # either here or in the DATABASE_URL env var (see .env file)
    #server_version: '15'

    profiling_collect_backtrace: "%kernel.debug%"
  orm:
    filters:
      emote_tier_filter:
        class: App\Doctrine\Filter\EmoteTierFilter
        enabled: false

..

Entity\Emote.php

namespace App\Entity;

use ApiPlatform\Metadata\ApiResource;
use ApiPlatform\Doctrine\Orm\Filter\SearchFilter;
use ApiPlatform\Metadata\ApiFilter;
use ApiPlatform\Metadata\Get;
use ApiPlatform\Metadata\GetCollection;
use ApiPlatform\Metadata\Patch;
use ApiPlatform\Metadata\Post;
use App\Repository\EmoteRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: EmoteRepository::class)]
#[ORM\HasLifecycleCallbacks]
#[ApiResource(
    operations: [
        new GetCollection(security: null),
        new Get(security: null),
        new Post(security: "is_granted('ROLE_ADMIN')"),
        new Patch(security: "is_granted('ROLE_ADMIN')"),
    ],
    paginationEnabled: false
)]
#[ApiFilter(SearchFilter::class, properties: ['category' => 'exact', 'name' => 'partial', 'tier' => 'exact'])]
class Emote
{

...

Query in Profiler

SELECT e0_.id AS id_0, e0_.vendor_id AS vendor_id_1, e0_.name AS name_2, e0_.images AS images_3, e0_.format AS format_4, e0_.scale AS scale_5, e0_.themes AS themes_6, e0_.added_at AS added_at_7, e0_.category AS category_8, e0_.tier AS tier_9 FROM emote e0_ ORDER BY e0_.id ASC

Parameters:
[]

I'm not sure where to look anymore.

I'm currently struggling with a Symfony EventSubscriber enabling a Doctrine Filter: The Filter query is not applied

I want to trigger a filter conditionally, and I successfully seem to do so. In the following subscriber I activate a filter, and when I check if the filter is active with $this->entityManager->getFilters()->getEnabledFilters() I get confirmed that the filter is now indeed enabled and active. The parameter I set is also successfully set.

But then the issue arises. When I look in the profiler, the filter is not applied at all. The query is not added the where clause as stated in the filter. It is like the filter isn't enabled, but it is shown as if it is.

Important to note: We do use API-Platform. The Filter is applied on a get or getCollection() operation on Entity\Emote.php. At first I thought, maybe the problem is with priorities. But I can't seem to get that verified.

EventSubscriber\EmoteTierFilterSubscriber.php

<?php

namespace App\EventSubscriber;

use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\SecurityBundle\Security;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;
use Symfony\Component\HttpKernel\Event\RequestEvent;
use Symfony\Component\HttpKernel\KernelEvents;

class EmoteTierFilterSubscriber implements EventSubscriberInterface
{
    public function __construct(
        private EntityManagerInterface $entityManager,
        private Security $security,
    ) {
    }

    public static function getSubscribedEvents()
    {
        return [
            KernelEvents::REQUEST => ['activateFilter', -10],
        ];
    }


    public function activateFilter(RequestEvent $event): void
    {
        if (!$event->isMainRequest()) {
            return;
        }

       $user = $this->security->getUser();
       $filter = $this->entityManager->getFilters()->enable('emote_tier_filter');

        if ($user && $tier = $user->getChannelFollowTier()) {
            $filter->setParameter('tier', $tier);
        } else {
            $filter->setParameter('tier', 'NULL');
        }
    }
}

Doctrine\Filter\EmoteTierFilter.php

<?php

namespace App\Doctrine\Filter;

use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;

class EmoteTierFilter extends SQLFilter
{
    public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias): string
    {
        if ($targetEntity->getReflectionClass()->getName() !== 'App\Entity\Emote') {
            return '';
        }

        try {
            $tier = $this->getParameter('tier');
        } catch (\InvalidArgumentException $e) {
            return '';
        }

        if ($tier === 'NULL') {
            return sprintf('(%s.tier IS NULL OR %s.tier = \'0000\')',
                $targetTableAlias,
                $targetTableAlias
            );
        }

        // Cast both sides to integer for comparison
        return sprintf('(%s.tier IS NULL OR %s.tier = \'0000\' OR CAST(%s.tier AS INTEGER) <= %d)',
            $targetTableAlias,
            $targetTableAlias,
            $targetTableAlias,
            (int)$tier
        );
    }
}

doctrine.yaml

doctrine:
  dbal:
    url: "%env(resolve:DATABASE_URL)%"

    # IMPORTANT: You MUST configure your server version,
    # either here or in the DATABASE_URL env var (see .env file)
    #server_version: '15'

    profiling_collect_backtrace: "%kernel.debug%"
  orm:
    filters:
      emote_tier_filter:
        class: App\Doctrine\Filter\EmoteTierFilter
        enabled: false

..

Entity\Emote.php

namespace App\Entity;

use ApiPlatform\Metadata\ApiResource;
use ApiPlatform\Doctrine\Orm\Filter\SearchFilter;
use ApiPlatform\Metadata\ApiFilter;
use ApiPlatform\Metadata\Get;
use ApiPlatform\Metadata\GetCollection;
use ApiPlatform\Metadata\Patch;
use ApiPlatform\Metadata\Post;
use App\Repository\EmoteRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: EmoteRepository::class)]
#[ORM\HasLifecycleCallbacks]
#[ApiResource(
    operations: [
        new GetCollection(security: null),
        new Get(security: null),
        new Post(security: "is_granted('ROLE_ADMIN')"),
        new Patch(security: "is_granted('ROLE_ADMIN')"),
    ],
    paginationEnabled: false
)]
#[ApiFilter(SearchFilter::class, properties: ['category' => 'exact', 'name' => 'partial', 'tier' => 'exact'])]
class Emote
{

...

Query in Profiler

SELECT e0_.id AS id_0, e0_.vendor_id AS vendor_id_1, e0_.name AS name_2, e0_.images AS images_3, e0_.format AS format_4, e0_.scale AS scale_5, e0_.themes AS themes_6, e0_.added_at AS added_at_7, e0_.category AS category_8, e0_.tier AS tier_9 FROM emote e0_ ORDER BY e0_.id ASC

Parameters:
[]

I'm not sure where to look anymore.

• php
• symfony
• doctrine-orm
• doctrine

1 Answer 1

If you are using API Platform, I think the appropriate way to filter queries would be using API Platform's extensions.

Here is a sample code from their documentation.

<?php
// api/src/Doctrine/CurrentUserExtension.php

namespace App\Doctrine;

use ApiPlatform\Doctrine\Orm\Extension\QueryCollectionExtensionInterface;
use ApiPlatform\Doctrine\Orm\Extension\QueryItemExtensionInterface;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Metadata\Operation;
use App\Entity\Offer;
use Doctrine\ORM\QueryBuilder;
use Symfony\Bundle\SecurityBundle\Security;

final readonly class CurrentUserExtension implements QueryCollectionExtensionInterface, QueryItemExtensionInterface
{

    public function __construct(
        private Security $security,
    )
    {
    }

    public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, Operation $operation = null, array $context = []): void
    {
        $this->addWhere($queryBuilder, $resourceClass);
    }

    public function applyToItem(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, array $identifiers, Operation $operation = null, array $context = []): void
    {
        $this->addWhere($queryBuilder, $resourceClass);
    }

    private function addWhere(QueryBuilder $queryBuilder, string $resourceClass): void
    {
        if (Offer::class !== $resourceClass || $this->security->isGranted('ROLE_ADMIN') || null === $user = $this->security->getUser()) {
            return;
        }

        $rootAlias = $queryBuilder->getRootAliases()[0];
        $queryBuilder->andWhere(sprintf('%s.user = :current_user', $rootAlias));
        $queryBuilder->setParameter('current_user', $user->getId());
    }
}