title: Symfony2 fetch categories and subcategories using self Join
body: <p>Hello guys i have one litle problem i want to fetch all my root categories and sub categories and list it in twig .</p>
  
  <p><strong>My database table looks like this:</strong></p>
  
  <pre><code>+----+----------+------------------+--------+
  | ID | Parent   |      Name        | Status |
  +----+----- ----+------------------+--------+
  | 1  |          | Electronics      |   1    |
  | 2  |   1      | Camcorders       |   1    |
  | 3  |   1      | Computers        |   1    |
  | 4  |   1      | TV Audio         |   1    |
  | 5  |          | Motors           |   1    |
  | 6  |   5      | Cars &amp; Trucks    |   1    |
  | 7  |   5      | Motorcycles      |   1    |
  | 8  |   5      | Boats            |   1    |
  +----+----------+------------------+--------+
  </code></pre>
  
  <p>Root categories dont have parent and have null value, all sub categories contain ID from root category. </p>
  
  <p><strong>The tree looks like this:</strong></p>
  
  <pre><code>|—Electronics 
     |—  Camcorders   
     |—  Computers
     |—  TV Audio     
  |—Motors                 
     |—  Cars &amp; Trucks
     |—  Motorcycles
     |—  Boats
  </code></pre>
  
  <blockquote>
    <p>NOTE: I dont need nested set data (tree data) this tree is just example</p>
  </blockquote>
  
  <p>I want to show all that in twig  like this drop down menu:</p>
  
  <pre><code>+---------------------+--------+
  | Electroics          | Motors |
  +---------------------+--------+
    | Camcorders |
    +------------+
    | Computers  |
    +------------+
    | TV Audio   |
    +------------+
  </code></pre>
  
  <p><strong>Repository method where i fetch all root categories:</strong></p>
  
  <pre><code>  public function getAllCategoriesByName()
      {
          return $this-&gt;getEntityManager()
              -&gt;createQuery(
                  'SELECT c FROM ISLabECommerceBundle:Category c WHERE c.parent IS NULL '
              )
              -&gt;getResult();
      }
  </code></pre>
  
  <p><strong>Controller :</strong></p>
  
  <pre><code>$em = $this-&gt;container-&gt;get('doctrine');
  $categories = $em-&gt;getRepository('ISLabECommerceBundle:Category')-&gt;getAllCategoriesByName();
  </code></pre>
  
  <p><strong>Twig:</strong></p>
  
  <pre><code>&lt;ul&gt;
  {% for category in categories %}
      &lt;li&gt;&lt;a href='#'&gt;&lt;span&gt;{{ category.name }}&lt;/span&gt;&lt;/a&gt;&lt;/li&gt;
  {% endfor %}
  &lt;/ul&gt;
  </code></pre>
  
  <p>Now i need to use JOIN to show all subcategories for category Electronics but i dont know to do that. I have low expiriance with join, left/right join. Any1 can give me example how to fetch all root and show subcategories for any1 root category.</p>
  
  <p><strong>Update:</strong></p>
  
  <p><strong>Entity</strong></p>
  
  <pre><code>&lt;?php
  
  namespace ISLab\Bundle\ECommerceBundle\Entity;
  
  use Doctrine\ORM\Mapping as ORM;
  use Doctrine\Common\Collections\ArrayCollection;
  /**
   * Category
   *
   * @ORM\Table(name="ecomerce_categories")
   * @ORM\HasLifecycleCallbacks()
   * @ORM\Entity(repositoryClass="ISLab\Bundle\ECommerceBundle\Repository\CategoryRepository")
   */
  class Category
  {
      /**
       * @var integer
       *
       * @ORM\Column(name="id", type="integer")
       * @ORM\Id
       * @ORM\GeneratedValue(strategy="AUTO")
       */
      private $id;
  
      /**
       * @var string
       *
       * @ORM\Column(name="name", type="string", length=255)
       */
      private $name;
  
      /**
       * @var integer
       *
       * @ORM\Column(name="image", type="integer")
       */
      private $image;
  
      /**
       * @var category
       *
       * @ORM\OneToMany(targetEntity="category", mappedBy="parent")
       */
      private $children;
  
      /**
       * @var ArrayCollection
       *
       * @ORM\ManyToOne(targetEntity="category", inversedBy="children")
       * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="CASCADE")
       */
      private $parent;
  
      /**
       * @var integer
       *
       * @ORM\Column(name="status", type="integer")
       */
      private $status;
  
      /**
       * @var string
       *
       * @ORM\Column(name="page_title", type="string", length=255)
       */
      private $pageTitle;
  
      /**
       * @var string
       *
       * @ORM\Column(name="meta_keywords", type="text")
       */
      private $metaKeywords;
  
      /**
       * @var string
       *
       * @ORM\Column(name="meta_description", type="text")
       */
      private $metaDescription;
  
      /**
       * @var string
       *
       * @ORM\Column(name="description", type="text")
       */
      private $description;
      /**
       * @var \DateTime
       *
       * @ORM\Column(name="date_created", type="datetime")
       */
      private $dateCreated;
  
      /**
       * @var \DateTime
       *
       * @ORM\Column(name="date_modified", type="datetime")
       */
      private $dateModified;
  
      /**
       * Constructor
       *
       * ArrayCollection
       */
      public function __construct()
      {
          $this-&gt;children = new ArrayCollection();
      }
  
      /**
       * To String
       *
       * @return string
       */
      public function __toString()
      {
          return (string) $this-&gt;name;
      }
  
      /**
       * Get id
       *
       * @return integer 
       */
      public function getId()
      {
          return $this-&gt;id;
      }
  
      /**
       * Set name
       *
       * @param string $name
       * @return Category
       */
      public function setName($name)
      {
          $this-&gt;name = $name;
  
          return $this;
      }
  
      /**
       * Get name
       *
       * @return string 
       */
      public function getName()
      {
          return $this-&gt;name;
      }
  
      /**
       * Set image
       *
       * @param integer $image
       * @return Category
       */
      public function setImage($image)
      {
          $this-&gt;image = $image;
  
          return $this;
      }
  
      /**
       * Get image
       *
       * @return integer 
       */
      public function getImage()
      {
          return $this-&gt;image;
      }
  
      /**
       * Set parent
       *
       * @param integer $parent
       * @return Category
       */
      public function setParent($parent)
      {
          $this-&gt;parent = $parent;
  
          return $this;
      }
  
      /**
       * Get parent
       *
       * @return integer 
       */
      public function getParent()
      {
          return $this-&gt;parent;
      }
  
      /**
       * Set status
       *
       * @param integer $status
       * @return Category
       */
      public function setStatus($status)
      {
          $this-&gt;status = $status;
  
          return $this;
      }
  
      /**
       * Get status
       *
       * @return integer 
       */
      public function getStatus()
      {
          return $this-&gt;status;
      }
  
      /**
       * Set pageTitle
       *
       * @param string $pageTitle
       * @return Category
       */
      public function setPageTitle($pageTitle)
      {
          $this-&gt;pageTitle = $pageTitle;
  
          return $this;
      }
  
      /**
       * Get pageTitle
       *
       * @return string 
       */
      public function getPageTitle()
      {
          return $this-&gt;pageTitle;
      }
  
      /**
       * Set metaKeywords
       *
       * @param string $metaKeywords
       * @return Category
       */
      public function setMetaKeywords($metaKeywords)
      {
          $this-&gt;metaKeywords = $metaKeywords;
  
          return $this;
      }
  
      /**
       * Get metaKeywords
       *
       * @return string 
       */
      public function getMetaKeywords()
      {
          return $this-&gt;metaKeywords;
      }
  
      /**
       * Set metaDescription
       *
       * @param string $metaDescription
       * @return Category
       */
      public function setMetaDescription($metaDescription)
      {
          $this-&gt;metaDescription = $metaDescription;
  
          return $this;
      }
  
      /**
       * Get metaDescription
       *
       * @return string 
       */
      public function getMetaDescription()
      {
          return $this-&gt;metaDescription;
      }
  
      /**
       * Set description
       *
       * @param string $description
       * @return Category
       */
      public function setDescription($description)
      {
          $this-&gt;description = $description;
  
          return $this;
      }
  
      /**
       * Get description
       *
       * @return string 
       */
      public function getDescription()
      {
          return $this-&gt;description;
      }
  
      /**
       * Set dateCreated
       *
       * @ORM\PrePersist
       *
       * @return Category
       */
      public function setDateCreated()
      {
          $this-&gt;dateCreated = new \DateTime();
  
          return $this;
      }
  
      /**
       * Get dateCreated
       *
       * @return \DateTime
       */
      public function getDateCreated()
      {
          return $this-&gt;dateCreated;
      }
  
      /**
       * Set dateModified
       *
       * @ORM\PrePersist
       * @ORM\PreUpdate
       *
       * @return Category
       */
      public function setDateModified()
      {
          $this-&gt;dateModified =  new \DateTime();
  
          return $this;
      }
  
      /**
       * Get dateModified
       *
       * @return \DateTime
       */
      public function getDateModified()
      {
          return $this-&gt;dateModified;
      }
  
  }
  </code></pre>