The many-to-many query in Doctrine 2

Question

I am trying to figure out how to make a many-to-many query in Doctrine 2, and I cannot find the answer. I know for sure how to do this in direct SQL:

SELECT ma.id, ma.name FROM user u JOIN user_media_area uma ON uma.user_id = u.id JOIN media_area ma ON uma.media_area_id = ma.id 

How do I do the same with Doctrine?

Answer 1

There are some good examples in the manual: http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/query-builder.html

In your case, it will look something like this:

  // Build query $em = $this->getEntityManager(); $qb = $em->createQueryBuilder(); $qb->addSelect('user'); $qb->addSelect('mediaArea'); $qb->from('SomeBundle:User','user'); $qb->leftJoin('user.userMediaArea','userMediaArea'); $qb->leftJoin('userMediaArea.mediaArea','mediaArea'); $query = $qb->getQuery(); $users = $query->getResult(); echo $users[0]->getUserMediaArea()->getName(); 

You did not publish your entity code, so I had to figure out how you defined the relationship. If you just have a simple ManyToMany between User and MediaArea, you can skip the UserMediaArea connection. D2 will find out. And since you really need MediaArea information, I would actually cancel the request and make a choice from MediaArea, so user information will not need to be returned. But I tried to fulfill your initial request.

Answer 2

 SELECT ma.id, ma.name FROM User u JOIN u.media 

User is a user object, and u.media is a multimedia object.