DDC-3174: Query Cache not correct working when using SQLFilter #3934

New Issue

admin · 2026-01-22T14:31:46+01:00

admin commented

2026-01-22 14:31:46 +01:00

Originally created by @doctrinebot on GitHub (Jun 17, 2014).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user benno:

We have an SQLFilter to filter on entities with a specific Trait implemented. The filter is very easy:
bq. $res = $targetTableAlias . '.agency_id = ' . $this->getCurrentAgencyId();
On our system we have the query cache enabled, this works as long the "AgencyId" doesn't change. When the ID changes, the query cache seems to return the wrong (old cache) query.

Originally created by @doctrinebot on GitHub (Jun 17, 2014). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user benno: We have an SQLFilter to filter on entities with a specific Trait implemented. The filter is very easy: bq. $res = $targetTableAlias . '.agency_id = ' . $this->getCurrentAgencyId(); On our system we have the query cache enabled, this works as long the "AgencyId" doesn't change. When the ID changes, the query cache seems to return the wrong (old cache) query.

admin added the Bug label 2026-01-22 14:31:46 +01:00

admin commented

2026-01-22 14:31:48 +01:00

@doctrinebot commented on GitHub (Jun 17, 2014):

Comment created by @ocramius:

I'm not sure if this case should be contemplated by the ORM. Filters are low-level and supposed to be stateless (services).

@doctrinebot commented on GitHub (Jun 17, 2014): Comment created by @ocramius: I'm not sure if this case should be contemplated by the ORM. Filters are low-level and supposed to be stateless (services).

admin commented

2026-01-22 14:31:49 +01:00

@doctrinebot commented on GitHub (Jun 17, 2014):

Comment created by benno:

OK, we can disable the query cache for this case. But then should at least the documentation be updated, which explicitly mentions to use filter for locales, which are also not stateless: http://doctrine-orm.readthedocs.org/en/latest/reference/filters.html#example-filter-class

Also in the query cache chapter: http://doctrine-orm.readthedocs.org/en/latest/reference/caching.html#query-cache
bq. It is highly recommended that in a production environment you cache the transformation of a DQL query to its SQL counterpart. It doesn’t make sense to do this parsing multiple times as it doesn’t change unless you alter the DQL query.

@doctrinebot commented on GitHub (Jun 17, 2014): Comment created by benno: OK, we can disable the query cache for this case. But then should at least the documentation be updated, which explicitly mentions to use filter for locales, which are also not stateless: http://doctrine-orm.readthedocs.org/en/latest/reference/filters.html#example-filter-class Also in the query cache chapter: http://doctrine-orm.readthedocs.org/en/latest/reference/caching.html#query-cache bq. It is highly recommended that in a production environment you cache the transformation of a DQL query to its SQL counterpart. It doesn’t make sense to do this parsing multiple times as it doesn’t change unless you alter the DQL query.

admin commented

2026-01-22 14:31:49 +01:00

@doctrinebot commented on GitHub (Jun 17, 2014):

Comment created by @ocramius:

[~benno] can you eventually provide a pull request?

@doctrinebot commented on GitHub (Jun 17, 2014): Comment created by @ocramius: [~benno] can you eventually provide a pull request?

admin commented

2026-01-22 14:31:50 +01:00

@doctrinebot commented on GitHub (Dec 1, 2014):

Comment created by jamesblizzardbrowserlondon:

I would just like to say that we're having exactly the same issue. I'd love some method (official or not) of having filters being taken into account in this situation.

@doctrinebot commented on GitHub (Dec 1, 2014): Comment created by jamesblizzardbrowserlondon: I would just like to say that we're having exactly the same issue. I'd love some method (official or not) of having filters being taken into account in this situation.

admin commented

2026-01-22 14:31:50 +01:00

@doctrinebot commented on GitHub (Jan 16, 2015):

Comment created by telegu:

I have the same problem when is generated QueryCacheId It consider only the name of active filters and not the value of the filter
This is the code at line 646 of class \Doctrine\ORM\Query
protected function _getQueryCacheId()
{
ksort($this->_hints);

    return md5(
        $this->getDql() . var*export($this->*hints, true) .
        ($this->*em->hasFilters() ? $this->*em->getFilters()->getHash() : '') .
        '&firstResult=' . $this->*firstResult . '&maxResult=' . $this->*maxResults .
        '&hydrationMode='.$this->*hydrationMode.'DOCTRINE_QUERY_CACHE*SALT'
    );
}

@doctrinebot commented on GitHub (Jan 16, 2015): Comment created by telegu: I have the same problem when is generated QueryCacheId It consider only the name of active filters and not the value of the filter This is the code at line 646 of class \Doctrine\ORM\Query protected function _getQueryCacheId() { ksort($this->_hints); ``` return md5( $this->getDql() . var*export($this->*hints, true) . ($this->*em->hasFilters() ? $this->*em->getFilters()->getHash() : '') . '&firstResult=' . $this->*firstResult . '&maxResult=' . $this->*maxResults . '&hydrationMode='.$this->*hydrationMode.'DOCTRINE_QUERY_CACHE*SALT' ); } ```

admin commented

2026-01-22 14:31:51 +01:00

@doctrinebot commented on GitHub (Jul 8, 2015):

Comment created by odiaseo:

I also have the same issue, there are circumstances where filters are dynamic and not stateless particularly when dealing with multi-site / multi-lingual platforms. Is there an appetite for the ORM to take this into consideration.

@doctrinebot commented on GitHub (Jul 8, 2015): Comment created by odiaseo: I also have the same issue, there are circumstances where filters are dynamic and not stateless particularly when dealing with multi-site / multi-lingual platforms. Is there an appetite for the ORM to take this into consideration.

admin commented

2026-01-22 14:31:51 +01:00

@doctrinebot commented on GitHub (Jul 17, 2015):

Comment created by bramstroker:

I have the same issue. We are using SQL filters a lot to filter entities by website and locale. It would be nice if the filter values can be taken into account as well. For now I disabled the query cache in the concerning repositories.

@doctrinebot commented on GitHub (Jul 17, 2015): Comment created by bramstroker: I have the same issue. We are using SQL filters a lot to filter entities by website and locale. It would be nice if the filter values can be taken into account as well. For now I disabled the query cache in the concerning repositories.

admin commented

2026-01-22 14:31:52 +01:00

@doctrinebot commented on GitHub (Oct 28, 2015):

Comment created by csolis:

Same issue here, we are using filters for soft deletion and it would be nice if we can use query cache.

@doctrinebot commented on GitHub (Oct 28, 2015): Comment created by csolis: Same issue here, we are using filters for soft deletion and it would be nice if we can use query cache.

admin commented

2026-01-22 14:31:52 +01:00

@doctrinebot commented on GitHub (Nov 12, 2015):

Comment created by tom.pryor:

We are currently working around this by naming the filter based on the value we apply in the filter. So in the agency_id example if we were filtering on an agency_id of 5 we'd name the filter something like 'agency_filter_5'. Would be good if Doctrine took into account the parameter values of the filters when generating the cache id though.

@doctrinebot commented on GitHub (Nov 12, 2015): Comment created by tom.pryor: We are currently working around this by naming the filter based on the value we apply in the filter. So in the agency_id example if we were filtering on an agency_id of 5 we'd name the filter something like 'agency_filter_5'. Would be good if Doctrine took into account the parameter values of the filters when generating the cache id though.

admin commented

2026-01-22 14:31:53 +01:00

@PastisD commented on GitHub (Nov 20, 2018):

Hello,

Old issue, I know, but I have the same issue. For now i just disabled the query cache.
Any news about that ? :)

@PastisD commented on GitHub (Nov 20, 2018): Hello, Old issue, I know, but I have the same issue. For now i just disabled the query cache. Any news about that ? :)

admin commented

2026-01-22 14:31:53 +01:00

@EmilePerron commented on GitHub (Oct 11, 2019):

Unless I misunderstood the issue, I believe this has been fixed.

Doctrine takes into account the parameters that are passed to the filter when the query cache is enabled. However, you have to pass those parameters via the filter's setParameter() method, otherwise it will not work. Below is a working example:

# Enable the Doctrine SQL filter for entity locales
$filter = $this->em->getFilters()->enable('localeFilter');
$filter->setParameter('locale', $request->getLocale());

Then, in your filter constraint, you can get the value with the getParameter() method, as such:

public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
    $locale = $this->getParameter('locale');

    return sprintf('%1$s.locale = %2$s OR %1$s.locale = "" OR %1$s.locale IS NULL', $targetTableAlias, $locale);
}

@EmilePerron commented on GitHub (Oct 11, 2019): Unless I misunderstood the issue, I believe this has been fixed. Doctrine takes into account the parameters that are passed to the filter when the query cache is enabled. However, you have to pass those parameters via the filter's `setParameter()` method, otherwise it will not work. Below is a working example: ```php # Enable the Doctrine SQL filter for entity locales $filter = $this->em->getFilters()->enable('localeFilter'); $filter->setParameter('locale', $request->getLocale()); ``` Then, in your filter constraint, you can get the value with the `getParameter()` method, as such: ```php public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias) { $locale = $this->getParameter('locale'); return sprintf('%1$s.locale = %2$s OR %1$s.locale = "" OR %1$s.locale IS NULL', $targetTableAlias, $locale); } ```

admin commented

2026-01-22 14:31:53 +01:00

@DamienHarper commented on GitHub (Dec 21, 2019):

@beberlei is query cache really usable with doctrine filters as of now (think also about softdeletable filter with no parameter) ?
What about doctrine 3: will filters be available and resulting queries cacheable?

@DamienHarper commented on GitHub (Dec 21, 2019): @beberlei is query cache really usable with doctrine filters as of now (think also about softdeletable filter with no parameter) ? What about doctrine 3: will filters be available and resulting queries cacheable?

admin commented

2026-01-22 14:31:54 +01:00

@SenseException commented on GitHub (Jan 8, 2020):

@DamienHarper Does the example of @EmilePerron not work?

@SenseException commented on GitHub (Jan 8, 2020): @DamienHarper Does the example of @EmilePerron not work?

admin commented

2026-01-22 14:31:55 +01:00

@DamienHarper commented on GitHub (Jan 31, 2020):

@SenseException No it still doesn't work for me.

@DamienHarper commented on GitHub (Jan 31, 2020): @SenseException No it still doesn't work for me.

admin commented

2026-01-22 14:31:55 +01:00

@beberlei commented on GitHub (Mar 1, 2020):

The fix here is to introduce a new final method getHash on SQLFilter that generates a hash based on the called class name and all the parameters, then modify FilterCollection::getHash() to use that.

Tricky bit is probably, how to handle case where parameter is an object, because serializing that would be expensive, and since its part of the query cache, must be stable across multiple requests.

@beberlei commented on GitHub (Mar 1, 2020): The fix here is to introduce a new final method `getHash` on `SQLFilter` that generates a hash based on the called class name and all the parameters, then modify `FilterCollection::getHash()` to use that. Tricky bit is probably, how to handle case where parameter is an object, because serializing that would be expensive, and since its part of the query cache, must be stable across multiple requests.

admin commented

2026-01-22 14:31:55 +01:00

@oojacoboo commented on GitHub (Sep 2, 2020):

Geez, this was a fun bug to run into. Luckily it hasn't been too dangerous, but could have been much worse, executing queries on wrong records.

Why is addFilterConstraint returning the correct SQL WHERE clause, yet Doctrine is using a different set from the query cache? I stepped through the execution for most of this and it's absolutely clear the filter is returning the correct SQL, but the final composed SQL statement includes another ID value which happens to be the one set after clearing the cache, aka from the first cache warming. Also, disabling the query cache resolves the issue.

We're just going to have to leave query cache disabled until this issue is resolved. Any other insights into this would be appreciated.

@oojacoboo commented on GitHub (Sep 2, 2020): Geez, this was a fun bug to run into. Luckily it hasn't been too dangerous, but could have been much worse, executing queries on wrong records. Why is `addFilterConstraint` returning the correct SQL WHERE clause, yet Doctrine is using a different set from the query cache? I stepped through the execution for most of this and it's absolutely clear the filter is returning the correct SQL, but the final composed SQL statement includes another ID value which happens to be the one set after clearing the cache, aka from the first cache warming. Also, disabling the query cache resolves the issue. We're just going to have to leave query cache disabled until this issue is resolved. Any other insights into this would be appreciated.

admin commented

2026-01-22 14:31:55 +01:00

@FabienPapet commented on GitHub (Sep 12, 2020):

I ran into the same issue, looks like disabling cache is the current way to fix this.

My problem was while adding a filter to add and user.company= :company depending on a user's company.

Here's how to reproduce the problem on a Symfony 5.1 with ApiPlatform v2.4

<?php

namespace App\Doctrine\Filter;

use App\Doctrine\Annotation\CompanyAware;
use App\Entity\User;
use Doctrine\Common\Annotations\Reader;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;
use Symfony\Component\Security\Core\Security;

class CompanyFilter extends SQLFilter
{
    private ?Reader $reader = null;
    private ?Security $security = null;

    public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias): string
    {
        $companyAware = $this->reader->getClassAnnotation($targetEntity->getReflectionClass(), CompanyAware::class);
        if (!$companyAware) {
            return '';
        }

        /** @var User $user */
        $user = $this->security->getUser();

        return sprintf('%s.company_id = %s', $targetTableAlias, $user->getCompany()->getId());
    }

    public function setSecurity(Security $security): void
    {
        $this->security = $security;
    }

    public function setAnnotationReader(Reader $reader): void
    {
        $this->reader = $reader;
    }
}

class CompanyFilterConfigurator
{
    private EntityManagerInterface $em;
    private Reader $reader;
    private Security $security;

    public function __construct(EntityManagerInterface $em, Security $security, Reader $reader)
    {
        $this->em = $em;
        $this->reader = $reader;
        $this->security = $security;
    }

    public function onKernelRequest(): void
    {
        /** @var CompanyFilter $filter */
        $filter = $this->em->getFilters()->enable('company_filter');
        $filter->setAnnotationReader($this->reader);
        $filter->setSecurity($this->security);
    }
}

doctrine configuration :

doctrine:
    orm:
        auto_generate_proxy_classes: false
        metadata_cache_driver:
            type: service
            id: doctrine.system_cache_provider
        query_cache_driver:
            type: service
            id: doctrine.system_cache_provider
        result_cache_driver:
            type: service
            id: doctrine.result_cache_provider

services:
    doctrine.result_cache_provider:
        class: Symfony\Component\Cache\DoctrineProvider
        public: false
        arguments:
            - '@doctrine.result_cache_pool'
    doctrine.system_cache_provider:
        class: Symfony\Component\Cache\DoctrineProvider
        public: false
        arguments:
            - '@doctrine.system_cache_pool'

framework:
    cache:
        pools:
            doctrine.result_cache_pool:
                adapter: cache.app
            doctrine.system_cache_pool:
                adapter: cache.system

@FabienPapet commented on GitHub (Sep 12, 2020): I ran into the same issue, looks like disabling cache is the *current* way to fix this. My problem was while adding a filter to add `and user.company= :company` depending on a user's company. Here's how to reproduce the problem on a Symfony 5.1 with ApiPlatform v2.4 ```php <?php namespace App\Doctrine\Filter; use App\Doctrine\Annotation\CompanyAware; use App\Entity\User; use Doctrine\Common\Annotations\Reader; use Doctrine\ORM\Mapping\ClassMetadata; use Doctrine\ORM\Query\Filter\SQLFilter; use Symfony\Component\Security\Core\Security; class CompanyFilter extends SQLFilter { private ?Reader $reader = null; private ?Security $security = null; public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias): string { $companyAware = $this->reader->getClassAnnotation($targetEntity->getReflectionClass(), CompanyAware::class); if (!$companyAware) { return ''; } /** @var User $user */ $user = $this->security->getUser(); return sprintf('%s.company_id = %s', $targetTableAlias, $user->getCompany()->getId()); } public function setSecurity(Security $security): void { $this->security = $security; } public function setAnnotationReader(Reader $reader): void { $this->reader = $reader; } } ``` ```php class CompanyFilterConfigurator { private EntityManagerInterface $em; private Reader $reader; private Security $security; public function __construct(EntityManagerInterface $em, Security $security, Reader $reader) { $this->em = $em; $this->reader = $reader; $this->security = $security; } public function onKernelRequest(): void { /** @var CompanyFilter $filter */ $filter = $this->em->getFilters()->enable('company_filter'); $filter->setAnnotationReader($this->reader); $filter->setSecurity($this->security); } } ``` doctrine configuration : ```yml doctrine: orm: auto_generate_proxy_classes: false metadata_cache_driver: type: service id: doctrine.system_cache_provider query_cache_driver: type: service id: doctrine.system_cache_provider result_cache_driver: type: service id: doctrine.result_cache_provider services: doctrine.result_cache_provider: class: Symfony\Component\Cache\DoctrineProvider public: false arguments: - '@doctrine.result_cache_pool' doctrine.system_cache_provider: class: Symfony\Component\Cache\DoctrineProvider public: false arguments: - '@doctrine.system_cache_pool' framework: cache: pools: doctrine.result_cache_pool: adapter: cache.app doctrine.system_cache_pool: adapter: cache.system ```

admin commented

2026-01-22 14:31:55 +01:00

@beberlei commented on GitHub (Sep 12, 2020):

@oojacoboo @FabienPapet see my last comment please, it explains what the problem is and it details how this can be fixed. It should be relatively simple if you want to give it a try.

@beberlei commented on GitHub (Sep 12, 2020): @oojacoboo @FabienPapet see my last comment please, it explains what the problem is and it details how this can be fixed. It should be relatively simple if you want to give it a try.

admin commented

2026-01-22 14:31:55 +01:00

@Th-julien commented on GitHub (Dec 22, 2021):

Hello,
Any news ? Can I help ?

@Th-julien commented on GitHub (Dec 22, 2021): Hello, Any news ? Can I help ?

admin commented

2026-01-22 14:31:57 +01:00

@mpdude commented on GitHub (Feb 17, 2022):

Remarks @beberlei regarding the suggestion in https://github.com/doctrine/orm/issues/3955#issuecomment-593161750:

1. Remark

Here is how the query cache ID is calculated for Query:

152c04c03d/lib/Doctrine/ORM/Query.php (L792-L803)

In that part, parameters (from AbstractQuery::$parameters) are not taken into account. To my understanding, that's for a good reason: When we deal with the same query repeatedly and only a parameter value changes, we want to re-use the same cache entry. DQL->SQL translation does not depend on the parameter value, which is bound only later on when the query is executed.

The parameters used for filters are from a different set, namely SQLFilter::$parameters (which is an independent, per-filter array). But when we consider them when computing the per-filter hash, they will effectively end up in the the query cache ID hash as well.

That is – for every different parameter value combination set for filters, different query cache entries will be used. When the filter is something like a soft delete with only two possible parameter values (on/off), that might not be a big deal. When it does something like filtering by tenantId in a multi-tenancy app, it might bloat the query cache and/or affect cache efficiency (but, at least, providing correct results).

Probably there is no easy way to fix this, since the filter returns "raw" SQL and has no way of adding parameters for the final query execution. Would require more investigation to find out how/if parameters could be returned from the filter and passed on into the Executor.

~~By the way: Why does Query::getQueryCacheId() look at all filters instead of calling getEnabledFilters() before like e. g. the SQLWalker does here?~~ The getHash() method iterates over enabled filters only. (Thanks @MalteWunsch!)

2. Remark

~~If you have a look at filter implementations like SoftDeletable, you'll see that this filter generates SQL based on data obtained at run-time through a listener. No parameters are in use in that case.~~ They added a setParameter() call to bust the query cache, but that's a rather recent fix and seems a bit like a workaround.

So, maybe the suggested SQLFilter::getHash() method should not be final, but instead be just a default implementation considering all parameters. Implementing filter subclasses may need to provide their own implementation, and this should probably be pointed out in the documentation as well. (#9522 for the code change)

@mpdude commented on GitHub (Feb 17, 2022): Remarks @beberlei regarding the suggestion in https://github.com/doctrine/orm/issues/3955#issuecomment-593161750: #### 1. Remark Here is how the query cache ID is calculated for `Query`: https://github.com/doctrine/orm/blob/152c04c03d68d39f485d367713dd69dec0f4106d/lib/Doctrine/ORM/Query.php#L792-L803 In that part, parameters (from `AbstractQuery::$parameters`) are not taken into account. To my understanding, that's for a good reason: When we deal with the same query repeatedly and only a parameter value changes, we want to re-use the same cache entry. DQL->SQL translation does not depend on the parameter value, which is bound only later on when the query is executed. The parameters used for filters are from a different set, namely `SQLFilter::$parameters` (which is an independent, per-filter array). But when we consider them when computing the per-filter hash, they will effectively end up in the the query cache ID hash as well. That is – for every different parameter value combination set for filters, different query cache entries will be used. When the filter is something like a soft delete with only two possible parameter values (on/off), that might not be a big deal. When it does something like filtering by `tenantId` in a multi-tenancy app, it might bloat the query cache and/or affect cache efficiency (but, at least, providing correct results). Probably there is no easy way to fix this, since the filter returns "raw" SQL and has no way of adding parameters for the final query execution. Would require more investigation to find out how/if parameters could be returned from the filter and passed on into the Executor. ~By the way: Why does `Query::getQueryCacheId()` look at _all_ filters instead of calling `getEnabledFilters()` before like e. g. the `SQLWalker` does [here](https://github.com/doctrine/orm/blob/152c04c03d68d39f485d367713dd69dec0f4106d/lib/Doctrine/ORM/Query/SqlWalker.php#L514)?~ The `getHash()` method [iterates over enabled filters only](https://github.com/doctrine/orm/blob/152c04c03d68d39f485d367713dd69dec0f4106d/lib/Doctrine/ORM/Query/FilterCollection.php#L193). (Thanks @MalteWunsch!) #### 2. Remark ~If you have a look at filter implementations like [SoftDeletable](https://github.com/doctrine-extensions/DoctrineExtensions/blob/2def628a9a8304c1e7f56847a0b21778a3a9ed61/src/SoftDeleteable/Filter/SoftDeleteableFilter.php), you'll see that this filter generates SQL based on data obtained at run-time through a listener. No parameters are in use in that case.~ They [added a `setParameter()` call](https://github.com/doctrine-extensions/DoctrineExtensions/pull/2112) to bust the query cache, but that's a rather recent fix and seems a bit like a workaround. So, maybe the suggested `SQLFilter::getHash()` method should not be `final`, but instead be just a default implementation considering all parameters. Implementing filter subclasses may need to provide their own implementation, and this should probably be pointed out in the documentation as well. (#9522 for the code change)

admin commented

2026-01-22 14:31:57 +01:00

@mpdude commented on GitHub (Feb 17, 2022):

@MalteWunsch just pointed out to me that FilterCollection::getHash() will effectively cast filters to strings when computing the hash:

152c04c03d/lib/Doctrine/ORM/Query/FilterCollection.php (L193-L195)

Since SQLFilter implements __toString by serializing its ::$parameters...

152c04c03d/lib/Doctrine/ORM/Query/Filter/SQLFilter.php (L178-L181)

... the solution described by @beberlei should effectively be in place for a long time already?

@mpdude commented on GitHub (Feb 17, 2022): @MalteWunsch just pointed out to me that `FilterCollection::getHash()` will effectively cast filters to strings when computing the hash: https://github.com/doctrine/orm/blob/152c04c03d68d39f485d367713dd69dec0f4106d/lib/Doctrine/ORM/Query/FilterCollection.php#L193-L195 Since `SQLFilter` implements `__toString` by serializing its `::$parameters`... https://github.com/doctrine/orm/blob/152c04c03d68d39f485d367713dd69dec0f4106d/lib/Doctrine/ORM/Query/Filter/SQLFilter.php#L178-L181 ... the solution described by @beberlei should effectively be in place for a long time already?

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3934