Doctrine how to create a query builder with an operation of two subqueries

Question

I am trying to create a query builder with doctrine with an query expression like the following:

WHERE a < ((VALUE FROM SUBQUERY 1) / (VALUE FROM SUBQUERY 2))

I've been able to accomplish a < (VALUE FROM SUBUQERY 1) but as soon I add the second sub query, doctrine throws an error of "Expected Literal, got 'SELECT'"

My database model is a Company entity related with a Sales Entity and a Quotations entity. I want to filter companies which the ratio quotation/sale is greater or lower of some value.

This is company entity:

#[ORM\Entity(repositoryClass: CompanyRepository::class)]
class Company 
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private ?int $id;

    #[ORM\OneToMany(mappedBy: 'company', targetEntity: Quotation::class, cascade: ['all'], orphanRemoval: true, fetch: 'EXTRA_LAZY')]
    private Collection $quotations;

    #[ORM\OneToMany(mappedBy: 'company', targetEntity: Sale::class, cascade: ['all'], orphanRemoval: true, fetch: 'EXTRA_LAZY')]
    private Collection $sales;
}

This is the query builder I am building:

$qb = $companyRepository->createQueryBuilder('item');

$qbInQuotation = $this->getEntityManager()->createQueryBuilder()
    ->from(Quotation::class, 'ratioQuotationMin')
    ->where('ratioQuotationMin.company = item')
    ->select('COUNT(ratioQuotationMin.id)');

$qbInSale = $this->getEntityManager()->createQueryBuilder()
    ->from(Sale::class, 'ratioSaleMin')
    ->where('ratioSaleMin.company = item')
    ->select('COUNT(ratioSaleMin.id)');

$qb->andWhere(
    $qb->expr()->lte(
        ':ratioMin',
        '(('.$qbInSale->getDQL().') / ('.$qbInQuotation->getDQL().'))'
    ))
    ->setParameter('ratioMin', $data->getQuotationsMax());

The resulting DQL is the following:

SELECT item 
FROM App\Entity\Company\Company item 
WHERE :ratioMin <= ((SELECT COUNT(ratioSaleMin.id) FROM App\Entity\Sales\Sale ratioSaleMin WHERE ratioSaleMin.company = item) / (SELECT COUNT(ratioQuotationMin.id) FROM App\Entity\Sales\Quotation ratioQuotationMin WHERE ratioQuotationMin.company = item)) 
ORDER BY item.name ASC

As an sql query, this is a correct query, but I think doctrine does not like the double parenthesis ((. But removing the double parenthesis it throws an error "Expected end of string, got '/'"

Doctrine how to create a query builder with an operation of two subqueries

0 Answers0