Multiple conditions in EntityRepository Symfony2

Question

I want to add orWhere conditions to query builder. params will come from array. but I dont know how I can add count of orWhere statements equal to count array elements. Here is some code:

 public function selectRelatedTrips($assoc)
{
    $params = array('k' => 'Kiev');
    $query = $this
        ->getEntityManager()
        ->createQueryBuilder()
        ->select('t')
        ->from('VputiTripBundle:Trip', 't')
        ->where('t.startCity = :k');

    foreach ($assoc as $k => $v) {
         //            $query->orWhere('t.startCity = ' . $v);
        $query->where('t.startCity = :param' . $k);
        $params['param' . $k] = $v;
    }

    return $query->setParameters($params)
        ->setMaxResults(20)
        ->orderBy('t.id', 'desc')
        ->getQuery()
        ->getResult();
}

Can somebody help me please?

score 2 · Answer 1 · edited May 23 '17 at 12:25

You can use the in() function:

class AcmeRepository extends EntityRepository
{
    public function selectRelatedTrips($assoc)
    {
        $qb = $this->createQueryBuilder('e');

        $qb = $qb
            ->where(
                $qb->expr()->in('e.field2', array_keys($assoc))
            );

        return($qb->getQuery()->getResult());
    }
}

See How to use WHERE IN with Doctrine 2 and Doctrine2 Query Builder for references.

score 1 · Accepted Answer · answered Dec 15 '14 at 10:02

1

Try to build the query dinamically, based by your array:

$params = array('param1' => 123);

$query = $this
     ->createQueryBuilder('e')
     ->select('e')
     ->where('e.field = :param1')

foreach ($assoc as $k => $v) {
    $query->orWhere('e.field2 = :param'.$k);
    $params['param'.$k] = $v;
}

$query->setParameters($params);

answered Dec 15 '14 at 10:02

Cristian Bujoreanu

1,147
10
21

hm. strange. I have got the error: Invalid parameter number: number of bound variables does not match number of tokens – nowiko Dec 15 '14 at 10:55
1

The numbers of parameters don't match. Debug your sql and your array with parameters, to see if has the same length, or if the keys match – Cristian Bujoreanu Dec 15 '14 at 10:59
1

I think the problem is that `:param` is not unique. – A.L Dec 15 '14 at 11:56
Yes, I considered this, and I tried to avoid it by using $params['param'.$k]. But the problem could be "param1". (In the loop, the second step, $key will take 1 as value) – Cristian Bujoreanu Dec 15 '14 at 12:04
@Cristian Bujoreanu, yes, I found this too. But it didnt help. I update my question with actual code. – nowiko Dec 15 '14 at 12:11
@Cristian Bujoreanu, thatnks. I found my mitake. You are the best) – nowiko Dec 15 '14 at 12:21

Multiple conditions in EntityRepository Symfony2

2 Answers2