Doctrine: Multiple (whereIn OR whereIn) query?

Question

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

Pend

Answer 1

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

Pend

Answer 2

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

Analysand

Attention, This is not compatible with Doctrine 2 ! – Phlegmy

This is relevant since this question is tagged doctrine and not doctrine2 – Vegetable