在DQL和symfony查询中使用IFNULL

Question

subquery and querybuilder in my project but when use ```IFNULL`` command in query return eroor my code is bellow

  $subQb = $em->createQueryBuilder();
            $subquery = $subQb->select('COUNT(v.id)')
                ->from('AdminBundle:Visitsite', 'v')
                ->where('v.site = s.id')
                ->Andwhere('v.createdate > :date')
                ->getDQL();

            $subQb2 = $em->createQueryBuilder();
            $subquery2 = $subQb2->select('l.quantity')
                ->from('AdminBundle:Limitviewday', 'l')
                ->where($subQb2->expr()->eq('s.limitviewday', 'l.id'))
                ->getDQL();


            $subQb3 = $em->createQueryBuilder();
            $subquery3 = $subQb3->select('COUNT(i.id)')
                ->from('AdminBundle:Visitsite', 'i')
                ->where('i.id = s.id')
                ->Andwhere('i.createdate > :date2')
                ->Andwhere('i.ip = :ip')
                ->groupBy('i.ip')
                ->getDQL();

            $subQb4 = $em->createQueryBuilder();
            $subquery4 = $subQb4->select('ipl.quantity')
                ->from('AdminBundle:Iplimitview', 'ipl')
                ->where('s.iplimitview = ipl.id')
                ->getDQL();


            $qb = $em->createQueryBuilder();
            $query = $qb->select('s')
                ->from('AdminBundle:Sites', 's')
                ->where('s.quantity > :one')
                ->Andwhere('s.status = :two')
                ->Andwhere($qb->expr()->lt("($subquery)", "($subquery2)"))
                ->Andwhere($qb->expr()->lt("(SELECT IFNULL(  ($subquery3),0) )", "($subquery4))"))
                ->setParameter('one', 1)
                ->setParameter('two', 1)
                ->setParameter('date', $date->format('Y-m-d'))
                ->setParameter('ip', $ip)
                ->setParameter('date2', $date->format('Y-m-d 00:00:00'));

and my result is

[Syntax Error] line 0, col 274: Error: Expected known function, got 'IFNULL'

Answer 1

Doctrine has limited set of mapped functions, and seems it has no IFNULL by default.

The simple way is to change your DQL and replace IFNULL with IF or COALESCE.

The more difficult way is read manual and implement your own IFNULL mapping like this