Doctrine生成大量查询以显示具有子实体的实体

I have the following database structure (simplified). All relations are many to one.

table parent:

ID | SOME_DATA
-------------------------------
1  | Lorum
2  | Ipsum
..etc

table child:

ID | PARENT_ID | SOME_DATA
-------------------------------
1  | 2         | Dolor
2  | 5         | Sis
..etc

Using the normal doctrine method to display them all on a single page:

<?php
//get parents
$parents = $this->getDoctrine()->getRepository('FamilyBundle:Parent')->findAll();

//loop through parents
foreach($parents AS $parent){

  //display parent
  echo '<h1>'.$parent->getName().'</h1>';

  //show children
  foreach($parent->getChildren() AS $child)
    echo '<h2>'.$child->getName().'</h2>';
}

When using the debut tool, I was surprised to find out that in order to retrieve the child entities, a new database query is used for every parent entity. Resulting in a very inefficient script.

Above example is simplified. I could use a raw query if I weren't dependend on some specialised methods in the entity class. So my question is, is there a way to do a smarter query, but still be able to manage the data with the doctrine entity manager so that I can still access the entity class methods. Preferably, I would like to specify which children of the parent entity are preloaded, as I won't need to use all of them.

Anyone can point me in the right direction?

Doctrine uses "lazy loading" by default if no join clause in query, so you have to create a custom repository class for your parent entity to reduce doctrine queries number.

Just add repository annotation to your Parent entity class :

// FamilyBundle\Entity\Parent.php
namespace FamilyBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="FamilyBundle\Repository\ParentRepository")
 */
class Parent {
  protected $children; // OneToMany bidirectional annotation i suppose
  // Do not forget ArrayCollection in constructor and addChild, removeChild and getChildren methods !
}

And create your custom repository with join clause :

// FamilyBundle\Repository\ParentRepository.php
namespace FamilyBundle\Repository;

use Doctrine\ORM\EntityRepository;

class ParentRepository extends EntityRepository
{
  public function findParents(array $criteria, array $orderBy = null)
  {
    $qb = $this
      ->createQueryBuilder('parent')
      ->leftJoin('parent.children', 'children') // join clause
      ->addSelect('children') // get children rows
    ;

    if (isset($criteria['some_data'])) // where clause example
    {
      $qb
        ->andWhere('children.some_data = :some_data') // andWhere clause works even if first where
        ->setParameter('some_data', $criteria['some_data'])
      ;
    }

    if (isset($orderBy['other_data'])) // orderBy clause example on Parent entity
    {
      $qb
        ->addOrderBy('parent.other_data', $orderBy['other_data']) // or orderBy clause
      ;
    }

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

In your controller :

$parents = $this->getDoctrine()->getRepository('FamilyBundle:Parent')->findParents(
  array(
    'some_data' => 'dolor'
  ),
  array(
    'other_data' => 'DESC'
  )
);