How to recover root nodes

Question

Using symfony2 and doctrine2 with doctrine tree extension, I have recently updated an entity to make it a doctrine nested set tree.

A doctrine schema update force added the right columns with null data.

Then I ran the following code :

        $repo = $this->getDoctrine()->getRepository('AppBundle:FoodAnalytics\Recipe');

        $repo->verify();
// can return TRUE if tree is valid, or array of errors found on tree
        $repo->recover();
        $this->flush(); // important: flush recovered nodes
// if tree has errors it will try to fix all tree

It successfully recovered left right and level values but not root. I can't manually set root values (prohibited by doctrine listener).

How can I update those root values so the tree could work properly ?

Thanks !

Mahala

Answer 1

If you have multiple root nodes and multiple sub categories you can run this query :

UPDATE category SET category.tree_root = IF(category.parent_id IS NULL, category.id, category.parent_id)

It will set the category id as the tree node only if it has no parent (so its a root category) otherwise it will set the parent id as the tree id.

then fix, running :

$em->clear();
$repository->verify();
$repository->recover();
$em->flush();

Example of data set:

• restaurant (id: 1, tree root: 1)
  • pizza (id: 2, tree root: 1)
  • burger (id: 3, tree root: 1)
• bar (id: 4, tree root, 4)
  • coktail bar (id: 5, tree root: 4)
  • dj bar (id: 6, tree root: 4)
• shop (id: 7, tree root: 7)
  • bakery (id: 8, tree root: 7)
  • ice cream (id: 9, tree root: 7)
  • wine shop (id: 10, tree root: 7)

Madwort

Answer 2

Well, i did not find an object oriented solution so I went for a raw SQL query. i'm doing this before any entity gets a parent, therefore each entity's root value should equal its own id.

In phpmyadmin, paste the following sql query and execute :

update recipe
set recipe.root = recipe.id

Mahala