DDC-3708: Doctrine SQL filters and lazy loading causing EntityNotFoundException #4553

New Issue

2026-01-22T14:44:31+01:00

admin commented

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

Originally created by @doctrinebot on GitHub (Apr 22, 2015).

Originally assigned to: @asm89 on GitHub.

Jira issue originally created by user pavle.predic:

When using a global SQL filter, lazy loading ManyToOne relations may lead to an EntityNotFoundException. You will always get a proxy object for the relation, but as soon as you try to access any property, doctrine will attempt to load the entity from DB, get zero results because the global filter has been applied (assuming that the filter affects the entity in question) and throw an EntityNotFoundException.

What is the recommended way of dealing with this issue globally? The use case is a CMS with a soft-delete feature. A global sql filter filters out entities with a deleted flag. One solution would be to write a custom ProxyFactory that doesn't throw the EntityNotFoundException or to write a custom ProxyGenerator that puts the initializer call in a try/catch block and returns null if an EntityNotFoundException is thrown.

This issue can be resolved by using "fetch EAGER" on ManyToOne relations, but this can lead to performance issues due to the large number of DB queries. How can this problem be solved while still using the lazy loading mechanism?

Originally created by @doctrinebot on GitHub (Apr 22, 2015). Originally assigned to: @asm89 on GitHub. Jira issue originally created by user pavle.predic: When using a global SQL filter, lazy loading ManyToOne relations may lead to an EntityNotFoundException. You will always get a proxy object for the relation, but as soon as you try to access any property, doctrine will attempt to load the entity from DB, get zero results because the global filter has been applied (assuming that the filter affects the entity in question) and throw an EntityNotFoundException. What is the recommended way of dealing with this issue globally? The use case is a CMS with a soft-delete feature. A global sql filter filters out entities with a deleted flag. One solution would be to write a custom ProxyFactory that doesn't throw the EntityNotFoundException or to write a custom ProxyGenerator that puts the initializer call in a try/catch block and returns null if an EntityNotFoundException is thrown. This issue can be resolved by using "fetch EAGER" on ManyToOne relations, but this can lead to performance issues due to the large number of DB queries. How can this problem be solved while still using the lazy loading mechanism?

admin added the Can't Fix label 2026-01-22 14:44:31 +01:00

admin closed this issue

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

admin commented

2026-01-22 14:44:32 +01:00

@doctrinebot commented on GitHub (Apr 22, 2015):

Comment created by @ocramius:

This sort of issue simply occurs when enabling SQL filters after any loading has already happened.

We can't fix this, you are supposed to call Doctrine\ORM\EntityManagerInterface#clear() right after enabling or disabling any filter.

@doctrinebot commented on GitHub (Apr 22, 2015): Comment created by @ocramius: This sort of issue simply occurs when enabling SQL filters after any loading has already happened. We can't fix this, you are supposed to call `Doctrine\ORM\EntityManagerInterface#clear()` right after enabling or disabling any filter.

admin commented

2026-01-22 14:44:32 +01:00

@doctrinebot commented on GitHub (Apr 22, 2015):

Comment created by pavle.predic:

Filter is enabled from the start. I'm not sure if I managed to explain the issue properly, so let's use an example. Say we have the following entities:

Person (id, name, image_id, deleted)
Image (id, file_name, deleted)

Image is defined as a ManyToOne relation in Person.

Now let's say we have a global SQL filter that filters out all entities where deleted = 1.
Say we have the following data:
Person table:

1, "Jimi", 1, 0

Image table:

1, "jimi.jpg", 1

Now I load the person:

$person = $personRepository->find(1);

If I now access the related Image entity, I will get the instance of Proxy class:

$image = $person->getImage(); //no errors here, doctrine assumes that the relation exists, but doesn't attempt to load it from DB

When I try to get a specific Image attribute, I get an EntityNotFoundException:

$imagePath = $image->getFileName(); //throws an EntityNotFoundException

Obviously, I could use a try/catch block here, but I need a global solution. Most of the time, I'm accessing such relations in a twig template, which is not the best place to deal with exceptions.

@doctrinebot commented on GitHub (Apr 22, 2015): Comment created by pavle.predic: Filter is enabled from the start. I'm not sure if I managed to explain the issue properly, so let's use an example. Say we have the following entities: ``` Person (id, name, image_id, deleted) Image (id, file_name, deleted) ``` Image is defined as a `ManyToOne` relation in Person. Now let's say we have a global SQL filter that filters out all entities where deleted = 1. Say we have the following data: Person table: ``` 1, "Jimi", 1, 0 ``` Image table: ``` 1, "jimi.jpg", 1 ``` Now I load the person: ``` $person = $personRepository->find(1); ``` If I now access the related Image entity, I will get the instance of Proxy class: ``` $image = $person->getImage(); //no errors here, doctrine assumes that the relation exists, but doesn't attempt to load it from DB ``` When I try to get a specific Image attribute, I get an `EntityNotFoundException`: ``` $imagePath = $image->getFileName(); //throws an EntityNotFoundException ``` Obviously, I could use a try/catch block here, but I need a global solution. Most of the time, I'm accessing such relations in a twig template, which is not the best place to deal with exceptions.

admin commented

2026-01-22 14:44:33 +01:00

@doctrinebot commented on GitHub (Apr 22, 2015):

Comment created by @ocramius:

Reopened: I obviously misunderstood the problem.

I don't think there is a doctrine-side solution to it.

@doctrinebot commented on GitHub (Apr 22, 2015): Comment created by @ocramius: Reopened: I obviously misunderstood the problem. I don't think there is a doctrine-side solution to it.

admin commented

2026-01-22 14:44:33 +01:00

@doctrinebot commented on GitHub (Apr 22, 2015):

Comment created by pavle.predic:

So definitely not possible to extend ProxyFactory or ProxyGenerator?

Or can this be made into a feature request where a specific config option would force the Proxy to return null instead of throwing an EntityNotFoundException?

@doctrinebot commented on GitHub (Apr 22, 2015): Comment created by pavle.predic: So definitely not possible to extend ProxyFactory or ProxyGenerator? Or can this be made into a feature request where a specific config option would force the Proxy to return null instead of throwing an EntityNotFoundException?

admin commented

2026-01-22 14:44:34 +01:00

@doctrinebot commented on GitHub (Apr 22, 2015):

Comment created by @ocramius:

Since it is an SQL-level filter, it would have to act at SQL level.

One possible solution is to always do joins on filtered associations, and then fetch either NULL or the identifier for those associations.

@doctrinebot commented on GitHub (Apr 22, 2015): Comment created by @ocramius: Since it is an SQL-level filter, it would have to act at SQL level. One possible solution is to always do joins on filtered associations, and then fetch either `NULL` or the identifier for those associations.

admin commented

2026-01-22 14:44:34 +01:00

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

Comment created by althaus:

I'm in the same boat. We've got soft-deletable users and fetching the associations results in "empty" user proxy entities.

For example: We track how creates/deletes whom and display this in Twig templates with the users's **toString() method. The soft-deleted proxy just explodes in the Twig template. I could disable the DoctrineFilter, but this would totally break its benefits. I'm currenlty running with EAGER loading on the association, but so I have to check "isDeleted" manually.

Is there a way to tell Doctrine to replace the proxy with NULL, if the filtered result doesn't return a row?

@doctrinebot commented on GitHub (Oct 12, 2015): Comment created by althaus: I'm in the same boat. We've got soft-deletable users and fetching the associations results in "empty" user proxy entities. For example: We track how creates/deletes whom and display this in Twig templates with the users's **toString() method. The soft-deleted proxy just explodes in the Twig template. I could disable the DoctrineFilter, but this would totally break its benefits. I'm currenlty running with EAGER loading on the association, but so I have to check "isDeleted" manually. Is there a way to tell Doctrine to replace the proxy with NULL, if the filtered result doesn't return a row?

admin commented

2026-01-22 14:44:34 +01:00

@althaus commented on GitHub (Jan 11, 2016):

One possible solution is to always do joins on filtered associations, and then fetch either NULL or the identifier for those associations.

@Ocramius Where should we do that? I started to use JOINs every now and than, but that's leading to other issues. It's simply making the filter more or less a waste of code. :(

@althaus commented on GitHub (Jan 11, 2016): > One possible solution is to always do joins on filtered associations, and then fetch either NULL or the identifier for those associations. @Ocramius Where should we do that? I started to use `JOIN`s every now and than, but that's leading to other issues. It's simply making the filter more or less a waste of code. :(

admin commented

2026-01-22 14:44:35 +01:00

@Ocramius commented on GitHub (Jan 11, 2016):

SQL filters (and soft deletes) are most probably a waste of code in most scenarios anyway.
Extending the proxy factory is a no-go, but the problem is much wider than what it looks like from in this tiny issue.

Basically, the ORM would have to completely change how hydration works, at the risk of breaking persistence later on.

Here's an example:

SELECT f FROM Foo f

Where f.bar is a Bar *-to-one association. Say following record is fetched (raw):

[
    'id' => 123,
    'name' => 'yadda',
    'bar_id' => 456,
]

Assuming that Bar with id 456 is soft-deleted, we'd obviously have a failure during lazy-loading.

If we instead fix DQL so that the results are fetched with filtered values, we get:

[
    'id' => 123,
    'name' => 'yadda',
    'bar_id' => null,
]

While this works for read APIs, persisting an object with this data hydrated into it will indeed set bar_id to null (breaks the reason why you used soft-deletes in first place) during the next flush operation.

Therefore the reference should be kept at object level (proxy), and the exceptions in the proxy loading are actually the most accurate and near-correct solution right now.

I'm going to close this as Can't Fix, as I don't think there is a reasonable escape from soft deletes.

@Ocramius commented on GitHub (Jan 11, 2016): SQL filters (and soft deletes) are most probably a waste of code in most scenarios anyway. Extending the proxy factory is a no-go, but the problem is much wider than what it looks like from in this tiny issue. Basically, the ORM would have to completely change how hydration works, at the risk of breaking persistence later on. Here's an example: ``` sql SELECT f FROM Foo f ``` Where `f.bar` is a `Bar` `*-to-one` association. Say following record is fetched (raw): ``` php [ 'id' => 123, 'name' => 'yadda', 'bar_id' => 456, ] ``` Assuming that `Bar` with `id` `456` is soft-deleted, we'd obviously have a failure during lazy-loading. If we instead fix DQL so that the results are fetched with filtered values, we get: ``` php [ 'id' => 123, 'name' => 'yadda', 'bar_id' => null, ] ``` While this works for read APIs, persisting an object with this data hydrated into it will indeed set `bar_id` to `null` (breaks the reason why you used soft-deletes in first place) during the next flush operation. Therefore the reference should be kept at object level (proxy), and the exceptions in the proxy loading are actually the most accurate and near-correct solution right now. I'm going to close this as `Can't Fix`, as I don't think there is a reasonable escape from soft deletes.

admin commented

2026-01-22 14:44:35 +01:00

@althaus commented on GitHub (Jan 15, 2016):

So you're basically telling us "Don't use soft-deletable"? Which worked for 10+ years in our legacy application. We've got a complex user and group management on our customers' servers bound to a shitload of linked data sets and these need a system which doesn't delete users/groups instantly as undoing would require massive handwork to regain the data. :[

@althaus commented on GitHub (Jan 15, 2016): So you're basically telling us "Don't use soft-deletable"? Which worked for 10+ years in our legacy application. We've got a complex user and group management on our customers' servers bound to a shitload of linked data sets and these _need_ a system which doesn't delete users/groups instantly as undoing would require massive handwork to regain the data. :[

admin commented

2026-01-22 14:44:35 +01:00

@Ocramius commented on GitHub (Jan 15, 2016):

@althaus I am not telling you to undo a legacy app: if it produces value, then it's doing what it should do (in first place).

That said, there is no resolution path for this issue, as I've explained above.

The simplest solution is to avoid going down this road, as it leads to broken entity graphs (as I outlined above)

@Ocramius commented on GitHub (Jan 15, 2016): @althaus I am not telling you to undo a legacy app: if it produces value, then it's doing what it should do (in first place). That said, there is no resolution path for this issue, as I've explained above. The simplest solution is to avoid going down this road, as it leads to broken entity graphs (as I outlined above)

admin commented

2026-01-22 14:44:36 +01:00

@althaus commented on GitHub (Jan 18, 2016):

It's not about undoing... we're migrating it to Symfony and "SoftDeletables" seemed like a good choice in the first place.

OK... so I'll take a look at the ProxyFactory and hopefully this can help us.

@althaus commented on GitHub (Jan 18, 2016): It's not about undoing... we're migrating it to Symfony and "SoftDeletables" seemed like a good choice in the first place. OK... so I'll take a look at the ProxyFactory and hopefully this can help us.

admin commented

2026-01-22 14:44:36 +01:00

@dave-newson commented on GitHub (Feb 15, 2016):

I've run into a similar issue; We're using an SQL Filter to "hide" some entities from all query scopes, and this is helping us avoid the need to augment hundreds of DQL statements with extra criteria.
This is similar to the Soft Delete scenario; we don't want our queries to show these items, but rather we do want them discoverable by the Proxies if they happen to still be in use somewhere.

@Ocramius I see your point that the associations can't magically turn into nulls, and that's perfectly OK by me, but can we give SQLFilter enough context that I can prevent it torpedoing Proxy loading?

I think a simple solution to this would for \Doctrine\ORM\Persisters\BasicEntityPersister::getSelectSQL to provide generateFilterConditionSQL with the $criteria. This can then be passed to the SQLFilter::addFilterConstraint implementation, and I can have a clause to check if the only criteria key is ID and then bail out.

That's a very specific fix to my specific problem, so another alternative:
Use a high-level event with a listener that can detect the Proxy load, and then disengage the specific SQLFilter for the duration of the Proxy load.
Unfortunately I can't think of/find anything with that capability. @Ocramius can you offer any suggestions?

@dave-newson commented on GitHub (Feb 15, 2016): I've run into a similar issue; We're using an SQL Filter to "hide" some entities from all query scopes, and this is helping us avoid the need to augment hundreds of DQL statements with extra criteria. This is similar to the Soft Delete scenario; we don't want our queries to show these items, but rather we _do_ want them discoverable by the Proxies if they happen to still be in use somewhere. @Ocramius I see your point that the associations can't magically turn into nulls, and that's perfectly OK by me, but can we give `SQLFilter` enough context that I can prevent it torpedoing Proxy loading? I think a simple solution to this would for `\Doctrine\ORM\Persisters\BasicEntityPersister::getSelectSQL` to provide `generateFilterConditionSQL` with the `$criteria`. This can then be passed to the `SQLFilter::addFilterConstraint` implementation, and I can have a clause to check if the only criteria key is ID and then bail out. That's a very specific fix to my specific problem, so another alternative: Use a high-level event with a listener that can detect the Proxy load, and then disengage the specific SQLFilter for the duration of the Proxy load. Unfortunately I can't think of/find anything with that capability. @Ocramius can you offer any suggestions?

admin commented

2026-01-22 14:44:37 +01:00

@Ocramius commented on GitHub (Feb 15, 2016):

and I can have a clause to check if the only criteria key is ID and then bail out.

Doesn't this provide you broken SQLFilter functionality then?

@Ocramius commented on GitHub (Feb 15, 2016): > and I can have a clause to check if the only criteria key is ID and then bail out. Doesn't this provide you broken `SQLFilter` functionality then?

admin commented

2026-01-22 14:44:37 +01:00

@Ocramius commented on GitHub (Feb 15, 2016):

Use a high-level event with a listener that can detect the Proxy load, and then disengage the specific SQLFilter for the duration of the Proxy load.

The data is not supposed to be visible from the object perspective, as the relation is supposed to be filter. This seems broken as well to me.

An alternative solution could be to throw specific exceptions when the filters are enabled, and re-querying for the same data when that happens (to allow recovery from userland, if you really know what you want there).

I'm re-assigning to @asm89, since he originally implemented SQL filters, and may have better ideas about a possible resolution path.

@Ocramius commented on GitHub (Feb 15, 2016): > Use a high-level event with a listener that can detect the Proxy load, and then disengage the specific SQLFilter for the duration of the Proxy load. The data is not supposed to be visible from the object perspective, as the relation is supposed to be filter. This seems broken as well to me. An alternative solution could be to throw specific exceptions when the filters are enabled, and re-querying for the same data when that happens (to allow recovery from userland, if you really know what you want there). I'm re-assigning to @asm89, since he originally implemented SQL filters, and may have better ideas about a possible resolution path.

admin commented

2026-01-22 14:44:38 +01:00

@dave-newson commented on GitHub (Feb 15, 2016):

@Ocramius thanks for bumping this along. To answer your questions:

I don't think this breaks the SQLFilter functionality in our particular use case. We'd still like to use the SQLFilter on broad queries, but we don't want it to break the Proxy loading of specific entities.
If there was an equivalent DQLFilter, we'd probably use that instead.

Sticking with the soft-delete example:
We load a User index page which shows all users in the system. Our SQLFilter detects the User entity and applies the additional SQL clause to exclude deleted users from the result set.

Each User has a Manager - this is just a one-to-one User relation which lazy-loads.
As we list all the Users we try to show their associated Manager, even if the manager is soft-deleted.

Joe has a valid db-backed foreign-key relation to his Manager, however the Manager has been soft-deleted. When the Proxy load runs, the SQLFilter is also added to the query, and the exception is thrown.

This unfortunately means exceptions get thrown by whatever code tries to load the lazy association. It's not practical for me to try/catch wrap every line of code that might access a lazy association, which is what makes me feel this issues lies on Doctrine's side.

On the other hand, if the Proxy had been loaded regardless of the SQLFilter, I could run subsequent checks on the entity and act accordingly.

Similarly, I don't want Joe's association to be turned into a Null, because in the DB the association is valid and I don't want that linkage to be broken. If I did want that, I'd say the onus is on me to break that association at time of soft-deletion.

I realise my scope is very narrow, so if there's a better way to implement your typical soft-delete, I'm all ears (again, we're not using this technique for soft-delete, but it's a similar-enough example).

I've just checked a bit deeper in the code and SqlWalker doesn't have scope over the Criteria, so perhaps passing that to SQLFilter isn't a very smart approach. In fact I think it's only the BasicEntityPersister that has the criteria, so really an Event or flag to tell SQLFilter not to run during Proxy load is the only way I can think to solve this.

Thinking about SQLFilter a bit deeper; wouldn't you end up with the EntityNotFound exception in any case where you try to fetch a filtered entity from the owning side in a relationship. That would mean it's only "safe" to use lazy-loading from the inherited side (many-to-one or many-to-many) on an entity that has an applied filter. That doesn't seem like a desirable aspect of this feature, right?

Sorry for the wall of text.

@dave-newson commented on GitHub (Feb 15, 2016): @Ocramius thanks for bumping this along. To answer your questions: I don't think this breaks the SQLFilter functionality in our particular use case. We'd still like to use the SQLFilter on broad queries, but we don't want it to break the Proxy loading of specific entities. If there was an equivalent DQLFilter, we'd probably use that instead. Sticking with the _soft-delete example:_ We load a User index page which shows all users in the system. Our SQLFilter detects the User entity and applies the additional SQL clause to exclude deleted users from the result set. Each User has a Manager - this is just a one-to-one User relation which lazy-loads. As we list all the Users we try to show their associated Manager, _even if the manager is soft-deleted_. Joe has a valid db-backed foreign-key relation to his Manager, however the Manager has been soft-deleted. When the Proxy load runs, the SQLFilter is also added to the query, and the exception is thrown. This unfortunately means exceptions get thrown by whatever code tries to load the lazy association. It's not practical for me to try/catch wrap every line of code that might access a lazy association, which is what makes me feel this issues lies on Doctrine's side. On the other hand, if the Proxy had been loaded regardless of the SQLFilter, I could run subsequent checks on the entity and act accordingly. Similarly, I don't want Joe's association to be turned into a Null, because in the DB the association is valid and I don't want that linkage to be broken. If I _did_ want that, I'd say the onus is on _me_ to break that association at time of soft-deletion. I realise my scope is very narrow, so if there's a better way to implement your typical soft-delete, I'm all ears (_again, we're not using this technique for soft-delete, but it's a similar-enough example_). I've just checked a bit deeper in the code and `SqlWalker` doesn't have scope over the Criteria, so perhaps passing that to SQLFilter isn't a very smart approach. In fact I think it's only the `BasicEntityPersister` that has the criteria, so really an Event or flag to tell SQLFilter not to run during Proxy load is the only way I can think to solve this. Thinking about SQLFilter a bit deeper; wouldn't you end up with the EntityNotFound exception in any case where you try to fetch a filtered entity from the owning side in a relationship. That would mean it's only "safe" to use lazy-loading from the inherited side (many-to-one or many-to-many) on an entity that has an applied filter. That doesn't seem like a desirable aspect of this feature, right? Sorry for the wall of text.

admin commented

2026-01-22 14:44:38 +01:00

@Ocramius commented on GitHub (Feb 15, 2016):

in our particular use case.

This is a general purpose library: please keep that very well in mind before proposing specific solutions.

This unfortunately means exceptions get thrown by whatever code tries to load the lazy association.

Yes, this is by design, though.

It's not practical for me to try/catch wrap every line of code that might access a lazy association, which is what makes me feel this issues lies on Doctrine's side.

Fully agree, but you have to choose between correctness and practical approach here, IMO. This is indeed not something that the ORM can do for you. You basically hid a business rule inside the persistence layer, and now are trying to re-use the persistence layer to re-apply the business rule at fetch time.

realise my scope is very narrow

Which is why I'm being so much adverse to providing workarounds for an issue that has its roots in a missing domain logic bit.

Thinking about SQLFilter a bit deeper; wouldn't you end up with the EntityNotFound exception in any case where you try to fetch a filtered entity from the owning side in a relationship. That would mean it's only "safe" to use lazy-loading from the inherited side (many-to-one or many-to-many) on an entity that has an applied filter. That doesn't seem like a desirable aspect of this feature, right?

It's actually by design. From our PoV, we completely hide a part of the DB from the ORM, which indeed may cause issues when foreign key integrity clashes with this obfuscated area of the DB. Again: by design - maybe not good design, but commonly used design (soft deletes, I mean).

I am really not going to further discuss this issue until @asm89 got his opinion on the topic.

Meanwhile, I can give you a workaround that is actually going to be more interesting to you:

class Employee
{
    // ...
    private $manager; // soft-deleted to-one association
    public function getManager()
    {
        return $this->manager && $this->manager->active() ? $manager : null;
    }
}

class Manager
{
    // ...
    private $deleted; // soft-delete flag, mapped as datetime|null
    public function active()
    {
        return ! $this->deleted;
    }
}

This will shift the soft-delete logic into your domain, fixing the entire issue completely by making the concept of deletion explicit.

@Ocramius commented on GitHub (Feb 15, 2016): > in our particular use case. This is a general purpose library: please keep that very well in mind before proposing specific solutions. > This unfortunately means exceptions get thrown by whatever code tries to load the lazy association. Yes, this is by design, though. > It's not practical for me to try/catch wrap every line of code that might access a lazy association, which is what makes me feel this issues lies on Doctrine's side. Fully agree, but you have to choose between correctness and practical approach here, IMO. This is indeed not something that the ORM can do for you. You basically hid a business rule inside the persistence layer, and now are trying to re-use the persistence layer to re-apply the business rule at fetch time. > realise my scope is very narrow Which is why I'm being so much adverse to providing workarounds for an issue that has its roots in a missing domain logic bit. > Thinking about SQLFilter a bit deeper; wouldn't you end up with the EntityNotFound exception in any case where you try to fetch a filtered entity from the owning side in a relationship. That would mean it's only "safe" to use lazy-loading from the inherited side (many-to-one or many-to-many) on an entity that has an applied filter. That doesn't seem like a desirable aspect of this feature, right? It's actually by design. From our PoV, we completely hide a part of the DB from the ORM, which indeed may cause issues when foreign key integrity clashes with this obfuscated area of the DB. Again: by design - maybe not good design, but commonly used design (soft deletes, I mean). I am really not going to further discuss this issue until @asm89 got his opinion on the topic. Meanwhile, I can give you a workaround that is actually going to be more interesting to you: ``` php class Employee { // ... private $manager; // soft-deleted to-one association public function getManager() { return $this->manager && $this->manager->active() ? $manager : null; } } class Manager { // ... private $deleted; // soft-delete flag, mapped as datetime|null public function active() { return ! $this->deleted; } } ``` This will shift the soft-delete logic into your domain, fixing the entire issue completely by making the concept of deletion explicit.

admin commented

2026-01-22 14:44:38 +01:00

@dave-newson commented on GitHub (Feb 15, 2016):

Thanks again @ocramius, I only added more because it seemed pertinent to prime you guys with enough detail on my use case. Obviously I don't know doctrine to the extent you guys do and my suggestions are made with binkers attached.

I think your solution fixes altaus's problem, not mine, but it's still useful code for others to consider.

I'm happy to accept that this isn't the intended use for SQLFilter and it's causing the data layer to be screwy, though I also wanted to also point out that I don't really understand what the use case for SQLFilter really is if it's going to "break" the ability to lazy load, but as you said, that could well be by design.

Thanks again for your time. Happy to wait for asm98 from here on out.

@dave-newson commented on GitHub (Feb 15, 2016): Thanks again @ocramius, I only added more because it seemed pertinent to prime you guys with enough detail on my use case. Obviously I don't know doctrine to the extent you guys do and my suggestions are made with binkers attached. I think your solution fixes altaus's problem, not mine, but it's still useful code for others to consider. I'm happy to accept that this isn't the intended use for SQLFilter and it's causing the data layer to be screwy, though I also wanted to also point out that I don't really understand what the use case for SQLFilter really is if it's going to "break" the ability to lazy load, but as you said, that could well be by design. Thanks again for your time. Happy to wait for asm98 from here on out.

admin commented

2026-01-22 14:44:38 +01:00

@PowerKiKi commented on GitHub (Nov 9, 2017):

I also stumbled upon this issue when using SQLFilter. My use-case is similar but slightly more complex, since I don't filter soft-deleted entities, but rather I filter entities that the currently logged in user is allowed to see.

Like others mentioned before, manually replicating that security filter on every single queries would not be very practical and very error prone. And @Ocramius suggestion to shift everything into business rules does not seems like a good idea either, because my security rules are rather complex and would most likely generate lots of SQL queries to load hundreds of objects just for security checks.

SQLFilter seemed like a great idea, because it allows to manage security in a single location and to be applied to all queries. That also very conveniently include many-to-many relations. So I can do $blog->getPosts() and never have to worry if the currently logged user has access to each single post inside that collection. Unfortunately, as soon we do something like $post->getAuthor() and that the currently logged in user is not supposed to be able to access the author, then EntityNotFoundException is thrown.

I start wondering what is the purpose of SQLFilter if it can so easily break code. I understand the explanation that the ORM still must know about the existing author. But then why was SQLFilter introduced at all ? was this case not considered ?

The documentation says nothing about that. And the last commit from @asm89 in this project dates back to 2013. So seems pretty safe to assume that he won't contribute to this issue anymore.

@Ocramius, would you have any new hindsights about that ? shouldn't we at the very least mention this shortcoming in the documentation ? or find a way to fix it anyway ?

@PowerKiKi commented on GitHub (Nov 9, 2017): I also stumbled upon this issue when using `SQLFilter`. My use-case is similar but slightly more complex, since I don't filter soft-deleted entities, but rather I filter entities that the currently logged in user is allowed to see. Like others mentioned before, manually replicating that security filter on every single queries would not be very practical and very error prone. And @Ocramius suggestion to shift everything into business rules does not seems like a good idea either, because my security rules are rather complex and would most likely generate lots of SQL queries to load hundreds of objects just for security checks. `SQLFilter` seemed like a great idea, because it allows to manage security in a single location and to be applied to all queries. That also very conveniently include many-to-many relations. So I can do `$blog->getPosts()` and never have to worry if the currently logged user has access to each single post inside that collection. Unfortunately, as soon we do something like `$post->getAuthor()` and that the currently logged in user is not supposed to be able to access the author, then `EntityNotFoundException` is thrown. I start wondering what is the purpose of `SQLFilter` if it can so easily break code. I understand the explanation that the ORM still must know about the existing author. But then why was `SQLFilter` introduced at all ? was this case not considered ? The [documentation says nothing about that](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/filters.html). And the last commit from @asm89 in this project [dates back to 2013](https://github.com/doctrine/doctrine2/commits?author=asm89). So seems pretty safe to assume that he won't contribute to this issue anymore. @Ocramius, would you have any new hindsights about that ? shouldn't we at the very least mention this shortcoming in the documentation ? or find a way to fix it anyway ?

admin commented

2026-01-22 14:44:39 +01:00

@thomasklein94 commented on GitHub (Jan 2, 2018):

Imho, SQLFilter is a very dangerous tool, if you use it, it's more likely you shot yourself in the foot, than have a working solution. The doctrine's design clearly can't handle any additional SQL filtering expression, as stated above. Because of these, it should be deprecated in the long run.

@PowerKiKi I had the exact same issue myself, and I think i finally managed to fix the problem. My solution is I created a Service that got the SecurityContext, AnnotationReader and the EntityManager's MetadataFactory, and pass these objects to a custom EntityRepository. The EntityRepository store these as static. When my repository asked to create a QueryBuilder object, it creates it with additional criterias and where expressions based on my custom annotations. It is an awful hack, I know, but I could not figure out any other way to solve this. It is a rather big patch to publish via comments (and also I am not very proud of it), but if you are interested, I can share you some codes or ideas.

@thomasklein94 commented on GitHub (Jan 2, 2018): Imho, SQLFilter is a very dangerous tool, if you use it, it's more likely you shot yourself in the foot, than have a working solution. The doctrine's design clearly can't handle any additional SQL filtering expression, as stated above. Because of these, it should be deprecated in the long run. @PowerKiKi I had the exact same issue myself, and I think i finally managed to fix the problem. My solution is I created a Service that got the SecurityContext, AnnotationReader and the EntityManager's MetadataFactory, and pass these objects to a custom EntityRepository. The EntityRepository store these as static. When my repository asked to create a QueryBuilder object, it creates it with additional criterias and where expressions based on my custom annotations. It is an awful hack, I know, but I could not figure out any other way to solve this. It is a rather big patch to publish via comments (and also I am not very proud of it), but if you are interested, I can share you some codes or ideas.

admin commented

2026-01-22 14:44:39 +01:00

@Ocramius commented on GitHub (Jan 2, 2018):

A generic solution is to keep a hard split between read models and write models, and have all read models require the filter as parameter. These read models would use HYDRATE_ARRAY or equivalent and never cause UnitOfWork changes.

Something like: interface ReadOperation { function __invoke(FilterCriteria $criteria) : ReadOnlyResultSet; }

That is a radical shift that represents the requirement in a more precise way, since the write-side needs to be specular to the DB state if you want to manage it with the ORM.

That also solves the performance issues most of the time, as queries can be optimised for reads and writes separately.

@Ocramius commented on GitHub (Jan 2, 2018): A generic solution is to keep a hard split between read models and write models, and have all read models require the filter as parameter. These read models would use `HYDRATE_ARRAY` or equivalent and never cause `UnitOfWork` changes. Something like: `interface ReadOperation { function __invoke(FilterCriteria $criteria) : ReadOnlyResultSet; }` That is a radical shift that represents the requirement in a more precise way, since the write-side needs to be specular to the DB state if you want to manage it with the ORM. That also solves the performance issues most of the time, as queries can be optimised for reads and writes separately.

admin commented

2026-01-22 14:44:39 +01:00

@Ocramius commented on GitHub (Jan 2, 2018):

And yes, I realise that I've just suggested this:

@Ocramius commented on GitHub (Jan 2, 2018): And yes, I realise that I've just suggested this: ![did-you-just-tell-me-to-go-fuck-myself](https://user-images.githubusercontent.com/154256/34491541-263e9e9e-efe4-11e7-8e01-c6ece9bae892.jpg)

admin commented

2026-01-22 14:44:40 +01:00

@PowerKiKi commented on GitHub (Mar 5, 2018):

@thomasklein94, if I understand correctly your solution, it would solve the use-case when using the repository. But it would not be applied to implicit work done by Doctrine. Most importantly things like $blog->getPosts() (implicit loading of a collection) will never use a repository but instead will use BasicEntityPersister (more precisely getOneToManyStatement(), getManyToManyStatement() and getSelectSQL()). So it would completely bypass all mechanics that might have been implemented in a custom repository.

Of course we could implement customer getter (in blog->getPosts()) to use our repositories instead of letting Doctrine do its job implicitly, but that seems to ruin a lot of Doctrine advantages. And explicitly using persistence layer in our model is also against best practice AFAIK.

The most complete and easiest solution I found for that is still the SQL filters, with the non-trivial gotcha mentioned earlier. But even with that gotcha it seems more manageable than everything else so far...

@Ocramius, would you have in mind a use-case where SQL filter would genuinely be the best solution with as few gotchas as possible ? or are SQL filters always some kind of half-assed solution ?

@PowerKiKi commented on GitHub (Mar 5, 2018): @thomasklein94, if I understand correctly your solution, it would solve the use-case when using the repository. But it would not be applied to implicit work done by Doctrine. Most importantly things like `$blog->getPosts()` (implicit loading of a collection) will never use a repository but instead will use `BasicEntityPersister` (more precisely `getOneToManyStatement()`, `getManyToManyStatement()` and `getSelectSQL()`). So it would completely bypass all mechanics that might have been implemented in a custom repository. Of course we could implement customer getter (in `blog->getPosts()`) to use our repositories instead of letting Doctrine do its job implicitly, but that seems to ruin a lot of Doctrine advantages. And explicitly using persistence layer in our model is also against best practice AFAIK. The most complete and easiest solution I found for that is still the SQL filters, with the non-trivial gotcha mentioned earlier. But even with that gotcha it seems more manageable than everything else so far... @Ocramius, would you have in mind a use-case where SQL filter would genuinely be the best solution with as few gotchas as possible ? or are SQL filters always some kind of half-assed solution ?

admin commented

2026-01-22 14:44:40 +01:00

@Ocramius commented on GitHub (Mar 5, 2018):

@Ocramius, would you have in mind a use-case where SQL filter would genuinely be the best solution with as few gotchas as possible ? or are SQL filters always some kind of half-assed solution ?

Can't think of one that isn't an over-simplification of the contextual filtering problem.

The issue is that the ORM is a tool designed to work on synchronous read/write (same model), while a filter filters out data that is actually existing in the write side.

In more recent times, this would be a perfect example of the necessity of a read/write split.

@Ocramius commented on GitHub (Mar 5, 2018): > @Ocramius, would you have in mind a use-case where SQL filter would genuinely be the best solution with as few gotchas as possible ? or are SQL filters always some kind of half-assed solution ? Can't think of one that isn't an over-simplification of the contextual filtering problem. The issue is that the ORM is a tool designed to work on synchronous read/write (same model), while a filter filters out data that is actually existing in the write side. In more recent times, this would be a perfect example of the necessity of a read/write split.

admin commented

2026-01-22 14:44:40 +01:00

@thomasklein94 commented on GitHub (Mar 5, 2018):

@PowerKiKi Yes, it's only effective, when using reporitories, and I can see why someone would need more agressive filtering. Altough, if you can "see" an object, then you should "see" it's related objects as well, as it is in a way part of the given object. If you need to filter the related objects, then you still have options to implement it on your business rules above Doctrine (insted of trying to implement it IN the Doctrine itself) or you can extend the EntityPersister to consult with the connected entity's repository (not very recommended). In my case, it was sufficient to filter entities, and i dont have to filter an entity's relations. With my "solution" I was able to filter entities based on it's properties and related entities' properties, which was enough for me.

SQL filters can not work with Doctrine, when you try to filter an entity and you try to write back anything to the underlying database. And without a major redesign, it would never work. period. As I stated above, SQLFilter should be deprecated, and later removed from the code base.

As bad as it sounds, but If you need this feature in your project, then you should change the ORM to an implementation that supports it. :(

@thomasklein94 commented on GitHub (Mar 5, 2018): @PowerKiKi Yes, it's only effective, when using reporitories, and I can see why someone would need more agressive filtering. Altough, if you can "see" an object, then you should "see" it's related objects as well, as it is in a way part of the given object. If you need to filter the related objects, then you still have options to implement it on your business rules above Doctrine (insted of trying to implement it IN the Doctrine itself) or you can extend the EntityPersister to consult with the connected entity's repository (not very recommended). In my case, it was sufficient to filter entities, and i dont have to filter an entity's relations. With my "solution" I was able to filter entities based on it's properties and related entities' properties, which was enough for me. SQL filters can not work with Doctrine, when you try to filter an entity and you try to write back anything to the underlying database. And without a major redesign, it would never work. period. As I stated above, SQLFilter should be deprecated, and later removed from the code base. As bad as it sounds, but If you need this feature in your project, then you should change the ORM to an implementation that supports it. :(

admin commented

2026-01-22 14:44:40 +01:00

@Ocramius commented on GitHub (Mar 5, 2018):

There isn't a "need for redesign", this is simply not possible to design, that's all.

@Ocramius commented on GitHub (Mar 5, 2018): There isn't a "need for redesign", this is simply not possible to design, that's all.

admin commented

2026-01-22 14:44:40 +01:00

@nhung-le commented on GitHub (Jul 12, 2022):

@althaus I agree with you about there are cases we have to use softdelete and sqlfitler just getting in the way for it because it just need to find by FK and then return a proxy object.
I tried solution from @Ocramius but it didn't work because it already return a proxy object where all the properties values are empty and there is no way I can check if it's active or not from an empty proxy object.

My solution to work around is wrap that EntityNotFoundException and then return null in the catch block. it's just work around to wrap the issue and hide it. not fixing the core issue at all though.

class Employee
{
    // ...
    private $manager; // soft-deleted to-one association
    public function getManager()
    {
         try {
                $this->manager->__load(); // when to get Manager object, call __load() here to get actual object so it won't just return proxy object
                return $this->manager;
            } catch (EntityNotFoundException $exception) {
                return null; // just like there is no manager associated to employee
            }
    }
}

@nhung-le commented on GitHub (Jul 12, 2022): @althaus I agree with you about there are cases we have to use softdelete and sqlfitler just getting in the way for it because it just need to find by FK and then return a proxy object. I tried solution from @Ocramius but it didn't work because it already return a proxy object where all the properties values are empty and there is no way I can check if it's `active` or not from an empty proxy object. My solution to work around is wrap that `EntityNotFoundException` and then return null in the catch block. it's just work around to wrap the issue and hide it. not fixing the core issue at all though. ```php class Employee { // ... private $manager; // soft-deleted to-one association public function getManager() { try { $this->manager->__load(); // when to get Manager object, call __load() here to get actual object so it won't just return proxy object return $this->manager; } catch (EntityNotFoundException $exception) { return null; // just like there is no manager associated to employee } } } ```

admin commented

2026-01-22 14:44:41 +01:00

@PowerKiKi commented on GitHub (Jul 12, 2022):

@nhung-le, that is the exact workaround I have been using for the last 5 years now. I made it into a re-usable method like so:

<?php

namespace Application\ORM\Query\Filter;

use Doctrine\ORM\EntityNotFoundException;
use Doctrine\Persistence\Proxy;

abstract class Filter
{
    /**
     * Try to load the entity from DB, but if it is filtered, it will return null.
     *
     * This mechanic is necessary to hide entities that should have been filtered by
     * AclFilter, but that are accessed via lazy-loaded by doctrine on a *-to-one relation.
     * This scenario is described in details on https://github.com/doctrine/doctrine2/issues/4543
     *
     * @param mixed $object or any kind of value
     *
     * @return mixed
     */
    public static function load($object)
    {
        if ($object instanceof Proxy) {
            try {
                $object->__load();
            } catch (EntityNotFoundException) {
                return null;
            }
        }

        return $object;
    }
}

Then usage in one of my model would look like so:

public function getCreator(): ?User
{
    return Filter::load($this->creator);
}

That does not solve the broken entity graph mentioned by @Ocramius, so you will have issues in some corner cases, but mostly it's been working OK for us.

@PowerKiKi commented on GitHub (Jul 12, 2022): @nhung-le, that is the exact workaround I have been using for the last 5 years now. I made it into a re-usable method like so: ```php <?php namespace Application\ORM\Query\Filter; use Doctrine\ORM\EntityNotFoundException; use Doctrine\Persistence\Proxy; abstract class Filter { /** * Try to load the entity from DB, but if it is filtered, it will return null. * * This mechanic is necessary to hide entities that should have been filtered by * AclFilter, but that are accessed via lazy-loaded by doctrine on a *-to-one relation. * This scenario is described in details on https://github.com/doctrine/doctrine2/issues/4543 * * @param mixed $object or any kind of value * * @return mixed */ public static function load($object) { if ($object instanceof Proxy) { try { $object->__load(); } catch (EntityNotFoundException) { return null; } } return $object; } } ``` Then usage in one of my model would look like so: ```php public function getCreator(): ?User { return Filter::load($this->creator); } ``` That does **not** solve the broken entity graph mentioned by @Ocramius, so you will have issues in some corner cases, but mostly it's been working OK for us.

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#4553