树根主义

Question

I want to get roots of tree in database, it works correctly but problem is here that it query database 500 times for 500 nodes, is there a way to improve this?

$query = $this->createQueryBuilder('n')
            ->where('n.parent IS NULL')
            ->getQuery();`

entity structure:

Radio\ArchiveBundle\Entity\Category:
    type: entity
    table: null
    repositoryClass: Radio\ArchiveBundle\Repository\CategoryRepository
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    fields:
        name:
            type: string
            length: 255
        isActive:
            type: boolean
            nullable: true

    oneToMany:
        tracks:
            targetEntity: Radio\ArchiveBundle\Entity\Track
            mappedBy: category
    manyToOne:
        parent:
            targetEntity: Radio\ArchiveBundle\Entity\Category
            inversedBy: childrens
            fetch: EAGER
    oneToMany:
        childrens:
            targetEntity: Radio\ArchiveBundle\Entity\Category
            mappedBy: parent
            cascade: [persist, remove]

Symfony2 profiler image:

Answer 1

Why 500 queries?

As you say, you've fetched 500 root nodes from the database. Somewhere in the request the property Category::$childrens is accessed. Probably in a view by calling a method on Category that uses that property (like $node->getChildren()).

Accessing that property means that Doctrine will need to load the Collection in order to fetch the children of that node. This results in a query for each root node. Hence you have 500 additional queries.

If you need to access the children, it's wise to fetch them immediately when querying for the root nodes:

SELECT rt, chld FROM Category rt LEFT JOIN rt.childrens chld WHERE rt.parent IS NULL

This way the direct children of the root nodes are hydrated along with the root nodes themselves. You'll have reduced the number of queries from 501 to just 1.

^{PS: The plural form of "child" is "children" (not "childrens").}

Nested Set model

IMHO the structure of the Category tree could be improved by switching to the Nested Set model. While it is slightly less performant when inserting new nodes to the tree, updating and selecting trees becomes significantly cheaper.

For example: Fetching an entire tree with the parent/children model you use now will need roughly 1 query for each level of depth (or 1 JOIN for each level, but this only works when the maximum depth is known). With the Nested Set model you can fetch an entire tree with just a single query (regardless of the depth). This will improve performance drastically when the level of depth increases.

Have a look at the Tree behavior of the Doctrine Extensions library, with which you can easily implement the Nested Set model. There's also a Symfony 2 bundle to integrate that library.

Simply add it to Composer and follow the instructions.

composer.phar require stof/doctrine-extensions-bundle