Symfony2 + Doctrine - Filtering

Question

I've got a OneToMany relationship where one football team has many players. I want to list all football teams and display the name of the captain for each team.

Each player entity has a foreign key (team_id) and a field 'captain' which is set to 0 or 1. I'm currently running the following query:

 $teams = $this
             ->getDoctrine()
             ->getRepository('FootballWebsiteBundle:Team')
              ->createQueryBuilder('t')
             ->setFirstResult(($pageNumber * $resultPerPage) - $resultPerPage)
             ->setMaxResults($resultPerPage)
             ->add('where','t.deleted = 0')
             ->add('orderBy', 't.name DESC')
             ->getQuery()->getResult();

Then when I loop through each team in twig I run team.getTeamCaptain().getName() which is a filter within my Team entity:

public function getTeamCaptain() {
    $them  = $this->players->filter(function($p) {
        return $p->getCaptain() == 1;
    });

    return $them->first();
}

Is there a better way to run this query?

Answer 1

First of all, you may want to fetch-join the players of each retrieved team to avoid having them lazy loaded during rendering of the template. Here's the DQL:

SELECT
    t, p
FROM
    FootballWebsiteBundle:Team t
LEFT JOIN
    t.players p
WHERE
    t.deleted = 0
ORDER BY
    t.name DESC

Which can be built with following query builder API calls:

 $teamsQuery = $this
         ->getDoctrine()
         ->getRepository('FootballWebsiteBundle:Team')
         ->createQueryBuilder('t')
         ->addSelect('p')
         ->leftJoin('t.players', 'p')
         ->add('where','t.deleted = 0')
         ->add('orderBy', 't.name DESC')
         ->getQuery()

Then you wrap this query into a Paginator object (since setMaxResults and setFirstResult cannot be trusted when fetch-joining):

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($teamsQuery, true);

$teamsQuery
     ->setFirstResult(($pageNumber * $resultPerPage) - $resultPerPage)
     ->setMaxResults($resultPerPage)

In your view you can then iterate on the teams like following pseudo-code:

foreach ($paginator as $team) {
    echo $team->getTeamCaptain() . "\n";
}

You can also gain some extra performance in your getTeamCaptain method by using the Selectable API:

public function getTeamCaptain() {
    $criteria = new \Doctrine\Common\Collections\Criteria();

    $criteria->andWhere($criteria->expr()->eq('captain', 1));

    return $this->players->matching($criteria)->first();
}

The advantage here is mainly relevant when the association players is not yet initialized, since this will avoid loading it entirely. This is not the case, but I consider it a good practice (instead of re-inventing collection filtering logic).