Query builder ManyToMany relationship

Question

I'm facing some troubles with query builder in many to many relationship.

I have an entity Company which have subcompanies as well, so I have:

class Company {

private $id;

/**
* @var ArrayCollection
* 
* @ORM\ManyToMany(targetEntity="Company")
* @ORM\JoinTable(name="company_relation", 
*   joinColumns={ 
*     @ORM\JoinColumn(name="id", referencedColumnName="id")
*   },
*   inverseJoinColumns={ 
*     @ORM\JoinColumn(name="subcompany", referencedColumnName="id")
*   }
* )
*/    
private $ChildrenCompany;

[...]

}

Then I'm trying to make a query builder which returns companies which have determinated subcompanies (for example subcompany with id 5). I tried in two ways:

method A)

$query->select ('c');
$query->from(MyBundle:Company, 'c');
$query->leftJoin('c.ChildrenCompany','j');
$query->where('j.subcompany = 5'); 

It doesn't work and give me the error: Class Application\Sademer\CoreBundle\Entity\Company has no field or association named subcompany

method B)

$query->select ('c');
$query->from(MyBundle:Company, 'c');
$query->where('j.ChildrenCompany = 5'); 

It doesn't work and give me the error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Answer 1

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 !

Answer 2

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.

Answer 3

Do it like this

Aisel\PageBundle\Entity\Page:
type: entity
table: aisel_page
repositoryClass: Aisel\PageBundle\Entity\PageRepository
id:
    id:
        type: integer
        id: true
        generator:
            strategy: AUTO
fields:
   ...

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;
}