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);
}
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With