Symfony / Twig-递归减少DB查询



我在树枝模板中有类别树:

{% 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 %}

相关内容

  • 没有找到相关文章

最新更新