0

A query load and hydrate a main entity with it's joined entities, which are filtered.
Same query with a different filtering did not update the joined entities.

Some data:

Tiers:
| id | name  |
| 1  | alpha |
| 2  | beta  |

Container:
| id  | tiers_id | category |
| 10  | 1        | A        |
| 20  | 1        | A        |
| 30  | 1        | B        |
| 40  | 1        | B        |

Execute 2 queries to get some tiers with theirs containers joined, category A first, then category B:

$dql = "select t, c
    from Tiers t
    join t.containers c
    where t.id in (?1) and c.category = (?2)";

$result = $em->createQuery($dql)
    ->setParameter(1, array(1))
    ->setParameter(2, 'A')
    ->getResult();
$tiers = $result[0];
$containers = $tiers->getContainers(); // tiers 1 with containers 10 and 20, that's fine !

$result = $em->createQuery($dql)
    ->setParameter(1, array(1))
    ->setParameter(2, 'B')
    ->getResult();
$tiers = $result[0];
$containers = $tiers->getContainers(); // BAD HERE: still get containers 10 and 20, looking for containers 30 and 40.

After the 2nd query, tiers 1 preserve its containers loaded during first query. That's not what is expected.
So is there a way to get the containers 30 and 40 after 2nd query ?
Maybe a kind of "reset/detach" the containers of tiers entities after the first query ?
Or anything else...

Mutiple select in queries are used to hydrate tiers with required containers joined.
'getContainers' method gives the expected containers from each tiers.
And cost to BDD is only 1 SQL query, whatever the quantity of tiers searched.

I suppose tiers cannot be detach/reload because they are updated before, between and after the queries, it throw this kind of exception when flushing:

Uncaught Exception: Multiple non-persisted new entities were found through the given association graph
* A new entity was found through the relationship 'XXX' that was not configured to cascade persist operations for entity: XXX\Entity\Tiers@00000000257b87500000000018499b62.
Guillaume
  • 162
  • 3
  • 11
  • 1
    https://stackoverflow.com/questions/48152609/doctrine-onetomany-relation-all-result-row-doesnt-fetch-in-object/48510697#48510697 This could be the solution – Jannes Botis Dec 15 '18 at 14:01

1 Answers1

0

Reset the tiers's containers before 2nd query:

foreach($result as $tiers)
    $tiers->nullContainers();

Add method to Entity\Tiers:

public function nullContainers()
{
     this->containers = null;
}

Then the 2nd query "refresh" tiers's containers.

Guillaume
  • 162
  • 3
  • 11