We have a legacy data structure which has an m:n-relationship with itself (think organizational structure where any entity can have a variable amount of child and/or parent entities). The technical realization is through a table for the entities and a table where each row describes two entities and their relationship (parent or child).

On reads for any entity, we see sort of cascading database request, where for any entity, the list of children or parents is traversed and all related entities are fetched, one by one, recursively.

This has flown mostly under the radar due to a low total entity count, but as time went on, this is becoming more problematic on a ORM level. The total count still is (and will stay) totally manageable, within the low four digits, so it would be perfectly fine to just fetch it all at once and have code do the traversal; the problem are the hundreds of DB round trips.

I know I can explicitly tell Doctrine to use a query cache and do a findAll(); but I don’t understand how I can then tell Doctrine that when I subsequently want a specific entity, I want it’s children/parents not fetched through new queries but returned from the cache. Is that possible ?

I understand that our data structure is not ideal, but due to the amount of legacy code depending on it, refactoring it will be at best a winter project. Adding caching now would be a huge benefit.

It doesn’t seem like adding any specific code would be helpful, but let me know if there is anything I should append to the question.