How to JOIN without relational table in Symfony Doctrine with QueryBuilder between 2 entities

Question

I have an Entity Video related with a Entity Category and I need to run this SQL with Doctrine QueryBuilder, with this I can get the most used categories in all videos (1000+):

    SELECT c.*
    FROM Video v
    INNER JOIN video_category vc ON vc.video_id = v.id
    INNER JOIN Category c ON vc.category_id = c.id
    GROUP BY c.id
    HAVING COUNT(v.id) > 1000
    ORDER BY c.name ASC;

My querybuilder:

    $queryBuilder = $this->getEntityManager()
        ->createQueryBuilder()
        ->select('c')
        ->from('AcmeVideoBundle:Video', 'v')
        // Can Doctrine join itself silently with relational info in the Entities?
        ->join('AcmeCategoryBundle:Category', 'c', Expr\Join::WITH, 'v.id = c.id')
        ->groupBy('c.id')
        ->having('COUNT(v.id) > 1000')
        ->orderBy('c.name', 'ASC')
        ->getQuery();

But the SQL query output by queryBuilder is this:

    SELECT c0_.id AS id0, c0_.NAME AS name1 
    FROM Video v1_ 
    INNER JOIN Category c0_ ON (v1_.id = c0_.id) 
    GROUP BY c0_.id 
    HAVING COUNT(v1_.id) > 1000
    ORDER BY c0_.NAME ASC

Without the relational table (video_category)

The Entities mapping:

    /**
     * Video
     *
     * @ORM\Table
     * @ORM\Entity(repositoryClass="Acme\VideoBundle\Entity\VideoRepository")
     */
    class Video
    {
        /**
         * @ORM\Id
         * @ORM\Column(type="integer")
         * @ORM\GeneratedValue(strategy="AUTO")
         */
        private $id;

        /**
         * @ORM\ManyToMany(targetEntity="Acme\CategoryBundle\Entity\Category", cascade={"persist"})
         */
        private $category;

        // More fields, getters and setters etc...
    }

    /**
     * Category
     *
     * @ORM\Table
     * @ORM\Entity(repositoryClass="Acme\CategoryBundle\Entity\CategoryRepository")
     */
    class Category
    {
        /**
         * @ORM\Id
         * @ORM\Column(type="integer")
         * @ORM\GeneratedValue(strategy="AUTO")
         */
        private $id;

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

        // More fields, getters and setters etc...
    }

How can I use the relation table to run the original SQL query with doctrine Querybuilder? I missed something?

INFO: When I findBy{field}, persist, flush, clear on all entities works fine, the Doctrine relations are ok, I have a Video, Category and video_category tables fine, the original SQL query works perfect.

Answer 1

   // Can Doctrine join itself silently with relational info in the Entities?
    ->join('AcmeCategoryBundle:Category', 'c', Expr\Join::WITH, 'v.id = c.id')

Yes! In fact that is one of the major reasons for using an ORM such as Doctrine 2.

Try:

->leftJoin('v.category','c')

The manual goes into more details though oddly enough is does not seem to have a join example. Hence the common confusion.

http://docs.doctrine-project.org/en/latest/reference/query-builder.html

And you may not be aware of this but the United Nations has passed a resolution outlawing the use of abbreviations for aliases. Just to be safe, try:

$queryBuilder = $this->getEntityManager()
    ->createQueryBuilder()
    ->addSelect('category')
    ->from('AcmeVideoBundle:Video', 'video')
    ->leftJoin('video.category', 'category')
    ->groupBy('category.id')
    ->having('COUNT(video.id) > 1000')
    ->orderBy('category.name', 'ASC')
    ->getQuery();

Answer 2

Ok, solved, the problem was that the Entities isn't fully mapped for ManyToMany bidirectional relationship.

The Entities are now:

    class Video
    {
        /**
         * @var ArrayCollection
         *
         * @ORM\ManyToMany(targetEntity="Acme\CategoryBundle\Entity\Category", inversedBy="video") // Note the inversedBy key
         */
        private $category;
    }

    class Category
    {
        /**
         * @var Video
         *
         * @ORM\ManyToMany(targetEntity="Acme\VideoBundle\Entity\Video", mappedBy="category") // Again the inversed
         */
        private $video; // New field for bidirectional ManyToMany
    }

And the final QueryBuilder working (now with full alias version :P):

    $queryBuilder = $this->getEntityManager()
        ->createQueryBuilder()
        ->select('category')
        ->from('AcmeCategoryBundle:Category', 'category')
        ->join('category.video', 'video')
        ->groupBy('category.id')
        ->having('COUNT(video.id) > 1000')
        ->orderBy('category.name', 'ASC')
        ->getQuery();

Best regards