How to ignore a where statement if the parameter is null in Doctrine?

Question

I have a problem with the QueryBuilder in Doctrine. I wrote a query that has 2 parameter and they affect the where statement. I want to ignore the where statement if the related parameter was null. For example, if $play = 3 and $theater = null, the query must return all tickets with play 3 and whatever theater.

This is my current code:

public function getAllSearchedTickets($play,$teater){
    return $this->getEntityManager()->createQuery('
        select s from mtadminBundle:ReserveLocation s
        join s.reserve a
        join a.sance b
        where a.acceptCode != 0
        and b.play = :play 
        and b.teater = :teater')
        ->setParameters(array('play'=>$play,'teater'=>$teater))->getResult();
}

thank you.

Answer 1

You should use the QueryBuilder for this, to do it more efficiently, I'll show you how you do yours and then the same with the QueryBuilder as example:

Yours:

public function getAllSearchedTickets($play,$teater){
    $query = 'select s from mtadminBundle:ReserveLocation s'.
        'join s.reserve a'.
        'join a.sance b'.
        'where a.acceptCode != 0');

    $paramArray = array();
    if( $play ) {
        $query .= ' and b.play = :play';
        $paramArray['play'] = $play;
    }
    if( $teater ) {
        $query .= ' and b.teater = :teater';
        $paramArray['teater '] = $teater;
    }

    return $this->getEntityManager()->createQuery($query)
        ->setParameters($paramArray)->getResult();
}

QueryBuilder:

public function getAllSearchedTickets($play,$teater){
    $queryBuilder = $this->getEntityManager()->createQueryBuilder();
    $queryBuilder->select('s')
        ->from('mtadminBundle:ReserveLocation', 's')
        ->join('s.reserve', 'a')
        ->join('a.sance', 'b')
        ->where('a.acceptCode != 0');

    if( $play ) {
        $queryBuilder->andWhere('b.play = :play');
        $queryBuilder->setParameter('play', $play);
    }
    if( $teater ) {
        $queryBuilder->andWhere('b.teater = :teater');
        $queryBuilder->setParameter('teater', $teater);
    }

    return $queryBuilder->getResult();
}