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

Question

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?

Answer 1

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'
  )
);