How to use WHERE IN with Doctrine 2

Question

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

I think this is the recommended way http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion — martin, Sep 16 '13 at 14:49

score 416 · Answer 1 · edited Oct 12 '15 at 10:47

416

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

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

edited Oct 12 '15 at 10:47

Wilt

41,477
12
152
203

answered Aug 08 '12 at 22:42

Maciej Pyszyński

9,266
3
25
28

41

Not only but starting from 2.1 – Maciej Pyszyński Aug 23 '12 at 10:33
7

@MaciejPyszyński +1. The easiest ways are often the best ones! – Andrzej Ośmiałowski Feb 06 '14 at 22:09
3

Quick mention: This works per default with ->setParameter('ids', $ids) but not with ->setParameters('ids' => $ids). Took me some minutes of debugging. – larrydahooster Sep 29 '15 at 14:59
4

to make is work with ->setParameters(...) ```->where('b.status IN (:statuses)') ->setParameters([ 'customerId' => $customerId, 'storeId' => $storeId, 'statuses' => [Status::OPEN, Status::AWAITING_APPROVAL, Status::APPROVED] ]);``` – George Mylonas Feb 09 '16 at 15:58
14

I would like to point out the importance of also passing the 3th parameter to `setParameter` to force `Connection::PARAM_STR_ARRAY` – Luc Wollants Oct 13 '17 at 11:46
@MaciejPyszyński that was my friend, extraordinary! – undefinedman Jan 04 '18 at 12:24
The easiest way for sure! – Vladimir Hraban Mar 16 '19 at 11:47
dislike cause i'll got something like this: `r.winner IN ('1, 2, 3, 4, 5')` – Vladimir Ch Apr 22 '19 at 19:02
@VladimirCh It won't just pass an array not a string – Maciej Pyszyński Apr 27 '19 at 05:44
@VladmirCh see the comment from @Luc Wollants. That will signal doctrine to format the parameter correctly `r.winner IN (1, 2, 3, 4, 5)` – Joe Devine Sep 30 '22 at 19:23

score 126 · Accepted Answer · answered Jun 12 '11 at 01:50

126

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.

answered Jun 12 '11 at 01:50

Buster Neece

1,292
1
8
3

5

I think `$qb->expr()->in()` is only in Doctrine 2 ORM, but not in Doctrine DBAL. – martin Sep 16 '13 at 14:48
4

`$qb->expr()->in()` is indeed in DBAL – JamesHalsall Nov 09 '15 at 10:38

score 79 · Answer 3 · answered May 21 '14 at 11:36

79

and for completion the string solution

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

answered May 21 '14 at 11:36

Daniel Espendiller

1,242
10
8

5

Can also use \Doctrine\DBAL\Connection::PARAM_INT_ARRAY if you have an array of integers not strings. – Omn Feb 25 '15 at 22:30
`\Doctrine\DBAL\Connection::PARAM_STR_ARRAY` is currently deprecated, use `\Doctrine\DBAL\ArrayParameterType::STRING` instead – Erik Apr 28 '23 at 07:47

score 27 · Answer 4 · answered May 10 '11 at 14:21

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

score 24 · Answer 5 · answered Mar 26 '18 at 15:00

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

score 11 · Answer 6 · answered Jan 24 '17 at 20:26

11

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

answered Jan 24 '17 at 20:26

Yes Barry

9,514
5
50
69

1

That is a perfectly acceptable way to do a where in without using DQL, but his question was in reference to his DQL code. He is doing more than just a simple give me all the things based on these IDs. – spetz83 Mar 31 '17 at 16:07

score 8 · Answer 7 · answered Feb 09 '16 at 16:02

This is how I used it:

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

ck1 · Answer 8 · 2013-10-31T01:01:57.500

7

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!

edited Oct 31 '13 at 01:01

answered Jul 16 '13 at 12:36

ck1

83
2
5

score 7 · Answer 9 · edited Apr 09 '20 at 03:30

7

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.

edited Apr 09 '20 at 03:30

Oscar Gallardo

2,240
3
27
47

answered Feb 29 '16 at 18:18

Calamity Jane

2,189
5
36
68

3

This solved my problem (the parentheses around `:userids` ) – Mihai Răducanu Sep 27 '16 at 14:01

score 3 · Answer 10 · edited Nov 25 '21 at 20:01

3

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

edited Nov 25 '21 at 20:01

miken32

42,008
16
111
154

answered Mar 25 '16 at 04:23

John Smith

1,848
3
13
24

score 2 · Answer 11 · edited Feb 08 '16 at 11:12

2

I prefer:

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

edited Feb 08 '16 at 11:12

jjmontes

24,679
4
39
51

answered Feb 08 '16 at 10:29

Nick

9,735
7
59
89

score 0 · Answer 12 · answered Feb 07 '17 at 07:49

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

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

score 0 · Answer 13 · answered Dec 06 '19 at 23:56

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.

score -1 · Answer 14 · answered Nov 25 '21 at 19:54

-1

$winnerIds = [1,3,5];
$qb->andWhere($qb->expr()->in('r.winner', ':winnerIds'))
   ->setParameter('winnerIds', $winnerIds)
;

answered Nov 25 '21 at 19:54

Serhii Vasko

383
4
11

How to use WHERE IN with Doctrine 2

14 Answers14

Linked

Related