我在树枝模板中有类别树:
{% for category in categories %}
<li>
<div class="li"><a href="{{ path('accessories_list', { 'category' : category.url }) }}">{{ category.name|trans({}, 'categories')|raw }}</a></div>
{% if category.children is not empty %}
<ul>
{% include "default/_menu_links.html.twig" with {'categories':category.children} only %}
</ul>
{% endif %}
</li>
{% endfor %}
如果我有6个类别和7个子类别,则创建 -53数据库查询。
有没有办法减少该数字?我在学说中使用了UserEsultCache(true),但看起来它没有从缓存中加载(至少不是在Dev模式下)。
您如何处理类别树?
更新:实体:
...
/**
* One Category has Many Subcategories.
* @ORMOneToMany(targetEntity="Category", mappedBy="parent", cascade={"persist"}))
*/
private $children;
/**
* Many Subcategories have One Category.
* @ORMManyToOne(targetEntity="Category", inversedBy="children", cascade={"persist"})
* @ORMJoinColumn(name="parent_id", referencedColumnName="id")
*/
private $parent;
...
/**
* Add child
*
* @param AppEntityProductCategory $child
*
* @return Category
*/
public function addChild(AppEntityProductCategory $child): Category
{
$this->children[] = $child;
return $this;
}
/**
* Remove child
*
* @param AppEntityProductCategory $child
*/
public function removeChild(AppEntityProductCategory $child)
{
$this->children->removeElement($child);
}
/**
* Get children
*
* @return DoctrineCommonCollectionsCollection
*/
public function getChildren(): Collection
{
return $this->children;
}
/**
* Set parent
*
* @param AppEntityProductCategory $parent
*
* @return Category
*/
public function setParent(AppEntityProductCategory $parent = null): Category
{
$this->parent = $parent;
return $this;
}
/**
* Get parent
*
* @return AppEntityProductCategory
*/
public function getParent()
{
return $this->parent;
}
...
根据我的评论:您必须 JOIN
您的子类别。我假设您目前正在这样做以检索您的categories
:
public function getCategories()
{
return $this->getEntityManager()->createQueryBuilder()
->select("category")
->from("App:Category", "category")
->where("category.parent IS NULL")
->getQuery()->getResult();
}
因此,如果您现在正在迭代此类别数组并尝试访问children
属性,则将为每个孩子触发子查询,从而导致大量的数据库查询。
相反,您应该像这样JOIN
:
public function getCategories()
{
return $this->getEntityManager()->createQueryBuilder()
->select("category", "subcat")
->from("App:Category", "category")
->leftJoin("category.children", "subcat")
->where("category.parent IS NULL")
->getQuery()->getResult();
}
如果您现在在categories
上迭代并访问children
属性,则不会发出额外的查询!
使用上面的查询和TWIG中的该片段仅导致一个数据库查询:
{% for category in categories %}
<p>cat={{ category.id }}</p>
{% for subcat in category.children %}
<p>subcat={{ subcat.id }}</p>
{% endfor %}
<hr>
{% endfor %}