php - Write "NOT IN" in Doctrine Query Language -

i have 2 tables company(id, ...) , company_has_wtax(company_id, ....). need companies not in company_has_wtax table. in raw sql should like:

select id company left join (company_has_wtax)                         on company.id = company_has_wtax.company_id 

but don't know how build on dql doctrine, help?

added method repository , call controller

this code made after answer leave @javad:

public function findcompanybydocument() {     $q2 = $this->createquerybuilder('approvalbundle:companyhaswtax c2');      $query = $this->createquerybuilder('c')             ->leftjoin('c.companyhaswtax', 'chw')             ->where($query->expr()->notin('c.id', $q2->select('c2.company')->getdql())     );      echo $query->getquery()->getsql();     // return $query->getquery()->getresult(); } 

and how call in controller:

$em = $this->getdoctrine()->getmanager(); $entities = $em->getrepository('registercompanybundle:company')->findcompanybydocument(); 

but error:

contexterrorexception: notice: undefined variable: query in /var/www/html/kraken/src/company/registercompanybundle/entity/repository/companyrepository.php line 40

where line 40 ->where($query->expr()->notin('c.id', $q2->select('c2.company')->getdql()).

this entities:

company.php

class company {      /**      * @orm\id      * @orm\column(type="integer", unique=true, nullable=false)      * @orm\generatedvalue      *       */     protected $id;      /**      * @orm\column(name="legal_name",type="string",nullable=false,length=255)      * @assert\notblank(message="este valor no debería estar vacío.")      * @assert\regex("/^[a-za-z0-9ñÑÁÉÍÓÚñáéíóú\s\,]+$/", message="este valor debería ser de tipo alfanumérico.")      */     protected $legalname;      /**      * @orm\column(name="social_reason",type="string",nullable=false,length=255)      * @assert\notblank(message="este valor no debería estar vacío.")      * @assert\regex("/^[a-za-z0-9ñÑÁÉÍÓÚñáéíóú\s\,]+$/", message="este valor debería ser de tipo alfanumérico.")      */     protected $socialreason;      /**      * @orm\column(name="tax_id",type="string",nullable=false)      * @assert\notblank(message="este valor no debería estar vacío.")      * @assert\regex("/^[\d{9}$]/", message="este valor debería tener exactamente 9 caracteres.")      */     protected $taxid;      /**      * @orm\onetoone(targetentity="common\mediabundle\entity\media")      * @orm\joincolumn(name="logo", referencedcolumnname="id")      */     protected $logo;      /**      * @orm\onetoone(targetentity="common\mediabundle\entity\media")      * @orm\joincolumn(name="banner", referencedcolumnname="id")      */     protected $banner;      /**      * @orm\onetoone(targetentity="company\registercompanybundle\entity\ncompanytype")      * @orm\joincolumn(name="type", referencedcolumnname="id")      */     protected $type;      /**      * @orm\onetoone(targetentity="company\registercompanybundle\entity\ncompanystatus")      * @orm\joincolumn(name="status", referencedcolumnname="id")      */     protected $status;      /**      * @orm\column(type="integer",nullable=false,length=1)      */     protected $certified;     } 

companyhaswtax.php

class companyhaswtax {      /**      * @orm\id      * @orm\manytoone(targetentity="\company\registercompanybundle\entity\company")      * @orm\joincolumn(name="company", referencedcolumnname="id")      */     protected $company;      /**      * @orm\id      * @orm\manytoone(targetentity="\configuration\feebundle\entity\fee")      * @orm\joincolumn(name="wtax", referencedcolumnname="id")      */     protected $wtax;      /**      * @orm\id      * @orm\manytoone(targetentity="\user\securitybundle\entity\user")      * @orm\joincolumn(name="kuser", referencedcolumnname="id")      */     protected $user;      /**      * @orm\column(name="from_date", type="datetime")      */     protected $from_date;      /**      * @orm\column(name="to_date", type="datetime")      */     protected $to_date;  } 

ugly solution

i found way working it's ugly me , me not right doing in way:

public function findcompanybydocument() {     $sql = "select * company c c.id not in (select chm.company company_has_media chm chm.company = c.id)";     $em = $this->getentitymanager();      return $em->getconnection()->fetchall($sql); } 

try this:

$q2 = $this->createquerybuilder('c')     ->select('identity(c2.company)')     ->join('registercompanybundle:companyhasmedia', 'c2', 'with', 'c2.company = c.id');  $query = $this->createquerybuilder('c3'); $query->where($query->expr()->notin('c3.id', $q2->getdql()));  $companies = $query->getquery()->getresult(); 

please pay attention created query none related entity reversedby/mappedby
we need use identiy specific field of related table