Doctrine - How do you use a subquery column (in the SELECT clause and having condition)

Question

Hello i am trying to rewrite ZF query builder into doctrine ORM.

Here IS mine old Zend framework query.

$dependent = new Zend_Db_Expr('if(br.billing_rate = \'dependent\',(SELECT COUNT(*) FROM childcare_billing_rule_price AS p WHERE p.id < brp.id AND p.childcare_billing_rule_id = brp.childcare_billing_rule_id),0)');

$select = $this
    ->select()->setIntegrityCheck(false)
    ->from(array('brg' => 'childcare_billing_rule_group'), array())
    ->joinInner(array('br' => 'childcare_billing_rule'), 'br.id = brg.billing_rule_id', array('*', 'dependent' => $dependent))
    ->join(array('brp' => 'childcare_billing_rule_price'), 'br.id = brp.childcare_billing_rule_id', array('age_from', 'age_to', 'default_rate' => 'rate'))
    ->where('is_default != 1')
    ->where('br.billing_type in (\'' . implode('\',\'', array(self::BILLING_TYPE_PER_DAY, self::BILLING_TYPE_PER_HOUR, self::BILLING_TYPE_PER_UNLIMITED, self::BILLING_TYPE_PER_RESERVATION)) . '\')')
    ->where('brg.group_id IN (?)', $groupId)
    ->group('brp.id')
    ->having('dependent <= ?', $dependentLevel)
    ->order('dependent desc')
    ->order('brp.rate');

And here it is SQL row value

SELECT `br`.*, 
 if (br.billing_rate = 'dependent', 
    (SELECT COUNT(*) FROM childcare_billing_rule_price AS p WHERE p.id < brp.id AND p.childcare_billing_rule_id = brp.childcare_billing_rule_id),
 0) AS `dependent`, 
 `brp`.`age_from`, 
 `brp`.`age_to`, 
 `brp`.`rate` AS `default_rate` 

 FROM `childcare_billing_rule_group` AS `brg`

 INNER JOIN `childcare_billing_rule` AS `br` ON br.id = brg.billing_rule_id

 INNER JOIN `childcare_billing_rule_price` AS `brp` ON br.id = brp.childcare_billing_rule_id 
 WHERE (is_default != 1) 
 AND (br.billing_type in ('Per Day','Per Hour','Unlimited','Per Reservation')) 
 AND (brg.group_id IN (103)) 
 AND ((brp.age_from = 0 AND brp.age_to = 0) || 3.1666666666667 
 BETWEEN brp.age_from AND brp.age_to) 
 GROUP BY `brp`.`id` HAVING (dependent <= '0') 
 ORDER BY `dependentw` desc, 
 `brp`.`rate` ASC

I am not sure how to implement this sub query

 new Zend_Db_Expr('if(br.billing_rate = \'dependent\',(SELECT COUNT(*) FROM childcare_billing_rule_price AS p WHERE p.id < brp.id AND p.childcare_billing_rule_id = brp.childcare_billing_rule_id),0)');

My code so far

$subQuery = $subQueryBuilder
    ->select('if (br.billing_rate = 'dependent', 
(SELECT COUNT(*) FROM childcare_billing_rule_price AS p WHERE p.id < brp.id AND p.childcare_billing_rule_id = brp.childcare_billing_rule_id)')
    ->getSQL();

 $queryBuilder->select($alias)
    ->addSelect($subQuery)
    ->innerJoin(ChildcareBillingRule::class, 'br', Join::WITH, 'br.id = childcare_billing_rule_group.childcareBillingRule')
    ->innerJoin(ChildcareBillingRulePrice::class, 'brp', Join::WITH, 'br.id = brp.childcareBillingRule')
    ->where("br.billingType in (:billingTypes)")
    ->andWhere(  'br.isDefault != :isDefault')
    ->andWhere(  'childcare_billing_rule_group.groupId in (:groupId)')
    ->setParameter('groupId', [103])
    ->setParameter('isDefault', 1)
    ->setParameter('billingTypes', ['Per Hour', 'Per Reservation', 'Per Day', 'Unlimited'])
    ->having('dependent <= ?', $dependentLevel')
    ->groupBy('brp.id')
    ->orderBy('brp.rate') ;
    ->getQuery()->getSingleResult();

so i am trying to add sub query with addSelect but not sure is this the right way? Any Help or idea is appreciated? Tnx

Hello Ambulance, lada, I am not a SQL specialist but I did want to say ... your username is AWESOME. :) — ether_joe, Mar 03 '20 at 15:54

score 0 · Answer 1 · answered Mar 04 '20 at 22:25

in short instead of ->getSQL() you should use ->getDQL().

Yours code will be like:

$dql = $this->createQueryBuilder()
   ->select('count(id)')
   ->from({Class::FQDN}, {ALIAS})
   ->getDQL();
$result = $this->createQueryBuilder()
   ->select({SECOND_ALIAS})
   ->from({SecondClass::FQDN}, {SECOND_ALIAS})
   ->where((new \Expr())->gt('{SECOND_ALIAS}.cnt', $dql))
   ->getQuery()
   ->getResult();

Doctrine - How do you use a subquery column (in the SELECT clause and having condition)

1 Answers1