在原则中使用SQL IN语句过滤查询 [英] Filter query using SQL IN statement in doctrine

问题描述

一个分支可能有许多客户，一个客户可能与许多分支相关。所以这是一个多对多的关系。

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

分支：

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

客户：

<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.

这是我尝试过的：

  $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();

所以我的想法是使用IN语句。但这是行不通的。

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

错误：

致命错误：对象DateTime类无法转换为第48行的字符串
..Query\Expr\Func.php

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?

推荐答案

尝试在查询中添加联接：

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();

这篇关于在原则中使用SQL IN语句过滤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！