如何在一个请求中获取所有类别

Question

How can I get all categories in one request?
I am using this mapping:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#one-to-many-self-referencing

My entity definition:

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\OneToMany(targetEntity="Application\Entity\Category", mappedBy="parent", cascade={"remove"})
 */
private $children;

/**
 * @var \Application\Entity\Category
 *
 * @ORM\ManyToOne(targetEntity="Application\Entity\Category", inversedBy="children")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="parent", referencedColumnName="id", nullable=true)
 * })
 */
private $parent;

My repository method:

public function findAllCategories() {

    $repository = $this->getEntityManager()
        ->getRepository(Category::class);

    $query = $repository->createQueryBuilder('c')
        ->getQuery();

    return $query->getResult();
}

Output using php var_dump:

array (size=6)
0 => 
object(stdClass)[258]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 10
  public 'name' => string 'Category-1' (length=20)
  public 'url' => string 'ddd/ddd/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(1)' (length=8)
  public 'parent' => null
  public 'products' => string 'Array(0)' (length=8)
1 => 
object(stdClass)[337]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 11
  public 'name' => string 'Category-2' (length=20)
  public 'url' => string 'ddd/ddd/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(2)' (length=8)
  public 'parent' => null
  public 'products' => string 'Array(0)' (length=8)
2 => 
object(stdClass)[351]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 12
  public 'name' => string 'SubCategory-1' (length=26)
  public 'url' => string 'url/url/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(0)' (length=8)
  public 'parent' => string 'Application\Entity\Category' (length=27)
  public 'products' => string 'Array(3)' (length=8)
3 => 
object(stdClass)[352]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 13
  public 'name' => string 'SubCategory-2' (length=26)
  public 'url' => string 'url/url/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(0)' (length=8)
  public 'parent' => string 'Application\Entity\Category' (length=27)
  public 'products' => string 'Array(0)' (length=8)
4 => 
object(stdClass)[353]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 14
  public 'name' => string 'SubCategory-3' (length=20)
  public 'url' => string 'ddd/ddd/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(0)' (length=8)
  public 'parent' => null
  public 'products' => string 'Array(0)' (length=8)
5 => 
object(stdClass)[354]
  public '__CLASS__' => string 'Application\Entity\Category' (length=27)
  public 'id' => int 15
  public 'name' => string 'SubCategory-3' (length=26)
  public 'url' => string 'url/url/' (length=8)
  public 'created' => string 'DateTime' (length=8)
  public 'children' => string 'Array(0)' (length=8)
  public 'parent' => string 'Application\Entity\Category' (length=27)
  public 'products' => string 'Array(0)' (length=8)

My view:

<?php foreach($categories as $category): ?>
    <?php if ($category->getParent() === null) : ?>
        <tr class="parent">
            <td><?= $category->getId() ?></td>
            <td><?= $category->getName() ?></td>
            <td><?= $this->dateFormat($category->getCreated(), IntlDateFormatter::MEDIUM) ?></td>
            <td>
                <a href="<?= $this->url('control/category', ['action' => 'update','id' => $category->getId() ]) ?>">Редактировать</a>
                <a href="<?= $this->url('control/category', ['action' => 'delete','id' => $category->getId() ]) ?>">Удалить</a>
            </td>
        </tr>
        <?php foreach($category->getChildren() as $children) {?>
            <tr class="child">
                <td><?= $children->getId() ?></td>
                <td><?= $children->getName() ?></td>
                <td><?= $this->dateFormat($children->getCreated(), IntlDateFormatter::MEDIUM) ?></td>
                <td>
                    <a href="<?= $this->url('control/category', ['action' => 'update','id' => $children->getId() ]) ?>">Редактировать</a>
                    <a href="<?= $this->url('control/category', ['action' => 'delete','id' => $children->getId() ]) ?>">Удалить</a>
                </td>
            </tr>
        <?php }?>
    <?php endif; ?>
<?php endforeach; ?>

So there are three additional database queries...

Answer 1

Solution: Building a tree of categories in a single request

$categoryTree = [];
foreach ($categories as $category) {
    $parentId = $category->getParent() ? (int)$category->getParent()->getId() : null;
    $categoryTree[$parentId][] = $category;
}


foreach ($categoryTree[null] as $rootCategory) :
    echo $rootCategory->getName();
    if (isset($categoryTree[$rootCategory->getId()])) :
        foreach ($categoryTree[$rootCategory->getId()] as $childCategory) :
            echo $childCategory->getName();
        endforeach;
    endif;
endforeach;