How to fetch data from many to many relation ship third table using symfony2 doctrine DQL

Question

1)Category Entity:

        /** 
    * Category 
    * 
    * @ORM\Table(name="category") 
    * @ORM\Entity(repositoryClass="AppBundle\Repository\CategoryRepository")
    */
    class Category {
    /**     
    * @var int   
    * @ORM\Column(name="id", type="integer")   
    * @ORM\Id    
    * @ORM\GeneratedValue(strategy="AUTO")    
    */   
     protected $id; 

      /** 
       *  @var string  
       *  @ORM\Column(name="categoryname", type="string")     
       */    
     protected $categoryname; 

     /** 
       * @ORM\ManyToMany(targetEntity="Tag", inversedBy="categories")    
       */   
     protected $tags;
       /**
        *  @return ArrayCollection     
        */ 
     public function __construct(){     
         $this->tags = new ArrayCollection();  
         }

2) Tag Entity:

    /** 
* Tag 
* @ORM\Table(name="tag") 
* @ORM\Entity(repositoryClass="AppBundle\Repository\TagRepository") 
*/ 
class Tag {
   /**    
   * @var int   
   * @ORM\Column(name="id", type="integer")   
  * @ORM\Id    
  * @ORM\GeneratedValue(strategy="AUTO")    
  *   
  */
protected $id;

/**   
 * 
 * @var string
 * 
 *@ORM\Column(name="tagname", type="string")     
 */  
protected $tagname;   

/** 
  *  @ORM\ManyToMany(targetEntity="Category", mappedBy="tags")     
  */    
protected $categories;
 /**   
  *  @return ArrayCollection     
  */
public function __construct(){     
    $this->categories = new ArrayCollection();   
    }

I am using below dql query: getting third table data form database,But i stuck to that getting third table data:

    $categoryId = $request->request->get('cat_id');  
        $repository = $em->getRepository('AppBundle:Tag');
        $tags = $repository->createQueryBuilder('t')
                    ->innerJoin('t.categories', 'c')
                    ->where('c.id = :category_id')
                    ->setParameter('category_id', $categoryId)
                    ->getQuery()->getResult();

How to get third table(category_tag) data from database using DQl query:

Thanks for advance...

Answer 1

I think you should add an * @JoinTable annotation the $tags attribute in the Category entity, like this:

 /** 
   * @ORM\ManyToMany(targetEntity="Tag", inversedBy="categories")
   * @ORM\JoinTable(name="category_tag")
   */   
 protected $tags;

Check documentation for Doctrine Many-toMany here: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#many-to-many-bidirectional.

If what you want the query to result is an array with the pairs of ids that have the many-to-may relation, then your query should look like this:

$tags = $repository->createQueryBuilder('t')
            ->select('c.id as categoryId, t.id as tagId')
            ->innerJoin('t.categories', 'c')
            ->where('c.id = :category_id')
            ->setParameter('category_id', $categoryId)
            ->getQuery()
            ->getResult();

Hope this helps! :)