Doctrine SQLFilter with QueryBuilder in Symfony not working

user_3383149 0
Viewed 341

I'm trying to create an SQLFilter for a query in my Symfony app.

The issue is that the filter is not applied on the query (and not called), even though is it enabled correctly (see below).

The repository is not linked to an entity, because the database is external to my app, but it still has access to the data.

Am I missing something ?

Here's the filter:

<?php

namespace App\SQL\Filter;

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

class UserRoleFilter extends SQLFilter
{
    public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
    {
        return 'c.roleId = 1';
    }
}

I registered it in config/packages/doctrine.yaml:

doctrine:
    filters:
            user_role: App\SQL\Filter\UserRoleFilter

The controller:

<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Doctrine\Persistence\ManagerRegistry;
use App\Repository\CustomerRepository;

class CustomerController extends AbstractController
{
    public function myAction(Request $request, ManagerRegistry $doctrine, CustomerRepository $customerRepository)
    {
        $doctrine->getManager()->getFilters()->enable('user_role');
        $customers = $customerRepository->findAll();
    }
}

The repository:

<?php

namespace App\Repository;

use Doctrine\DBAL\Connection;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\Persistence\ObjectManager;

class CustomerRepository
{

    protected Connection $conn;
    protected ObjectManager $em;

    public function __construct(ManagerRegistry $doctrine)
    {
        $this->em = $doctrine->getManager();
        $this->conn = $this->em->getConnection();
    }

    public function findAll(): array
    {
        dump($this->em->getFilters()->isEnabled('user_role')); // returns true
        
        return $this->conn->createQueryBuilder()
            ->select('c.*')
            ->from('customer', 'c')
            ->executeQuery()
            ->fetchAllAssociative();
    }
}
symfony5 symfony doctrine php
1 Answers

From looking at the source for Doctrine/DBAL, it doesn't look like the filter would ever be applied to the query you are executing.

Within your repository class you are creating an instance of Doctrine\DBAL\Query\QueryBuilder which only holds a reference to Doctrine\DBAL\Connection.

Then the select data is set to its private parameter $sqlParts.

When executeQuery() is called is concatenates the content of sqlParts into a string with no mention or reference to any filter objects nor their constraints. This can be seen on line 308 of the QueryBuilder class.

You can also see how the select query is concatenated on line 1320 of QueryBuilder.

The only way I can see to add it easily would be to add it directly to a where clause, e.g.

public function findAll(): array
{
    return $this->conn->createQueryBuilder()
            ->select('c.*')
            ->from('customer', 'c')
            ->where("c.roleId = 1") // Or pull it from the filter object in some way
            ->executeQuery()
            ->fetchAllAssociative();
}

If you want to see where the filter constraints are added to the queries you can find that data in the ORM package from Doctrine, however these are all linked to entities and table aliases.

  1. SqlWalker::generateFilterConditionSQL()

  2. BasicEntityPersistor::generateFilterConditionSQL()

  3. ManyToManyPersistor::generateFilterConditionSQL()

user_137459690
Related