Hierarchal data with Doctrine2 using closure table model

Question

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

In this post I'll use Category as an example entity. Here's what I imagine the entity looking like:

<?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 closure table looks as follows:

categories_paths(ancestorID, descendantID, pathLength)

This table is essentially a join table -- it only stores the parent-child relations, so I don't think it makes sense for there to be an entity here, similar to how there's no entity when creating a many-to-many relationship with @JoinTable.

I'd like to be able to use my Category entity like any other Entity, with $parent / $children populated when I fetch it from the repository and when $em->flush() is called, have SQL executed to reflect newly added children.

Some examples of SQL 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 a subtree to a 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 children of a Category:

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

I have a few questions here. First of all, does this seem like a reasonable approach / something that is possible to implement with Doctrine? If not, is there a better way to approach this?

If this does seem like a reasonable approach, I'm wondering how to go about attacking this? I'm more looking for where I need to put these files / how I need to set up classes vs. someone giving me an actual implementation. Any documentation or examples that would help me get started would be much appreciated. I have pretty much zero experience with Doctrine--hopefully I'm not missing anything obvious here.

Answer 1

I think if you want to build a hierarchical database you should look for the doctrine ODM project. All the things you want are built in into that and you can customize your node.

There's a mongoDB adapter and also you can take a look at DoctrinePHPCR project that has adapters for several databases.

Even if you want to implement your own approach using doctrine ORM you can look at their implementations to get an idea how they work. They have node based relationship so you always have reference to adjacent nodes in the tree in your object.

Hope that helps.