如何用在什么地方与学说2

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')

12 个解决方案

#1

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中已经解决了这个问题。

#2

279

The easiest way to do that is by binding the array itself as a parameter:

最简单的方法是将数组本身绑定为参数:

$queryBuilder->andWhere('r.winner IN (:ids)')
             ->setParameter('ids', $ids);

#3

and for completion the string solution

为了完成字符串的解决方案

$qb->andWhere('foo.field IN (:string)');
$qb->setParameter('string', array('foo', 'bar'), \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

#4

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

#5

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方法，因为您的引号将被转义!

#6

This is how I used it:

我就是这么用的:

->where('b.status IN (:statuses)')
->setParameters([
                'customerId' => $customerId,
                'storeId'    => $storeId,
                'statuses'   => [Status::OPEN, Status::AWAITING_APPROVAL, Status::APPROVED]
            ]);

#7

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/07/2/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 setParameters will take an array and implode it properly to be used in the “IN” statement.

setParameters将获取一个数组并将其内爆，以便在“in”语句中使用。

#8

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和query builder，但我无意中发现了如何从控制器或存储库类之外执行此操作，因此这可能会对其他人有所帮助。

You can also do a WHERE IN from the controller this way:

你也可以从控制器中这样做:

// Symfony example
$ids    = [1, 2, 3, 4];
$repo   = $this->getDoctrine()->getRepository('AppBundle:RepoName');
$result = $repo->findBy([
    'id' => $ids
]);

#9

I prefer:

我喜欢:

$qb->andWhere($qb->expr()->in('t.user_role_id', [
    User::USER_ROLE_ID_ADVERTISER,
    User::USER_ROLE_ID_MANAGER,
]));

#10

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

我知道这是一个古老的帖子，但可能对某些人有帮助。我将通过在关于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类型的，您可以在传递之前将cast输入到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 & doctrine-bundle中测试选择/删除:1.8

#11

->where($qb->expr()->in('foo.bar', ':data'))
            ->setParameter('participants', $data);

Also works with:

也适用于:

 ->andWhere($qb->expr()->in('foo.bar', ':users'))
                ->setParameter('data', $data);

#12

I struggled with this same scenario where I had to do a query against an array of values.

我遇到了同样的情况，我必须对一个值数组进行查询。

The following worked for me:

以下是我的工作经验:

http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/dql-doctrine-query-language.html#where-clause

http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/dql-doctrine-query-language.html where子句

->andWhereIn("[fieldname]", [array[]])

Array data example (worked with strings and integers):

数组数据示例(使用字符串和整数):

$ids = array(1, 2, 3, 4);

Query example (Adapt to where you need it):

查询示例(适用于您需要的地方):

$q = dataTable::getInstance()
    ->createQuery()
    ->where("name = ?",'John')
    ->andWhereIn("image_id", $ids)
    ->orderBy('date_created ASC')
    ->limit(100);

$q->execute();

#1