ansaurus

Question

Answer 1

A:

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.

Pelle ten Cate 2010-10-21 14:42:49

Thanks, I tried that but it worries me a bit; the generated SQL is quite intensive: FROM property p INNER JOIN property_feature p2 ON (p.id = p2.property_id) INNER JOIN feature_attraction_entity f ON f.id = p2.feature_id AND (f.name = ?) AND f.type = 1 SELECT... shortened....INNER JOIN property_feature p3 ON (p.id = p3.property_id) INNER JOIN feature_attraction_entity f2 ON f2.id = p3.feature_id AND (f2.name = ?) AND f2.type = 1

Zeljko 2010-10-21 17:26:08

As you can see, property_feature is joined with 'feature_attraction_entity' 2 times when I try to search for 2 Features. Can it be eleminated somehow? I work with Doctrine almost a year, but the difference between leftjoin and innerjoin confuses me.

Zeljko 2010-10-21 17:31:19

No. Well, you could probably do subqueries. Note that this 'issue' is not a Doctrine issue, there is simply no easier way to do this in SQL without joining the same table twice. That said, if you make sure you have indices on your foreign keys (and FK constraints if necessary) in your database, this really is not an intensive query.

Pelle ten Cate 2010-10-22 09:02:01

Don't let the amount of JOINS fool you in thinking this is an intensive query. Especially, be aware of the difference between WITH and WHERE here, WHERE is applied after the joins are completed, and is a lot more intensive because it is applied on a lot more records. WITH (which is actually ON) is applied just before the join is made, so less rows will remain after the join, which is a potentially huge performance improvement for every operatoin after this JOIN. :)

Pelle ten Cate 2010-10-22 09:03:57

As to the difference between LeftJoin and InnerJoin: `a left join b` leaves all A elements that have no corresponding B in the resultset. `a inner join b` only returns results from a that have a matching b. `Properties left join Features` would return ALL properties, and their features if present. `Properties inner join features` returns only the Properties that have Features. Since you don't want properties selected that have no features at all, it is good practice to filter them out as soon as possible. This is therefore a classic case where `inner join` is the right way to go.

Pelle ten Cate 2010-10-22 09:09:01

Thanks Pete, I will implement your solution. And also thanks for explaining the difference between inner join and left join; I couldn't really understand explanation from Doctrine's sit.

Zeljko 2010-10-22 17:08:58

ansaurus

tags:

views:

answers:

Doctrine: Complex JOIN problem

related questions