Query builder join on ManyToMany relationship

Question

I am trying to use query builder to select all the categories that belong to a certain superCategory (category and superCategory have a many to many relationship).

However, I am unable to build a correct query builder sentence because I don't know how to reference to the superCategory from my categoryas there is no superCategory field inside my category ID.

The objects in the database look like :

Category:   id   name  SuperCategory   id   name  categories_superCategories   id   category_id   superCategory_id 

Here are the definition of my objects (yml files):

YOP\YourOwnPoetBundle\Entity\TraitCategory:   type: entity   repositoryClass: YOP\YourOwnPoetBundle\Repository\TraitCategoryRepository   table: null   fields:     id:       type: integer       id: true       generator:         strategy: AUTO     name:       type: string       length: '255'   lifecycleCallbacks: {  }   manyToMany:     superCategories:       targetEntity: SuperCategory       joinTable:         name: traitCategories_superCategories         joinColumns:           traitCategory_id:             referencedColumnName: id         inverseJoinColumns:           superCategory_id:             referencedColumnName: id 

and

YOP\YourOwnPoetBundle\Entity\SuperCategory:   type: entity   repositoryClass: YOP\YourOwnPoetBundle\Repository\SuperCategoryRepository   table: null   fields:     id:       type: integer       id: true       generator:         strategy: AUTO     name:       type: string       length: '255'   lifecycleCallbacks: {  }   manyToMany:     msgCategories:       targetEntity: MsgCategory       mappedBy: superCategories     traitCategories:       targetEntity: TraitCategory       mappedBy: superCategories 

How would I build a query builder sentence to get the categories that belong to a certain superCategory?

The query inside my CategoryRepository:

$this->createQueryBuilder('c')             ->innerJoin( ?????? )             ->setParameter('superCategoryName', $superCategoryName); 

Answer 1

Got it :

public function findBySuperCategoryName($superCategoryName) {     return $this->createQueryBuilder('c')             ->innerJoin('c.superCategories', 's', 'WITH', 's.name = :superCategoryName')             ->setParameter('superCategoryName', $superCategoryName); } 

The problem was that I had to ask for c.superCategories and not c.superCategory !

Answer 2

Something like:

$this->createQueryBuilder()         ->select('s')         ->from('SuperCategory', 's')         ->innerJoin('s.Category c ON c.category_id = s.superCategory_id')         ->where('s.name = :superCategoryName')         ->setParameter('superCategoryName', $superCategoryName)         ->getQuery()         ->getResult();