Как я могу получить все категории в одном запросе?
Я использую это отображение:
Мое определение сущности:
/**
* @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;
Мой метод хранилища:
public function findAllCategories() {
$repository = $this->getEntityManager()
->getRepository(Category::class);
$query = $repository->createQueryBuilder('c')
->getQuery();
return $query->getResult();
}
Вывод с использованием 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)
Мой взгляд:
<?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; ?>
Итак, есть три дополнительных запроса к базе данных …
Решение:
Построение дерева категорий за один запрос
$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;
Других решений пока нет …