static function getRelationships($id1, $id2, $options = array())
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('r' => 'Relationship'));
$from = 'link l LEFT JOIN relationship r ON (r.id = l.relationship_id)';
$where = 'l.entity1_id = ? AND l.entity2_id = ? AND r.is_deleted = 0';
$params = array($id1, $id2);
if ($catIds = @$options['cat_ids']) {
if (count(explode(',', $catIds)) == 1) {
$where .= ' AND l.category_id = ?';
$params[] = $catIds;
} else {
$where .= ' AND l.category_id IN (' . $catIds . ')';
}
}
$paging = LsApi::getPagingFromOptions($options, $defaultNum = 20, $maxNum = 100);
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' ' . $paging;
$stmt = $db->execute($sql, $params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
static function getSecondDegreeNetwork($id, $options = array(), $countOnly = false)
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('e2' => 'Entity')) . ', GROUP_CONCAT(DISTINCT l.entity1_id) degree1_ids, COUNT(DISTINCT l.entity1_id) degree1_num, SUM(r.amount) degree1_total';
$from = 'ls_list_entity le ' . 'LEFT JOIN link l ON (l.entity1_id = le.entity_id) ' . 'LEFT JOIN relationship r ON (r.id = l.relationship_id) ' . 'LEFT JOIN entity e1 ON (e1.id = l.entity1_id) ' . 'LEFT JOIN entity e2 ON (e2.id = l.entity2_id)';
if ($order = @$options['order']) {
$isReverse = (int) ($order == 2);
$where = 'le.list_id = ? AND le.is_deleted = 0 AND l.is_reverse = ? AND e1.is_deleted = 0 AND e2.is_deleted = 0';
$params = array($id, $isReverse);
} else {
$where = 'le.list_id = ? AND le.is_deleted = 0 AND e1.is_deleted = 0 AND e2.is_deleted = 0';
$params = array($id);
}
if ($catIds = @$options['cat_ids']) {
if (count(explode(',', $catIds)) == 1) {
$where .= ' AND l.category_id = ?';
$params[] = $catIds;
} else {
$where .= ' AND l.category_id IN (' . $catIds . ')';
}
}
if ($degree1Type = @$options['degree1_type']) {
$where .= ' AND e1.primary_ext = ?';
$params[] = $degree1Type;
}
if ($degree2Type = @$options['degree2_type']) {
$where .= ' AND e2.primary_ext = ?';
$params[] = $degree2Type;
}
if ($countOnly) {
$sql = 'SELECT COUNT(DISTINCT l.entity2_id) FROM ' . $from . ' WHERE ' . $where;
} else {
$paging = LsApi::getPagingFromOptions($options, $defaultNum = 10, $maxNum = 20);
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' GROUP BY e2.id ORDER BY ' . (@$options['sort'] == 'amount' ? 'degree1_total DESC, degree1_num DESC' : 'degree1_num DESC, degree1_total DESC') . ' ' . $paging;
}
$stmt = $db->execute($sql, $params);
if ($countOnly) {
return $stmt->fetchColumn();
}
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
static function getSecondDegreeNetworkFromFirstDegrees($id, $degree1_ids, $options = array(), $countOnly = false)
{
$select = 'l.entity2_id AS degree2_id, GROUP_CONCAT(DISTINCT l.entity1_id) AS degree1_ids, COUNT(DISTINCT l.entity1_id) AS num';
$from = 'link l';
$where = 'l.entity1_id IN (' . $degree1_ids . ') AND l.entity2_id <> ?';
$group = 'l.entity2_id';
$params = array($id, $id);
//limit by relationship order, if specified
if (@($order = $options['order'])) {
$isReverse = $order == 2;
$where .= ' AND l.is_reverse = ' . (int) $isReverse;
}
//limit by cat1_ids
if ($catIds = @$options['cat_ids']) {
if (count(explode(',', $catIds)) == 1) {
$where .= ' AND l.category_id = ?';
$params[] = $catIds;
} else {
$where .= ' AND l.category_id IN (' . $catIds . ')';
}
}
if ($countOnly) {
$sql = 'SELECT COUNT(DISTINCT l.entity2_id) FROM ' . $from . ' WHERE ' . $where;
} else {
$paging = LsApi::getPagingFromOptions($options, $defaultNum = 20, $maxNum = 100);
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' GROUP BY ' . $group . ' ORDER BY num DESC ' . $paging;
}
$stmt = $db->execute($sql, $params);
if ($countOnly) {
return $stmt->fetchColumn();
} else {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$entities = array();
foreach ($rows as $row) {
$entity = self::get($row['degree2_id']);
$entity['degree1_num'] = $row['num'];
$entity['degree1_ids'] = $row['degree1_ids'];
$entities[] = $entity;
}
return $entities;
}
