title: Write "NOT IN" in Doctrine Query Language
body: <p>I have two tables <code>company(id, ...)</code> and <code>company_has_wtax(company_id, ....)</code>. I need to get all companies that are not in <code>company_has_wtax</code> table. In raw SQL should be something like:</p>
  
  <pre><code>SELECT id FROM company LEFT JOIN (company_has_wtax) 
                         ON company.id = company_has_wtax.company_id
  </code></pre>
  
  <p>But I don't know how to build this on DQL for Doctrine, any help?</p>
  
  <p><strong>Added method to repository and call it from Controller</strong></p>
  
  <p>This is the code I made after the answer leave by @Javad:</p>
  
  <pre><code>public function findCompanyByDocument() {
      $q2 = $this-&gt;createQueryBuilder('ApprovalBundle:CompanyHasWtax c2');
  
      $query = $this-&gt;createQueryBuilder('c')
              -&gt;leftJoin('c.CompanyHasWtax', 'chw')
              -&gt;where($query-&gt;expr()-&gt;notIn('c.id', $q2-&gt;select('c2.company')-&gt;getDQL())
      );
  
      echo $query-&gt;getQuery()-&gt;getSQL();
      // return $query-&gt;getQuery()-&gt;getResult();
  }
  </code></pre>
  
  <p>And this is how I call it in my controller:</p>
  
  <pre><code>$em = $this-&gt;getDoctrine()-&gt;getManager();
  $entities = $em-&gt;getRepository('RegisterCompanyBundle:Company')-&gt;findCompanyByDocument();
  </code></pre>
  
  <p>But I get this error:</p>
  
  <blockquote>
    <p>ContextErrorException: Notice: Undefined variable: query in
    /var/www/html/kraken/src/Company/RegisterCompanyBundle/Entity/Repository/CompanyRepository.php
    line 40</p>
  </blockquote>
  
  <p>Where line 40 is <code>-&gt;where($query-&gt;expr()-&gt;notIn('c.id', $q2-&gt;select('c2.company')-&gt;getDQL())</code>.</p>
  
  <p>This are the entities:</p>
  
  <p><code>Company.php</code></p>
  
  <pre><code>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;    
  }
  </code></pre>
  
  <p><code>CompanyHasWtax.php</code></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>Ugly solution</strong></p>
  
  <p>I found a way to get this working but it's ugly to me and also something say me is not right doing in this way:</p>
  
  <pre><code>public function findCompanyByDocument() {
      $sql = "SELECT * FROM company c WHERE c.id NOT IN (SELECT chm.company FROM company_has_media chm WHERE chm.company = c.id)";
      $em = $this-&gt;getEntityManager();
  
      return $em-&gt;getConnection()-&gt;fetchAll($sql);
  }
  </code></pre>