Как получить все категории за один запрос

Как я могу получить все категории в одном запросе?
Я использую это отображение:

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

Мое определение сущности:

/**
* @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; ?>

Итак, есть три дополнительных запроса к базе данных …

0

Решение

Решение:
Построение дерева категорий за один запрос

$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;
0

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]