Doctrine - OneToMany relation, all result row does

Question

I try to get all my objects DemandCab with their children object (DecisionCab).

My 2 entities

/**
 * DemandCab.
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="DemandCabRepository")
 */
class DemandCab
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
     private $id;

    /**
     * @var DecisionCab
     *
     * @ORM\OneToMany(targetEntity="\My\CabBundle\Entity\DecisionCab", mappedBy="demandCab")
     */
     private $decisionsCab;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="startDate", type="datetime")
     */
     private $startDate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="endDate", type="datetime", nullable=true)
     */
     private $endDate;

    /**
     * @var int
     *
     * @ORM\Column(name="followup", type="integer", nullable=true)
     */
     private $followup;

    /**
     * @var InfoCab
     *
     * @ORM\ManyToOne(targetEntity="\My\CabBundle\Entity\InfoCab", inversedBy="demandsCab")
     */
     private $infoCab;

}

/**
 * DecisionCab.
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="DecisionCabRepository")
 */
 class DecisionCab
 {
     /**
      * @var int
      *
      * @ORM\Column(name="id", type="integer")
      * @ORM\Id
      * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

     /**
      * @var DemandCab
      *
      * @ORM\ManyToOne(targetEntity="\My\CabBundle\Entity\DemandCab", inversedBy="decisionsCab")
      */
     private $demandCab;

    /**
     * @var bool
     *
     * @ORM\Column(name="decision", type="boolean", nullable=true)
     */
     private $decision;

    /**
     * @var string
     *
     * @ORM\Column(name="motif", type="string", length=500, nullable=true)
     */
     private $motif;

    /**
     * @var string
     *
     * @ORM\Column(name="role", type="string", length=255)
     */
     private $role;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="datetime", nullable=true)
     */
     private $date;

    /**
     * @var DemandCab
     *
     * @ORM\ManyToOne(targetEntity="\My\CabBundle\Entity\DemandCab", inversedBy="decisionsCab")
     */
     private $demandCab;

    /**
     * @var User
     *
     * @ORM\ManyToOne(targetEntity="\My\CabBundle\Entity\User", inversedBy="decisionsCab")
     */
    private $user;
}

In my DemandCabRepository

public function findAllCompleted(){
    $qb = $this->createQueryBuilder("dem");
    $qb->select('dem, dec');
    $qb->leftJoin("dem.decisionsCab", "dec");
    $qb->andWhere("dem.completed = 1");
    $qb->orderBy("dem.startDate", "DESC");

    return $qb->getQuery()->getResult();
}

My DemandCab data

My DecisionCab data

When i dump result, only 2 decisions appear ...

... whereas when i use getArrayResult, i have my 4 decisions ...

The query is good but i dont understand why hydration remove DecisionCab object with attribute decision at 0 or 1 (only null are display).

I would like to understand why and is there a solution to get DemandCab object with all DecisionCab children object.

Thanks

Answer 1

I am able to reproduce your issue, but I am not sure if this is your case.

Anyway, my assumption is that you query the demand Entity joined with decision relation at least once with the help of a query builder. Maybe this is done in your action, in an event listener or somewhere else in your code.

So you may have something like:

$qb = $this->getDoctrine()
        ->getRepository(DemandCab::class)->createQueryBuilder("dem");
$qb->select('dem, dec');
$qb->leftJoin("dem.decisionsCab", "dec");

$qb->andWhere("dec.decision IS NULL");
$qb->orderBy("dem.startDate", "DESC");

$results = $qb->getQuery()->getResult(); // <-- the decisionsCab collection is hydrated but filtered

$qb2 = $this->getDoctrine()
        ->getRepository(DemandCab::class)->createQueryBuilder("dem");
$qb2->select('dem, dec');
$qb2->leftJoin("dem.decisionsCab", "dec");

$qb2->andWhere("dem.completed = 1");
$qb2->orderBy("dem.startDate", "DESC");

    $q = $qb2->getQuery();
    //$q->setHint(Query::HINT_REFRESH, true);

    $results = $q->getResult();

The issue is in Doctrine\ORM\Internal\Hydration\ObjectHydrator, it has the property "initializedCollections" where already initialized collections are kept and the collections are hashed by the parent entity type and the entity itself. Unfortunately in the above case, the heydrator does not understand that the collection is filtered in the 1st query and uses it in the 2nd query in order to avoid rehydration.(github link)

The solution is to cause the query builder to refresh. Try the code:

$qb->orderBy("dem.startDate", "DESC");
$q = $qb->getQuery();
$q->setHint(Query::HINT_REFRESH, true);    // <-- Tell the hydrator to refresh
return $q->getResult();

Answer 2

First you have initialize your class with the relation ManyToOne with an ArrayCollection.

And you don't need any of this 'DemandCabRepository'. All the work is done by Doctrine