Doctrine: The JOIN Challenge

Question

I have many or two relationships between Property and Feature. I need to extract the results from the property table, but only if both or more of the Features are related.

Those. Property No. 1 is associated with "Pool" and "Garden". Property No. 2 is only associated with the "Swimming Pool". Therefore, when I request properties that have both "Pool" and "Garden", he should return only property No. 1 to me.

I tried EVERY possible combinations, such as complex 'where ()', 'whereIn', 'join ... with', 'exists ()', etc., but cannot solve it. It returns me all the properties or not, I can’t remember everything that I tried. Please help, I spent 8 hours and killed me.

Answer 1

So, in other words, you want to select all the properties associated with several specific functions.

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

The functions are supposed to be Doctrine_Collection with the functions you want to join with. Replace $f->idwith $f['id']for Doctrine array support or with $fif you want to provide a simple array filled with function identifiers.