Self JOIN query with Symfony entity

Question

Here's my table :

My Category entity (without getter/setter):

    /**
 * @var int
 *
 * @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 string
 *
 * @ORM\Column(name="slug", type="string", length=255, nullable=true)
 */
private $slug;

/**
 * @var int
 * @Gedmo\TreeLeft
 * @ORM\Column(name="lft", type="integer")
 */
private $lft;

/**
 * @var int
 * @Gedmo\TreeLevel
 * @ORM\Column(name="lvl", type="integer")
 */
private $lvl;

/**
 * @var int
 * @Gedmo\TreeRight
 * @ORM\Column(name="rgt", type="integer")
 */
private $rgt;

/**
 * @Gedmo\TreeRoot
 * @ORM\ManyToOne(targetEntity="Category")
 * @ORM\JoinColumn(name="root", referencedColumnName="id", onDelete="CASCADE")
 */
private $root;

/**
 * @Gedmo\TreeParent
 * @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
 * @ORM\JoinColumn(name="parent", referencedColumnName="id", onDelete="CASCADE")
 */
private $parent;

/**
 * @ORM\OneToMany(targetEntity="Category", mappedBy="parent")
 * @ORM\OrderBy({"lft" = "ASC"})
 */
private $children;

In my controller, if I do this :

    $category= $this->container->get('app.category.manager')->getCategoryBySlug($category_slug);
    $root = $term->getRoot();

Doctrine execute 2 queries, one for the category itself, and one for the root of the category. I would like to create my own repository function to join the 2 entities in one query. I've tried so many things with the query builder, now I'm completly lost.

chalasr chalasr · Accepted Answer · 2016-02-26T14:55:58

First, create a custom Repository class for your entity.

Then, paste the a method like the following into:

class CategoryRepository extends EntityRepository
{
    public function getRootByCategorySlug($slug)
    {
        return $this->getEntityManager()
            ->createQueryBuilder()
            ->from('YourBundle:Category', 'c')
            ->where('c.slug = :slug')
            ->setParameter('slug', $slug)
            ->leftJoin('c.root', 'r') // Join the association
            ->select('r') // Fetch the association only
            ->getQuery()
            ->getResult()
       ;
    }
}

And use it like follows:

$repo = $this->getDoctrine()->getManager()->getRepository('YourBundle:Category');
$rootCategory = $repo->getRootByCategorySlug('your_slug');

Self JOIN query with Symfony entity

1 Answers