Query builder ManyToMany relationship
There's another method without using the join statement, you should just add the following code to extract the companies with the subcompanyId = 5
$query->where(':subCompanyId MEMBER OF c.ChildrenCompany'); $query->setParameter("subCompanyId", 5);
And doctrine will do the hard work for you . Have a nice coding time !
You should just be able to check on j.id:
$query->select ('c');$query->from(MyBundle:Company, 'c');$query->leftJoin('c.ChildrenCompany','j');$query->where('j.id = :subCompanyId'); $query->setParameter("subCompanyId", 5);
If you look at the error you were getting first time you can see that you have an instance of Company which means you should be querying on Company fields. When you join an alias you then write queries against the target entity, not the join table.
Do it like this
Aisel\PageBundle\Entity\Page:type: entitytable: aisel_pagerepositoryClass: Aisel\PageBundle\Entity\PageRepositoryid: id: type: integer id: true generator: strategy: AUTOfields: ...manyToMany: categories: targetEntity: Aisel\CategoryBundle\Entity\Category joinTable: name: aisel_page_category joinColumns: page_id: referencedColumnName: id inverseJoinColumns: category_id: referencedColumnName: id
And in your function
public function getPagesByCategory($categoryId){ $qb = $this->getEntityManager()->createQueryBuilder(); $categoryId = 291; // CategoryId $r = $qb->select('p') ->from('AiselPageBundle:Page', 'p') ->innerJoin('p.categories','c') ->where('p.status = 1') ->andWhere('p.isHidden != 1') ->andWhere('c.id = :categoryId')->setParameter('categoryId',$categoryId) ->getQuery() ->execute(); return $r;}