Building dynamic Doctrine query

Question

I'm trying to build filter for my database. Based on values of several form inputs I need to construct appropriate query. This is the code:

    $qb = $em->createQueryBuilder();
            $qb->select('c.title')
            ->from('Baza\BlogBundle\Entity\Conferences', 'c');         
    $conditions=array(
        $qb->expr()->gt('c.title',$enquiry->getNaziv()) );//First condition 

    if ($enquiry->getKontakt()!=null) {
         $conditions[] = $qb->expr()->lt('c.contactemail',$enquiry->getKontakt())}//Adding second, optional condition

    $conditions = call_user_func_array(array($qb, 'andX'), $conditions);
    $qb->where($conditions);
    $query = $qb->getQuery();

When I run this I get folowing exception:

Warning: call_user_func_array() expects parameter 1 to be a valid callback, class 'Doctrine\ORM\QueryBuilder' does not have a method 'andX'

*EDIT After call_user_func_array() modification I get following error:

QueryException: SELECT c.title FROM Baza\BlogBundle\Entity\Conferences c WHERE c.title > ITRO AND c.contactemail < [email protected]

Obviously, query is not formatted quite right. I was following this post:Doctrine DQL conditional query

Any thoughts?

Answer 1

I built dynamic query with the following code:

$em = $this->getDoctrine()->getEntityManager();

            $naziv = $enquiry->getNaziv();
            $drzava=$enquiry->getDrzava();
            $oblast=$enquiry->getOblast();
            $grad=$enquiry->getGrad();
            $provincija=$enquiry->getProvincija();
            $kontakt=$enquiry->getKontakt();
            $sajt=$enquiry->getSajt();

            $testquery = "SELECT c.title FROM Baza\BlogBundle\Entity\Conferences c JOIN c.topicid t JOIN c.locationid l JOIN l.stateid s WHERE c.lh = :lh";
            $testparam ['lh'] = $lh;

            if ($kontakt != '') {
                    $testquery .= " and c.contactemail = :kontakt";
                    $testparam['kontakt'] = $kontakt;
            }
            if ($drzava != '') {
                    $testquery .= " and s.name = :drzava";
                    $testparam['drzava'] = $drzava;
            }

            if ($provincija != '') {
                    $testquery .= " and l.province = :provincija";
                    $testparam['provincija'] = $provincija;
            }

             if ($grad != '') {
                    $testquery .= " and l.city = :grad";
                    $testparam['grad'] = $grad;
            }

             if ($sajt != '') {
                    $testquery .= " and c.siteurl = :sajt";
                    $testparam['sajt'] = $sajt;
            }

             if ($oblast != '') {
                    $testquery .= " and t.name = :oblast";
                    $testparam['oblast'] = $oblast;
            }

            $query = $em->createQuery($testquery)->setParameters($testparam);
            $result = $query->getResult();

Answer 2

1. andx is all lowercase

2. andx is a function of expr(), not of query builder:

   $conditions = call_user_func_array(array($qb->expr(), 'andx'), $conditions);

Answer 3

Regarding your edit:

$qb = $em->createQueryBuilder();
            $qb->select('c')
            ->from('Baza\BlogBundle\Entity\Conferences', 'c');

Use doctrine (as an ORM) to select the entire object. You can still where clause on the fields. Also gt and lt search on strings are not really proper sql practice. They will likely produce odd results with improper letter case.

Answer 4

Years later here it is how I do it:

    if (array_key_exists('phone', $filters)) {
        $qb->andWhere('p.phone LIKE :phone')
        ->setParameter(':phone', '%' . $filters['phone'] . '%');
    }

Hope this helps to someone