Hierarchical data with Doctrine2 using closure table model

I have some existing data stored using a closure table model . I am new to Doctrine and am trying to implement Entity for this "Doctrine way" and not really sure how to proceed. The philosophy I'm trying to understand is that the Entity should be just an old PHP object and that some kind of annotation should be used to set up parent-child associations.

In this post I will use Category as an example. Here, I imagine an object similar to:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Table(name="categories)
 * @ORM\Entity
 */
class Category
{
    /**
     * @ORM\Column(name="categoryID", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $categoryID;

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

    /**
     * @MyORM\TreeParent(targetEntity="Category", closureTable="categories_paths", ancestorColumn="ancestorID", descendantColumn="descendantID")
     */
    protected $parent;

    /**
     * @MyORM\TreeChildren(targetEntity="Category", closureTable="categories_paths", ancestorColumn="ancestorID", descendantColumn="descendantID")
     */
    protected $children;

    public function __construct()
    {
        $this->children = new ArrayCollection();
    }

    public function getChildren()
    {
        return $this->children;
    }

    public function addChild(Category $child)
    {
        $this->children[] = $children;
    }

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

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

      

        
        
        
      

    

The closing table looks like this:

categories_paths(ancestorID, descendantID, pathLength)

      

        
        
        
      

    

This table is essentially a join table - it only stores the parent-child relationship, so I don't think it makes sense for an entity to exist here, just as there is no entity when creating a multi-user relationship with @JoinTable

.

I would like to be able to use a Category object like any other Entity, with a $parent

/ $children

that gets populated when I check it out from the repository, and when called $em->flush()

, the SQL is executed to reflect the new children added.

Some SQL examples used here:

Add a new child:

INSERT INTO categories_paths (ancestorID, descendantID, pathLength)
SELECT a.ancestorID, d.descendantID, a.pathLength+d.pathLength+1
FROM categories_paths a, categories_paths d
WHERE a.descendantID = $parentCategoryID AND d.ancestorID = $childCategoryID

      

        
        
        
      

    

Move the subtree to the new parent:

// Delete all paths that end at $child
DELETE a FROM categories_paths a
JOIN categories_paths d ON a.descendantID=d.descendantID
LEFT JOIN categories_paths x
ON x.ancestorID=d.ancestorID AND x.descendantID=a.ancestorID
WHERE d.ancestorID = $subtreeCategoryID and x.ancestorID IS NULL

// Add new paths
INSERT INTO categories_paths (ancestorID, descendantID, pathLength)
SELECT parent.ancestorID, subtree.descendantID,
       parent.pathLength+subtree.pathLength+1
FROM categories_paths parent
JOIN categories_paths subtree
WHERE subtree.ancestorID = $subtreeCategoryID
  AND parent.descendantID = $parentCategoryID;

      

        
        
        
      

    

Get all kids in a category:

SELECT * FROM categories
JOIN categories_paths cp ON cp.descendantID=categories.categoryID
WHERE cp.ancestorID = $catogeryID
AND cp.depth=1

      

        
        
        
      

    

I have several questions here. First of all, does this sound like a sane approach / something that can be implemented with Doctrine? If not, is there a better way to approach this?

If this sounds like a sane approach, I'm wondering how to do it? I'm looking more for where I need to put these files / how I need to set up the classes and someone gives me a real implementation. Any documentation or examples to help me get started would be much appreciated. I have had quite a bit of experience with Doctrine - I hope I don’t miss anything obvious here.