php 如何将 WHERE IN 与 Doctrine 2 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5929036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use WHERE IN with Doctrine 2
提问by Tjorriemorrie
I have the following code which gives me the error:
我有以下代码，它给了我错误：
Message: Invalid parameter number: number of bound variables does not match number of tokens
Code:
代码：
public function getCount($ids, $outcome)
{
if (!is_array($ids)) {
$ids = array($ids);
}
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->add('select', $qb->expr()->count('r.id'))
->add('from', '\My\Entity\Rating r');
if ($outcome === 'wins') {
$qb->add('where', $qb->expr()->in('r.winner', array('?1')));
}
if ($outcome === 'fails') {
$qb->add('where', $qb->expr()->in('r.loser', array('?1')));
}
$qb->setParameter(1, $ids);
$query = $qb->getQuery();
//die('q = ' . $qb);
return $query->getSingleScalarResult();
}
Data (or $ids):
数据（或 $ids）：
Array
(
[0] => 566
[1] => 569
[2] => 571
)
DQL result:
DQL 结果：
q = SELECT COUNT(r.id) FROM \My\Entity\Rating r WHERE r.winner IN('?1')
采纳答案by Buster Neece
In researching this issue, I found something that will be important to anyone running into this same issue and looking for a solution.
在研究这个问题时，我发现了一些对遇到同样问题并寻找解决方案的人来说很重要的东西。
From the original post, the following line of code:
从原始帖子中，以下代码行：
$qb->add('where', $qb->expr()->in('r.winner', array('?1')));
Wrapping the named parameter as an array causes the bound parameter number issue. By removing it from its array wrapping:
将命名参数包装为数组会导致绑定参数编号问题。通过从它的数组包装中删除它：
$qb->add('where', $qb->expr()->in('r.winner', '?1'));
This issue should be fixed. This might have been a problem in previous versions of Doctrine, but it is fixed in the most recent versions of 2.0.
这个问题应该被修复。这可能是 Doctrine 以前版本中的一个问题，但它在 2.0 的最新版本中得到了修复。
回答by Maciej Pyszyński
The easiest way to do that is by binding the array itself as a parameter:
最简单的方法是将数组本身绑定为参数：
$queryBuilder->andWhere('r.winner IN (:ids)')
->setParameter('ids', $ids);
回答by Daniel Espendiller
and for completion the string solution
并完成字符串解决方案
$qb->andWhere('foo.field IN (:string)');
$qb->setParameter('string', array('foo', 'bar'), \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);
回答by Jeremy Hicks
I found that, despite what the docs indicate, the only way to get this to work is like this:
我发现，尽管文档表明了什么，但让它工作的唯一方法是这样的：
$ids = array(...); // Array of your values
$qb->add('where', $qb->expr()->in('r.winner', $ids));
http://groups.google.com/group/doctrine-dev/browse_thread/thread/fbf70837293676fb
http://groups.google.com/group/doctrine-dev/browse_thread/thread/fbf70837293676fb
回答by Azhar Khattak
I know it's an old post but may be helpful for someone. I would vote & enhance @Daniel Espendiller answer by addressing the question asked in comments about ints
我知道这是一个旧帖子，但可能对某人有帮助。我会通过解决有关整数的评论中提出的问题来投票并增强@Daniel Espendiller 的回答
To make this work for int's in proper way, make sure the values in array are of type int, you can type cast to int before passing...
为了以正确的方式为 int 工作，请确保数组中的值是 int 类型，您可以在传递之前将类型转换为 int...
$qb->andWhere('foo.field IN (:ints)');
$qb->setParameter('ints', array(1, 2),
\Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
Tested for select/delete in symfony 3.4 & doctrine-bundle: 1.8
在 symfony 3.4 和学说包中测试选择/删除：1.8
回答by Yes Barry
I know the OP's example is using DQL and the query builder, but I stumbled upon this looking for how to do it from a controller or outside of the repository class, so maybe this will help others.
我知道 OP 的示例使用 DQL 和查询构建器，但我偶然发现了这个，寻找如何从控制器或存储库类之外执行此操作，因此也许这对其他人有帮助。
You can also do a WHERE INfrom the controller this way:
您还可以通过WHERE IN以下方式从控制器执行 a ：
// Symfony example
$ids = [1, 2, 3, 4];
$repo = $this->getDoctrine()->getRepository('AppBundle:RepoName');
$result = $repo->findBy([
'id' => $ids
]);
回答by ck1
The best way doing this - especially if you're adding more than one condition - is:
执行此操作的最佳方法 - 特别是如果您添加多个条件 - 是：
$values = array(...); // array of your values
$qb->andWhere('where', $qb->expr()->in('r.winner', $values));
If your array of values contains strings, you can't use the setParameter method with an imploded string, because your quotes will be escaped!
如果您的值数组包含字符串，则不能对内爆字符串使用 setParameter 方法，因为您的引号将被转义！
回答by George Mylonas
This is how I used it:
这是我使用它的方式：
->where('b.status IN (:statuses)')
->setParameters([
'customerId' => $customerId,
'storeId' => $storeId,
'statuses' => [Status::OPEN, Status::AWAITING_APPROVAL, Status::APPROVED]
]);
回答by Calamity Jane
Found how to do it in the year of 2016: https://redbeardtechnologies.wordpress.com/2011/07/01/doctrine-2-dql-in-statement/
在 2016 年找到了如何做：https: //redbeardtechnologies.wordpress.com/2011/07/01/doctrine-2-dql-in-statement/
Quote:
引用：
Here is how to do it properly:
以下是如何正确执行此操作：
$em->createQuery(“SELECT users
FROM Entities\User users
WHERE
users.id IN (:userids)”)
->setParameters(
array(‘userids' => $userIds)
);
The method setParameterswill take the given array and implode it properly to be used in the “IN” statement.
该方法setParameters将采用给定的数组并将其正确内爆以在“IN”语句中使用。
回答by mnv
I prefer:
我更喜欢：
$qb->andWhere($qb->expr()->in('t.user_role_id', [
User::USER_ROLE_ID_ADVERTISER,
User::USER_ROLE_ID_MANAGER,
]));
