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