Query builder ManyToMany relationship

Angel picture Angel · Apr 15, 2013 · Viewed 24k times · Source

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

sami boussacsou picture sami boussacsou · Jul 10, 2015

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 !