Doctrine 2 - How to design an Entity having subtree type attribute from main tree type Entity

Question

I have a main Entity Segment that has one-to-many categories and category has one-to-many items, so 3 level tree structure

class Segment
{

    /**
     * @ORM\Id
     */
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="Category", mappedBy="segment")
     * @ORM\JoinColumn(name="segment_id", referencedColumnName="id")
     */
    protected $categories;
}  

class Category
{

    /**
     * @ORM\Id
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Segment")
     * @ORM\JoinColumn(name="segment_id", referencedColumnName="id")
     */
    protected $segment;

    /**
     * @ORM\OneToMany(targetEntity="Item", mappedBy="category")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     */
    protected $items;
}

class Item
{

    /**
     * @ORM\Id
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Category")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     */
    protected $category;
}

SQL Tables

CREATE TABLE segment (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
)

CREATE TABLE segment_category 
(
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    segment_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY(segment_id) REFERENCES segment(id)
)
CREATE TABLE segment_item 
(
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY(category_id) REFERENCES segment_category(id)
)

and here is my Entity Company that has sub-segment from Segment

class Company
{
    /**
     * @ORM\Id
     */
    protected $id;

    /**
     * @ORM\ManyToMany(targetEntity="Segment")
     * -- What here to add? Is this correct ?--
     * @ORM\JoinTable(name="company_segment_linker",
     *      joinColumns={@ORM\JoinColumn(name="company_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")}
     * )
     */
    protected $segments;
}

Company SQL table that determines which Segment (also Categories and Items) belongs to Company.

CREATE TABLE company_segment_linker 
(
    company_id INT NOT NULL,
    item_id INT NOT NULL,
    FOREIGN KEY(company_id) REFERENCES company(id),
    FOREIGN KEY(item_id) REFERENCES segment_item(id)
) 

Segment data structure example (numbers represent IDs):

1. seg1
   1. cat1
      1. item1
      2. item2
      3. item3
   2. cat2
      1. item4
      2. item5
2. seg2
   1. cat3
      1. item6
   2. cat4
      1. item7
      2. item8

Company com1 that has a sub-segment like this:

1. seg1
   1. cat1
      1. item2
      2. item3
   2. cat2
      1. item5
2. seg2
   1. cat4
      1. item8

So I would store only company fk_id and item fk_ids in company_segment_linker table
company_id | item_id
1 | 2
1 | 3
1 | 5
1 | 8
because from item table i can refer to category and from category table to segment.

My question is, is this all possible in Doctrine2, to create a sub-segment only from item foreign key(s) and if yes how to write the annotation(s)? Or must I use another approach to achieve this ? Thanks for your help. PS: Sorry for my english :/

Answer 1

For your Category class you can use OneToMony self referencing association like this:

/**
 * Category
 *
 * @ORM\Table(name="category")
 * @ORM\Entity(repositoryClass="Category\Entity\Repository\CategoryRepository")
 */

class Category 

{
/**
* @var Integer
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;

/**
 *@var String
 *@ORM\Column(name="cat_name", type="string", length=60, nullable=false)
 * 
 */
private $name;


/**
 * @ORM\OneToMany(targetEntity="Category\Entity\Category", mappedBy="parent")
 **/

private $children;

/**
 *  @ORM\ManyToOne(targetEntity="Category\Entity\Category", inversedBy="children")
 *  @ORM\JoinColumn(name="parent_id", referencedColumnName="id", unique=false, nullable=true)
 **/
private $parent;



public function __construct() {

    $this->children = new \Doctrine\Common\Collections\ArrayCollection();

}

This create one table for you, and you get your tree by this DQL:

 $ql="SELECT  ca.name, c.name AS child_name FROM Category\Entity\Category ca LEFT JOIN ca.children c WHERE ca.parent IS NULL";
    $query = $em->createQuery($ql);
  $categories = $query->getResult();