Doctrine: Complex JOIN problem

Question

I have many-2-many relation between Property and Feature. I have to extract results from Property table, but only if both or more Features are related.

Ie. Property #1 is related to 'Swimming Pool' and 'Garden'. Property #2 is related only to 'Swimming Pool'. So when I ask for properties that has both 'Swimming Pool' and 'Garden', it should return me only Property #1.

I tried EVERY possible combinations like complex 'where()', 'whereIn', 'join... with', 'exists()' etc. but can't solve it. It either returns me all properties or none, I can't really remember all the things I have tried. Please, help, I have wasted 8 hours and it is killing me

Answer 1

So, in other words, you want to select all properties that are related to multiple specific features.

Try:

function findPropertyForFeatures($features)
{
  $q = Doctrine_Query::create()->from('Property p');
  $i = 0;
    foreach ($features as $f) 
    {
      $i++;
      $q->innerJoin("p.Feature f$i WITH f$i.id = {$f->id}");
    }
  return $q->execute();
}

Features is supposed to be a Doctrine_Collection with the features you wish to join with. Replace $f->id with $f['id'] to support a Doctrine array, or with $f if your want to supply a simple array filled with feature IDs.