1

How do I build a query so that I can parameterize multiple fields in the QueryBuilder like this:

$qb->andWhere('(a.field1, a.field2) IN ((1, 1), (2, 2))');

The above example gives me an error:

Doctrine\ORM\Query\QueryException : [Syntax Error] line 0, col 84: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

I'm using Doctrine 2.6.1.

Aimee
  • 316
  • 2
  • 12
  • Possible duplicate of [Symfony2 Doctrine querybuilder where IN](https://stackoverflow.com/questions/16255070/symfony2-doctrine-querybuilder-where-in) – ficuscr May 29 '18 at 21:36
  • Or, https://stackoverflow.com/questions/18536190/doctrine-2-where-in-clause-using-a-collection-of-entities Oh, and that's really about WHERE IN, trying to do two in one go like that simply won't work. Use `where->(field1..., andWhere->(field2...` – ficuscr May 29 '18 at 21:37
  • This is not a duplicate question. The possible duplicate question does not address multiple corresponding fields. – Aimee May 29 '18 at 22:02

1 Answers1

0

Think the main part about how to use WHERE IN has been answered many times.

Trying to combine two criteria like that won't work. Same way this SQL would never work:

select * from users where (id, email) IN ((1, 2), ('foo@gmail.com', 'bar@gmail.com'));

You would write that as

select * from users where id IN(1,2) AND email IN('foo@gmail.com', 'bar@gmail.com');

As of 2.5 I believe you would want:

$qb->andWhere('a.field1 IN (:fieldOneValues)')
  ->andWhere('a.field2 IN (:fieldTwoValues)')
  ->setParameter('fieldOneValues', $fieldOneValues)
  ->setParameter('fieldTwoValues', $fieldTwoValues);

UPDATE:

Now I understand better. The SQL I shared shows I didn't understand that syntax. How many values are you testing against? Could you approach it like?

(a.field1 = 1 AND a.field2 = 1) OR (a.field1 = 2 AND a.field2 = 2)

Doing an explain on the query it actually seemed like MySQL leveraged keys much better with this method.

ficuscr
  • 6,975
  • 2
  • 32
  • 52
  • That query could create unexpected results though. Consider these two queries, which would produce different results: "select * from tbl WHERE (a, b) IN ((2, 1), (1, 2))" versus "select * from tbl WHERE a in (2, 1) AND b in (1, 2)" – Aimee May 29 '18 at 22:01
  • I'm confused. That first one seems like invalid syntax. Are you looking for `OR` as opposed to `AND`? You understand grouping with parentheses? – ficuscr May 29 '18 at 22:02
  • Wow, looking at https://stackoverflow.com/questions/44706196/mysql-multiple-columns-in-in-clause Swear I got error about "two operands" when I tested it. Well learn something new every day. Can say in over 20 years of development I've never made use of that. – ficuscr May 29 '18 at 22:04
  • Yeah, I'm trying to get Doctrine to work with multiple columns for IN's so that it doesn't return unexpected results. :D – Aimee May 29 '18 at 22:05
  • Doctrine isn't gonna play well with that. I'd just write raw SQL and use `RSM` if needing actual entities to be returned. – ficuscr May 29 '18 at 22:07
  • I may just go w/ https://stackoverflow.com/a/44706402/6950851 and translate it to DQL. – Aimee May 29 '18 at 22:07
  • 1
    I'm still trying to wrap my head around that logic... both fields being in both sets right? Not working like I would expect. Sorry this answer is not helpful. Good luck! ... oh I get it now... and why I got the operand error, not what I anticipated at first – ficuscr May 29 '18 at 22:13