Doctrine SQLFilter with QueryBuilder in Symfony not working

Question

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();
    }
}

What version of Symfony are you using? (Oh, just noticed your `symfony5` tag.) Have you tried making your repo extend `ServiceEntityRepository` and using `$this->createQueryBuilder('c')`? — Quentin, Mar 29 '22 at 12:34
Minor improvement would be to use `return sprintf('%s.roleId = 1', $targetTableAlias);` in `addFilterConstraint`. — Quentin, Mar 29 '22 at 12:51
I can't extend `ServiceEntityRepository` because customer is not an actual entity, just a table in the database. The code here is an example, my constraint will not look like this :-) — skirato, Mar 29 '22 at 13:22
Good catch. Have you tried the same filter on an "actual" entity? — Quentin, Mar 29 '22 at 14:13
Yes, it does work on an actual entity. I'm guessing the problem is that the table is not linked to an entity. — skirato, Mar 29 '22 at 19:02

score 0 · Answer 1 · answered Apr 01 '22 at 22:13

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.

QueryBuilder::executeQuery()

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

QueryBuilder::getSQLForSelect()

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.

Thanks for your answer. So does this mean that filters only apply to Entities and that there is no way to use them without ? `$this->em->getFilters()->getEnabledFilters()` in BasicEntityPersistor::generateFilterConditionSQL() should return my filter, no ? — skirato, Apr 02 '22 at 08:29
That looks to be the case, when you are hydrating a query using entities it applies the filters in a loop during the `getSelectSQL` method of the EntityPersisitor. — Ash Marchington, Apr 02 '22 at 09:23
BasicEntityPersistor would return your filter but aren't using that to run your query. You are using an instance of QueryBuilder — Ash Marchington, Apr 02 '22 at 09:30

Doctrine SQLFilter with QueryBuilder in Symfony not working

1 Answers1