/**
* Return a query for Entities that have Relationships to a given Entity and this Entity.
* Eg, all the common organizations that this Entity and a given Entity have positions in.
*
* In the param definitions below, the following terms are used:
*
* Entity0: This Entity
* Entity1: The entity with a relationship to Entity0 (one degree of separation)
* Entity2: The entity with a relationship to Entity1 (two degrees of separation)
* Relationship1: The Relationship between Entity and Entity1
* Relationship2: The Relationship between Entity1 and Entity2
*
* This query is looking for Entity1s that match this pattern:
* Entity0 <---(Relationship1)---> Entity1 <---(Relationship2)---> Entity2
*
* @param Entity $entity Entity2
* @param integer $order1 Order of Entity1 in Relationship1; can be 1, 2, or NULL
* @param integer $order2 Order of Entity2 in Relationship2; can be 1, 2, or NULL
* @param mixed $categoryIds1 Limits Relationship1 to one or more category_ids
* @param mixed $categoryIds2 Limits Relationship2 to one or more category_ids
* @param mixed $description1 A description to limit Relationship1
* @param mixed $description2 A description to limit Relationship2
* @param mixed $extensions1 Limits Entity1 to one or more extension names
* @param mixed $extensions2 Limits Entity2 to one or more extension names
* @param boolean $concurrent Whether Relationship1 and Relationship2 must overlap in time; false by default
*
* @return Doctrine_Query A query with the given constraints
*/
public function getCommonEntitiesQuery(Entity $entity, $order1 = null, $order2 = null, $categoryIds1 = null, $categoryIds2 = null, $description1 = null, $description2 = null, $extensions = null, $concurrent = false)
{
$q = LsDoctrineQuery::Create()->from('Entity e1');
if ($order2) {
$q->innerJoin('e1.Relationship r2 ON r2.entity' . $order2 . '_id = ?', $entity->id)->addWhere('r2.entity' . (3 - $order2) . '_id = e1.id');
} else {
$q->innerJoin('e1.Relationship r2 ON e1.id IN (r2.entity1_id, r2.entity2_id)')->addWhere('r2.entity1_id = ? OR r2.entity2_id = ?', array($entity->id, $entity->id));
}
if ($order1) {
$q->innerJoin('e1.Relationship r1 ON r1.entity' . $order1 . '_id = e1.id')->addWhere('r1.entity' . (3 - $order1) . '_id = ?', $this->id);
} else {
$q->innerJoin('e1.Relationship r1 ON e1.id IN (r1.entity1_id, r2.entity2_id)')->addWhere('r1.entity1_id = ? OR r1.entity2_id = ?', array($this->id, $this->id));
}
if ($categoryIds1) {
$q->andWhereIn('r1.category_id', (array) $categoryIds1);
}
if ($categoryIds2) {
$q->andWhereIn('r2.category_id', (array) $categoryIds2);
}
$q->groupBy('e1.id');
if ($extensions) {
LsQuery::addExtensionRequirement($q, $extensions, 'e1');
}
if ($concurrent) {
LsQuery::addConcurrence($q);
}
return $q;
}