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.

Did you try declaring the relation table like you did with your JOIN? — Thomas Potaire
– Thomas Potaire, Commented Feb 2, 2014 at 16:53
Yep: [Semantical Error] line 0, col 54 near 'video_category': Error: Class 'video_category' is not defined. — Tecnocat
– Tecnocat, Commented Feb 2, 2014 at 17:20

InvertedAcceleration · Accepted Answer · 2014-12-04 18:59:56Z

 // 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();

I love it the full name for alias, I put the short version for clarify the example in StackOverflow, is good known the people with the same point of view! In last hours I got this same Exception with other combination of ->select() and ->join() / innerJoin(): [Semantical Error] line 0, col -1 near 'SELECT category': Error: Cannot select entity through identification variables without choosing at least one root entity alias. I used your answered code :/

Tecnocat · Accepted Answer · 2014-02-02 19:51:04Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related