Write “NOT IN” in Doctrine Query Language

问题:

I have two tables company(id, ...) and company_has_wtax(company_id, ....). I need to get all companies that are not in company_has_wtax table. In raw SQL should be something like:

SELECT id FROM company LEFT JOIN (company_has_wtax) 
                       ON company.id = company_has_wtax.company_id

But I don't know how to build this on DQL for Doctrine, any help?

Added method to repository and call it from Controller

This is the code I made after the answer leave by @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 this is how I call it in my controller:

$em = $this->getDoctrine()->getManager();
$entities = $em->getRepository('RegisterCompanyBundle:Company')->findCompanyByDocument();

But I get this error:

ContextErrorException: Notice: Undefined variable: query in /var/www/html/kraken/src/Company/RegisterCompanyBundle/Entity/Repository/CompanyRepository.php line 40

Where line 40 is ->where($query->expr()->notIn('c.id', $q2->select('c2.company')->getDQL()).

This are the 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 a way to get this working but it's ugly to me and also something say me is not right doing in this way:

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->getEntityManager();

    return $em->getConnection()->fetchAll($sql);
}

回答1:

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 that we created query from a none related entity by reversedBy/mappedBy
We need to use IDENTIY for the specific field of the related table

回答2:

Try this :

$em = $this->getDoctrine()->getManager();

$query = $em->createQuery('SELECT * FROM YourBundle\Entity\Company c WHERE c.id NOT IN (SELECT c2.company_id FROM YourBundle\Entity\Company_has_wtax c2)');

$companies = $query->getResult(); // array of Company objects