14

I'm having trouble crafting a fairly simple query with Doctrine...

I have two arrays ($countries, $cities) and I need to check whether database record values would match any inside either. I'm looking for something like:

->whereIn('country', 'city', $countries, $cities)

... with 'country' being a WHERE IN for $countries and 'city' being a WHERE IN for $city.

I could separate the two out but the needed query has lots of other conditions so that's not possible. The resulting SQL I'm after would be:

SELECT ... 
WHERE ... 
AND ...
AND ... 
AND ('country' IN (1,2,3) OR 'city' IN (7,8,9))
AND ... 
AND ...;

One could therefore think of it also as a bracketing issue only. Anyone know if this is possible with Doctrine DQL? I've looked through the documentation but can't find any direction.

Thanks

Tom
  • 30,090
  • 27
  • 90
  • 124

2 Answers2

40

After an hour of experimenting on this nonsense, here's the syntax to make it work.

$q->andWhere('country IN ? OR city IN ?', array(array(1, 2, 3), array(7, 8, 9)));
Tom
  • 30,090
  • 27
  • 90
  • 124
4

Why not use something like?

$countryIds=[1,2,3];
$cityIds=[7,8,9];

$q->whereIn('country',$countryIds)->andWhereIn('city',$cityIds);

Also, chain them together for context (most Doctrine methods return $this).

see http://www.symfony-project.org/doctrine/1_2/en/06-Working-With-Data

Jujhar Singh
  • 3,641
  • 5
  • 29
  • 38