Filter query using SQL IN statement in doctrine

Question

One branch may have many customers, a customer may be related to many branches. So this is a many to many relation.

Branch:

<many-to-many target-entity="Customer" inversed-by="branches" field="customers"/>

Customer:

<many-to-many field="branches" target-entity="Branch" mapped-by="customers"/>

Now I want to perform following query: Select all customers where customer's branch matches a given branch object.

This is what I tried:

  $branch = $em->getRepository('MyBundle:Branch')
               ->findOneById($bid);

  $qb->select(array('c'))
     ->from('MyBundle:Customer', 'c')
     ->where($qb->expr()->in('c.branches', $branch))
     ->andWhere('c.delted = 0')
     ->getQuery();

So my idea was to use IN statement. But this does not work.

Error:

Fatal error: Object of class DateTime could not be converted to string ..Query\Expr\Func.php on line 48

Any ideas how to do this the right way?

Answer 1

Try add join in your query:

$qb->select(array('c', 'b'))
    ->from('MyBundle:Customer', 'c')
    ->join('c.branches', 'b')
    ->where('b IN (:branch)')
    ->andWhere('c.deleted = 0')
    ->setParameter('branch', array($branch))
    ->getQuery();