Write "NOT IN" in Doctrine Query Language
Solution 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
Solution 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
ReynierPM
A passionate programmer and web developer with a background in front-end and back-end development, which is what he's been doing for over eight years. I had experience in web development using PHP (mostly), MySQL and JavaScript. I follows two major principles everyday work: beauty and simplicity. I believes everyone should learn something new every day. While I'm not working, I spends time coding personal projects, learning, watching screen casts, blogging, etc. Some specific areas of interest for me include cloud computing and anything related to web development among other like system and database administration.
Updated on June 04, 2022Comments
-
ReynierPM almost 2 years
I have two tables
company(id, ...)
andcompany_has_wtax(company_id, ....)
. I need to get all companies that are not incompany_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); }