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- gt getEntityManager  - gt createQueryBuilder         qb- gt add  select    qb- gt expr  - gt count  r id           - gt add  from     My Entity Rating r        if   outcome      wins               qb- gt add  where    qb- gt expr  - gt in  r winner   array   1                if   outcome      fails              qb- gt add  where    qb- gt expr  - gt in  r loser   array   1                 qb- gt setParameter 1   ids        query    qb- gt getQuery          die  q        qb       return  query- gt getSingleScalarResult        Data  or  ids    Array        0    gt  566      1    gt  569      2    gt  571     DQL result   q   SELECT COUNT r id  FROM  My Entity Rating r WHERE r winner IN   1

User · Answer

The easiest way to do that is by binding the array itself as a parameter    queryBuilder- gt andWhere  r winner IN   ids                 - gt setParameter  ids    ids

User · Answer

I know it s an old post but may be helpful for someone  I would vote  amp  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- gt andWhere  foo field IN   ints       qb- gt setParameter  ints   array 1  2      Doctrine DBAL Connection  PARAM INT ARRAY     Tested for select delete in symfony 3 4  amp  doctrine-bundle  1 8

User · Answer

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- gt add  where    qb- gt expr  - gt in  r winner    ids      http   groups google com group doctrine-dev browse thread thread fbf70837293676fb

User · Answer

This is how I used it   - gt where  b status IN   statuses    - gt setParameters                    customerId    gt   customerId                   storeId       gt   storeId                   statuses      gt   Status  OPEN  Status  AWAITING APPROVAL  Status  APPROVED

User · Answer

This is years later  working on a legacy site    For the life of me I couldn t get the - gt andWhere   or - gt expr  - gt in   solutions working   Finally looked in the Doctrine mongodb-odb repo and found some very revealing tests   public function testQueryWhereIn         qb    this- gt dm- gt createQueryBuilder  Documents User       choices   array  a    b       qb- gt field  username  - gt in  choices      expected          username    gt     in    gt   choices           this- gt assertSame  expected   qb- gt 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

User · Answer

and for completion the string solution   qb- gt andWhere  foo field IN   string      qb- gt setParameter  string   array  foo    bar     Doctrine DBAL Connection  PARAM STR ARRAY

User · Answer

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- gt add  where    qb- gt expr  - gt 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- gt add  where    qb- gt expr  - gt 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

User · Answer

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- gt createQuery    SELECT users       FROM Entities User users       WHERE           users id IN   userids      - gt setParameters       array    userids      gt   userIds       The method setParameters will take the given array and implode it properly to be used in the    IN    statement

User · Answer

The best way doing this - especially if you re adding more than one condition - is    values   array          array of your values  qb- gt andWhere  where    qb- gt expr  - gt 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

User · Answer

- gt where  qb- gt expr  - gt in  foo bar     data                - gt setParameter  participants    data     Also works with    - gt andWhere  qb- gt expr  - gt in  foo bar     users                    - gt setParameter  data    data

User · Answer

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- gt getDoctrine  - gt getRepository  AppBundle RepoName     result    repo- gt findBy        id    gt   ids

User · Answer

I prefer    qb- gt andWhere  qb- gt expr  - gt in  t user role id         User  USER ROLE ID ADVERTISER      User  USER ROLE ID MANAGER

User · Answer

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  - gt 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       - gt createQuery       - gt where  name       John       - gt andWhereIn  image id    ids      - gt orderBy  date created ASC       - gt limit 100     q- gt execute

[php] How to use WHERE IN with Doctrine 2

Examples related to php

Examples related to doctrine-orm

Examples related to query-builder