php recursive function performance optimization

Question

I am using Symfony 2.8 with doctrine 2.4

I have an entity with One-To-Many, Self-referencing Association Mapping entity: id name parent_id example:

id: 1 
name: A 
parent_id: null

id: 2
name: B
parent_id = 1

id: 3
name: C
parent_id = 2

id: 4
name: D
parent_id = 3

I wrote a function which finds all nested children's id of the parent.

public function getAllChildrenIdRecursively($entity)
{
    $result = array();
    if (count($entity->getChildren()) > 0) {
        $result[] = $entity->getId();
        foreach ($entity->getChildren() as $child) {
            $result = array_merge($result, $this->getAllChildrenIdRecursively($child)) ;
        }
    }

    return $result;
}

if i call this function getAllChildrenIdRecursively(2) then i will get 3, 4.

this function is working but my problem is it generates too many queries in DB which take almost 1.5 seconds.Query generates more than 2500, total result = 2600 and total row = 5000

How can I reduce query time?

Any help would be much appreciated! Thanks

Answer 1

With your children implementation you do not have solution to efficiently optimized your code. You have to change your implementation to go throught all your children and subchildren with only one request. Tree implementation is a better solution because with one SQL request, you can catch all children, with another one you can have all parents, etc.

I think you should have a look on this excellent tree doctrine extensions to avoid recursion. If you only are in doctrine 2.4, test the 2.3 extension version.

<?php
namespace Entity;

use Gedmo\Mapping\Annotation as Gedmo;
use Doctrine\ORM\Mapping as ORM;

/**
 * @Gedmo\Tree(type="nested")
 * @ORM\Table(name="categories")
 * use repository for handy tree functions
 * @ORM\Entity(repositoryClass="Gedmo\Tree\Entity\Repository\NestedTreeRepository")
 */
class Category
{
    /**
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    private $id;

    /**
     * @ORM\Column(name="title", type="string", length=64)
     */
    private $title;

    /**
     * @Gedmo\TreeLeft
     * @ORM\Column(name="lft", type="integer")
     */
    private $lft;

    /**
     * @Gedmo\TreeLevel
     * @ORM\Column(name="lvl", type="integer")
     */
    private $lvl;

    /**
     * @Gedmo\TreeRight
     * @ORM\Column(name="rgt", type="integer")
     */
    private $rgt;

    /**
     * @Gedmo\TreeRoot
     * @ORM\ManyToOne(targetEntity="Category")
     * @ORM\JoinColumn(name="tree_root", referencedColumnName="id", onDelete="CASCADE")
     */
    private $root;

    /**
     * @Gedmo\TreeParent
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
     * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="CASCADE")
     */
    private $parent;

    /**
     * @ORM\OneToMany(targetEntity="Category", mappedBy="parent")
     * @ORM\OrderBy({"lft" = "ASC"})
     */
    private $children;

    public function getId()
    {
        return $this->id;
    }

    public function setTitle($title)
    {
        $this->title = $title;
    }

    public function getTitle()
    {
        return $this->title;
    }

    public function getRoot()
    {
        return $this->root;
    }

    public function setParent(Category $parent = null)
    {
        $this->parent = $parent;
    }

    public function getParent()
    {
        return $this->parent;
    }
}