Doctrine 2: Efficient way to query a many-to-many association

Question

I have a many-to-many association between two tables (franchise and players), built with an additional table (franchise_players). I have create the three entities:

class Franchise 
{
    ...
    /**
     * 
     * @var ArrayCollection
     * 
     * @ORM\OneToMany(targetEntity="NBA\Entity\FranchisePlayer", mappedBy="franchise")
     */
    private $franchiseplayers;
    ...
}

class Player
{
    /* some fields */
}

class FranchisePlayers 
{
    ...
    /**
     * @var \NBA\Entity\Player
     *
     * @ORM\ManyToOne(targetEntity="NBA\Entity\Player")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="playerId", referencedColumnName="id")
     * })
     */
     private $player;

     /**
      * @var \NBA\Entity\Franchise
      *
      * @ORM\ManyToOne(targetEntity="NBA\Entity\Franchise")
      * @ORM\JoinColumns({
      *   @ORM\JoinColumn(name="franchiseId", referencedColumnName="id")
      * })
      */
      private $franchise;
}

My goal is to get all the franchises with their related players.

The obvious solution is easy but not efficient:

Get all franchises
foreach $franchise
    $franchise->getFranchisePlayers()
    foreach $franquisePlayer
        $franquisePlayer->getPlayer()->getFullName();

Another solution is to get all franchises and then perform a left join to get the related players for every franchise:

Get all franchises
foreach $franchise
    public function getCurrentPlayers(Franchise $franchise) {   

    $qb = $this->entityManager->createQueryBuilder();
    $qb->select(array('p'))
       ->from('NBA\Entity\Player', 'p')
       ->leftjoin('NBA\Entity\FranchisePlayer','f','WITH','f.player=p')
       ->where('f.franchise = ?1')
       ->orderBy('p.surname','ASC')
       ->setParameter(1,$franchise);

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

In this way we reduce the number of queries.

My question is if I can achieve my goal with just one query like this:

public function getFranchisesWithPlayers() {
    $qb = $this->entityManager->createQueryBuilder();
    $qb->select(array('f'))
        ->from('NBA\Entity\Franchise', 'f')
        ->leftJoin('NBA\Entity\FranchisePlayer','fp','WITH','fp.franchise=f')
        ->leftJoin('NBA\Entity\Player','p','WITH','fp.player=p')
        ->orderBy('f.name')
        ->addOrderBy('p.surname');

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

I thought that the franchisePlayers field of the Franchise entities (and also de Player entity of every FranchisePlayer) would be automatically loaded but it seems that this isn't true.

Is it possible to do it? Maybe Am I doing something wrong in the mappings of my entities?

Thank you!

EDIT: Yesterday I was sure that the less queries best efficiency but now I'm doubting. Maybe the cost of hydratating the result of this complex query is bigger than the cost of hydratating every result of the queries of option 2 (query for the players of every franchise in different queries). What do you think?

Answer 1

The documentation shows how to create queries. Might be worth taking a look. Something like:

$qb->select('franchise,franchisePlayer,player')
    ->from('NBA\Entity\Franchise', 'franchise')
    ->leftJoin('franchise.franchisePlayers','franchisePlayer')
    ->leftJoin('franchisePlayer.player','player')
    ->orderBy('franchise.name')
    ->addOrderBy('player.surname');