use IFNULL in DQL and symfony query

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'

lamik · Accepted Answer · 2016-10-28 22:33:39Z

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

answered Oct 28, 2016 at 22:33

lamik

991 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

use IFNULL in DQL and symfony query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related