How to use WHERE IN with Doctrine 2
PhpDoctrine OrmQuery BuilderPhp Problem Overview
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):
Array
(
[0] => 566
[1] => 569
[2] => 571
)
DQL result:
q = SELECT COUNT(r.id) FROM \My\Entity\Rating r WHERE r.winner IN('?1')
Php Solutions
Solution 1 - Php
The easiest way to do that is by binding the array itself as a parameter:
$queryBuilder->andWhere('r.winner IN (:ids)')
->setParameter('ids', $ids);
Solution 2 - Php
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.
Solution 3 - Php
and for completion the string solution
$qb->andWhere('foo.field IN (:string)');
$qb->setParameter('string', array('foo', 'bar'), \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);
Solution 4 - Php
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
Solution 5 - Php
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
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...
$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
Solution 6 - Php
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.
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
]);
Solution 7 - Php
This is how I used it:
->where('b.status IN (:statuses)')
->setParameters([
'customerId' => $customerId,
'storeId' => $storeId,
'statuses' => [Status::OPEN, Status::AWAITING_APPROVAL, Status::APPROVED]
]);
Solution 8 - Php
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!
Solution 9 - Php
Found how to do it in the year of 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 setParameters
will take the given array and implode it properly to be used in the “IN” statement.
Solution 10 - Php
I prefer:
$qb->andWhere($qb->expr()->in('t.user_role_id', [
User::USER_ROLE_ID_ADVERTISER,
User::USER_ROLE_ID_MANAGER,
]));
Solution 11 - Php
$qb->where($qb->expr()->in('r.winner', ':ids'))
->setParameter('ids', $ids);
Also works with:
$qb->andWhere($qb->expr()->in('r.winner', ':ids'))
->setParameter('ids', $ids);
Solution 12 - Php
I struggled with this same scenario where I had to do a query against an array of values.
The following worked for me:
->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();
Solution 13 - Php
This is years later, working on a legacy site... For the life of me I couldn't get the ->andWhere()
or ->expr()->in()
solutions working.
Finally looked in the Doctrine mongodb-odb repo and found some very revealing tests:
public function testQueryWhereIn()
{
$qb = $this->dm->createQueryBuilder('Documents\User');
$choices = array('a', 'b');
$qb->field('username')->in($choices);
$expected = [
'username' => ['$in' => $choices],
];
$this->assertSame($expected, $qb->getQueryArray());
}
It worked for me!
You can find the tests on github here. Useful for clarifying all sorts of nonsense.
Note: My setup is using Doctrine MongoDb ODM v1.0.dev as far as i can make out.
Solution 14 - Php
$winnerIds = [1,3,5];
$qb->andWhere($qb->expr()->in('r.winner', ':winnerIds'))
->setParameter('winnerIds', $winnerIds)
;