CODEWITHSUNDEEP
phpdoctrine-ormdoctrine
olidem
olidem

Reputation: 2121

Fetching all objects of one class with self-referencing ManyToMany

Think of this class:

class Person {

    /**
     * @var ArrayCollection
     * 
     * @ORM\ManyToMany(targetEntity="Person", fetch="EXTRA_LAZY")
     * @ORM\OrderBy({"name" = "ASC"})
     */
    public $friends;
   /**
     *
     * @var Person
     * @ORM\ManyToOne(targetEntity="Person")
     */
    public $bestFriend;
}

I have to iterate a lot over all Persons, so I'd like to fetch join them all at once. To save memory, I have to do this partially.

So what I can do is:

$this->em->createQuery('SELECT partial p.{id, name, bestFriend} FROM Person p')->getResult();

This is cool, after this query, all persons are in the UoW, and I can traverse the graph via $aPersion->bestFriend->bestFriend without creating an additional query to the DB, since all Persons are in memory.

However, this does not work with the ToMany association. Adding friends to the partial select gives an error. If I want to iterate over all friends, this will first create a query to the join table...

How can I realise the full hydration of the friends-ToMany-assotiation with one query? Maybe a second query could help? Or a clever join clause?

Thanks in advance!

Upvotes: 1

Views: 43

Answers (2)

olidem
olidem

Reputation: 2121

@DirkJFaber your answer was right,

in terms of DQL here is my solution:

$this->em->createQuery('
    SELECT partial p.{id, name, bestFriend}, f FROM Person p JOIN f.friends f')->getResult();

Upvotes: 0

Dirk J. Faber
Dirk J. Faber

Reputation: 4701

I would create a query in PersonRepository.php with a leftJoin and a addSelect like so:

$qb = $this->em->getRepository('App:Person')
    ->createQueryBuilder('p')
    ->leftJoin('p.friends', 'friends')
    ->select('partial p.{id, name, bestFriend}'}
    ->addSelect('partial friends.{id, name}') // Retrieve what you want here
    ->getQuery()->getResult();
return $qb;

I have not tested this, but believe it should work.

Upvotes: 1

Related Questions