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.

Unpolled

Can you post more code. For instance, where you instantiate the query builder. – Posturize

I instantiate the query builder like: $query = $this->getModelManager()->createQuery(MyBundle:Company, 'entity'); – Unpolled

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 !

Lelalelah

I love this answer! Some more DQL Select Examples – Valley

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.

Margarito

Right, but it is any form to attemp against the join table?? Because the join table is not an entity at all, but also a relationship. :( – Unpolled

You can create an entity for the join table if you want to, then you set many to one relationships on that entity to the two tables that have a many to many relationship. This is helpful if you need to have another column on the many to many table. – Margarito

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

Boron