public function fetchAll($filter = null, $format = '', $userid = '', $xmldetailed = false) { if (isset($filter)) { $orderby = $filter->orderBy; if (is_array($orderby)) { $_orderby = implode(", ", $orderby); } else { $_orderby = $orderby; } } else { $orderby = ""; } $select = $this->getDbTable()->select(); $executor = $this->getDbTable(); if ($filter !== null && $filter->expr() != '') { $select = $this->getDbTable()->getAdapter()->select()->distinct()->from('actor_group_members'); $this->joins($select, $filter); $select->where($filter->expr()); $executor = $this->getDbTable()->getAdapter(); $executor->setFetchMode(Zend_Db::FETCH_OBJ); } $select = fixuZenduBuguru("" . $select); noDBSeqScan($executor); $resultSet = $executor->fetchAll($select); $entries = array(); foreach ($resultSet as $row) { $entry = new Default_Model_ActorGroupMember(); $this->populate($entry, $row); $entries[] = $entry; } return $entries; }
public function fetchAll($filter = null, $format = '', $userid = '', $xmldetailed = false) { $select = $this->getDbTable()->select(); $executor = $this->getDbTable(); if ($filter !== null) { $orderby = $filter->orderBy; if (is_array($orderby)) { $orderby = end($orderby); } } else { $orderby = null; } if ($filter !== null && $filter->expr() != '') { $select = $this->getDbTable()->getAdapter()->select()->distinct()->from('researchers'); $this->joins($select, $filter); if (!is_array($filter->expr())) { $select->where($filter->expr()); } $executor = $this->getDbTable()->getAdapter(); $executor->setFetchMode(Zend_Db::FETCH_OBJ); } if ($filter !== null) { $select->limit($filter->limit, $filter->offset); } if ($filter !== null) { $inv = false; if (substr($orderby, 0, 16) === "researchers.rank") { // $orderby = substr($orderby,12); // if ( strpos($_orderby," DESC") === false ) { // $orderby = str_replace(" ASC"," DESC", $orderby); // } else { // $orderby = str_replace(" DESC"," ASC", $orderby); // $inv = true; // } // $orderby = array($orderby, 'name'.($inv?' DESC':'')); /** * NOTE: Ordering by rank is handled by the database, so trust DB order */ $orderby = "rank DESC, name ASC"; } $select->order($orderby); if (is_array($orderby)) { $_orderby = implode(",", $orderby); } else { $_orderby = $orderby; } } if (str_replace('export', '', $format) === 'xml' || str_replace('export', '', $format) === 'csv') { $this->getDbTable()->getAdapter()->setFetchMode(Zend_Db::FETCH_OBJ); if ($userid != '') { $userid = ", {$userid}"; } else { $userid = ''; } $simpleList = ", ''"; if ($xmldetailed === "-1") { $xmldetailed = false; $simpleList = ", 'simpleindex'"; } if ($format === 'xml') { $func = "researcher_to_xml"; } else { $func = "export_researcher"; $simpleList = ',\'' . str_replace('export', '', $format) . '\''; } if ($xmldetailed) { $func = $func . "_ext"; } if (is_array($filter->expr())) { $where = array(); $from = array(); foreach ($filter->expr() as $x) { $sss = clone $select; $sss->where($x); getZendSelectParts($sss, $f, $w, $orderby, $limit); $f = fixuZenduBuguru($f); $where[] = $w; $from[] = $f; } } else { getZendSelectParts($select, $from, $where, $orderby, $limit); $from = fixuZenduBuguru($from); } if (isset($_orderby)) { $orderby = 'ORDER BY ' . $_orderby; } # TODO: FIX NULLS FIRST/LAST bypass if ($from == '') { $from = 'FROM researchers'; } if ($func == "researcher_to_xml") { // debug_log(var_export($filter->fltstr,true)); // debug_log(var_export($from, true)); // debug_log(var_export($where,true)); // debug_log($orderby); if (is_array($filter->expr())) { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(id {$orderby})) as researcher FROM filterppl((?)::text[],(?)::text[],(?)::text[]) AS researchers {$limit}", array(php_to_pg_array($filter->fltstr, false), php_to_pg_array($from, false), str_replace("''", "\\'", php_to_pg_array($where, false))))->fetchAll(); } else { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(id {$orderby})" . $simpleList . $userid . ") AS researcher FROM filterppl(?,?,?) AS researchers {$limit}", array($filter->fltstr, $from, $where))->fetchAll(); } } else { $select = fixuZenduBuguru("" . $select); noDBSeqScan($this->getDbTable()->getAdapter()); $resultSet = $this->getDbTable()->getAdapter()->query("SELECT " . $func . "(id" . $simpleList . $userid . ") as researcher FROM (" . $select . ") AS t;")->fetchAll(); } } else { $select = fixuZenduBuguru("" . $select); noDBSeqScan($executor); $resultSet = $executor->fetchAll($select); } $entries = array(); foreach ($resultSet as $row) { if (str_replace('export', '', $format) === 'xml' || str_replace('export', '', $format) === 'csv') { $entry = $row->researcher; } else { $entry = new Default_Model_Researcher(); $this->populate($entry, $row); } $entries[] = $entry; } return $entries; }
public function fetchAll($filter = null, $format = '', $xmldetailed = false) { $select = $this->getDbTable()->select()->from('applications'); $select->columns('applications.keywords'); $executor = $this->getDbTable(); if ($filter !== null) { $orderby = $filter->orderBy; if (is_array($orderby)) { $orderby = end($orderby); } } else { $orderby = null; } if ($filter !== null && $filter->expr() != '' || substr($orderby, 0, 27) === "applications.app_popularity") { $select = $this->getDbTable()->getAdapter()->select()->distinct()->from('applications'); $select->columns('applications.keywords'); if (substr($orderby, 0, 27) === "applications.app_popularity") { $select->columns('app_popularity(applications.id)'); } if ($filter !== null) { if ($filter->expr() != '') { $this->joins($select, $filter); if (!is_array($filter->expr())) { $select->where($filter->expr()); } $executor = $this->getDbTable()->getAdapter(); $executor->setFetchMode(Zend_Db::FETCH_OBJ); } } } if ($filter !== null) { $select->limit($filter->limit, $filter->offset); } if ($filter !== null) { if (substr($orderby, 0, 17) === "applications.rank") { // $orderby = substr($orderby,13); // if ( strpos($orderby," DESC") !== false ) { // $orderby = str_replace(" DESC"," DESC NULLS LAST", $orderby); // } else { // $orderby = str_replace(" ASC"," ASC NULLS FIRST", $orderby); // } // $orderby = array($orderby, "name"); /** * NOTE: Ordering by rank is handled by the database, so trust DB order */ $orderby = "applications.rank DESC, applications.name ASC"; } elseif (substr($orderby, 0, 27) === "applications.app_popularity") { $orderby = substr($orderby, 13); if (strpos($orderby, " DESC") !== false) { $orderby = str_replace(" DESC", " DESC NULLS LAST", $orderby); } else { $orderby = str_replace(" ASC", " ASC NULLS FIRST", $orderby); } $orderby = array($orderby, "applications.name"); } elseif (substr($orderby, 0, 19) === 'applications.rating') { if (strpos($orderby, " DESC") !== false) { $orderby = str_replace(" DESC", " DESC NULLS LAST", $orderby); } else { $orderby = str_replace(" ASC", " ASC NULLS FIRST", $orderby); } $orderby = array($orderby, "applications.ratingcount DESC", "applications.name"); } elseif (substr($orderby, 0, 21) === 'applications.hitcount') { if (strpos($orderby, " DESC") !== false) { $orderby = str_replace(" DESC", " DESC NULLS LAST", $orderby); } else { $orderby = str_replace(" ASC", " ASC NULLS FIRST", $orderby); } // $orderby = str_replace("applications.hitcount","hitcount",$orderby); $orderby = array($orderby, "applications.name"); } /** freshness app order HACK start **/ $orderby = str_replace("applications.lastupdated", "CASE WHEN applications.id IN (SELECT appid FROM app_order_hack) THEN '2000-01-01 00:00:00'::timestamp ELSE applications.lastupdated END", $orderby); $orderby = str_replace("applications.dateadded", "CASE WHEN applications.id IN (SELECT appid FROM app_order_hack) THEN '2000-01-01 00:00:00'::timestamp ELSE applications.dateadded END", $orderby); /** freshness app order HACK end **/ $select->order($orderby); if (is_array($orderby)) { $_orderby = implode(",", $orderby); } else { $_orderby = $orderby; } } if (str_replace('export', '', $format) === 'xml' || str_replace('export', '', $format) === 'csv') { $this->getDbTable()->getAdapter()->setFetchMode(Zend_Db::FETCH_OBJ); $userid = ''; if ($format === 'xml') { $func = "app_to_xml"; } else { $func = "export_app"; $userid = ", '" . str_replace('export', '', $format) . "'"; } if ($xmldetailed === true) { $func = $func . "_ext"; if ($this->_userid != '') { $userid = ", {$this->_userid}"; } } elseif ($xmldetailed === "listing") { $func = $func . "_list"; } if (is_array($filter->expr())) { $where = array(); $from = array(); foreach ($filter->expr() as $x) { $sss = clone $select; $sss->where($x); getZendSelectParts($sss, $f, $w, $orderby, $limit); $f = fixuZenduBuguru($f); $where[] = $w; $from[] = $f; } } else { getZendSelectParts($select, $from, $where, $orderby, $limit); $from = fixuZenduBuguru($from); } if (isset($_orderby) && trim($_orderby) !== "") { $orderby = 'ORDER BY ' . $_orderby; } # TODO: FIX NULLS FIRST/LAST bypass if (isset($orderby)) { $orderby = str_replace("applications.lastupdated", "apps.lastupdated", $orderby); } # FORCE current value of "lastupdated" instead of cached if ($from == '') { $from = 'FROM applications'; } if ($func == "app_to_xml" || $func == "app_to_xml_list" || $func == "export_app") { // debug_log(var_export($filter->fltstr,true)); // debug_log(var_export($from, true)); // debug_log(var_export($where,true)); // debug_log($orderby); $frmt = ""; if ($func === "export_app") { $frmt = str_replace('export', '', $format); if (!$frmt || trim($frmt) === "") { $frmt = ""; } else { if ($frmt === 'xml') { $frmt = ", 'xml'"; } else { if ($frmt === 'csv') { $frmt = ", 'csv'"; } else { $frmt = ""; } } } } if (is_array($filter->expr())) { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(applications.id {$orderby}) {$frmt} ) as application FROM filterapps((?)::text[],(?)::text[],(?)::text[]) AS applications INNER JOIN applications AS apps ON apps.id = applications.id {$limit}", array(php_to_pg_array($filter->fltstr, false), php_to_pg_array($from, false), str_replace("''", "\\'", php_to_pg_array($where, false))))->fetchAll(); } else { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(applications.id {$orderby}) {$frmt}) as application FROM filterapps(?,?,?) AS applications INNER JOIN applications AS apps ON apps.id = applications.id {$limit}", array($filter->fltstr, $from, $where))->fetchAll(); } } else { //debug_log("########" . "".$select . "#########"); $select = fixuZenduBuguru("" . $select); noDBSeqScan($this->getDbTable()->getAdapter()); $resultSet = db()->query("SELECT " . $func . "(id" . $userid . ") as application FROM (" . $select . ") AS t")->fetchAll(); // $resultSet = $this->getDbTable()->getAdapter()->query("SELECT ".$func."(id".$userid.") as application FROM (".$select.") AS t")->fetchAll(); } } else { $query = fixuZenduBuguru("" . $select); if (strpos(str_replace('"', '', $orderby), "applications.name") !== false && !is_array($orderby)) { $query = str_replace("DISTINCT \"applications", "DISTINCT ON (applications.name) \"applications", $query); } // debug_log("########" . "".$query. "#########"); noDBSeqScan($executor); $resultSet = $executor->fetchAll($query); } $entries = array(); foreach ($resultSet as $row) { if (str_replace('export', '', $format) === 'xml' || str_replace('export', '', $format) === 'csv') { $entry = $row->application; } else { $entry = new Default_Model_Application(); $this->populate($entry, $row); } $entries[] = $entry; } return $entries; }
public function fetchAll($filter = null, $format = '', $xmldetailed = false) { $select = $this->getDbTable()->select(); $executor = $this->getDbTable(); if ($filter !== null) { $orderby = $filter->orderBy; if (is_array($orderby)) { $orderby = end($orderby); } } else { $orderby = null; } if ($filter !== null && $filter->expr() != '') { $select = $this->getDbTable()->getAdapter()->select()->distinct()->from('vos'); $this->joins($select, $filter); if (!is_array($filter->expr())) { $select->where($filter->expr()); } $executor = $this->getDbTable()->getAdapter(); $executor->setFetchMode(Zend_Db::FETCH_OBJ); } if ($filter !== null) { $select->limit($filter->limit, $filter->offset); } if ($filter !== null) { $inv = false; if (substr($orderby, 0, 8) === "vos.rank") { // $orderby = substr($orderby, 4); // if ( strpos($orderby," DESC") === false ) { // $orderby = str_replace(" ASC"," DESC", $orderby); // } else { // $orderby = str_replace(" DESC"," ASC", $orderby); // $inv = true; // } // $orderby = array($orderby, 'name'.($inv?' DESC':'')); /** * NOTE: Ordering by rank is handled by the database, so trust DB order */ $orderby = "rank DESC, name ASC"; } $select->order($orderby); if (is_array($orderby)) { $_orderby = implode(",", $orderby); } else { $_orderby = $orderby; } } if (is_array($filter->expr())) { $where = array(); $from = array(); foreach ($filter->expr() as $x) { $sss = clone $select; $sss->where($x); getZendSelectParts($sss, $f, $w, $orderby, $limit); $f = fixuZenduBuguru($f); $where[] = $w; $from[] = $f; } } else { getZendSelectParts($select, $from, $where, $orderby, $limit); $from = fixuZenduBuguru($from); } if (isset($_orderby)) { $orderby = 'ORDER BY ' . $_orderby; } # TODO: FIX NULLS FIRST/LAST bypass if ($from == '') { $from = 'FROM vos'; } if ($format === 'xml') { $func_name = "vo_to_xml"; if ($xmldetailed) { $func_name .= "_ext"; } // debug_log(var_export($filter->fltstr,true)); // debug_log(var_export($from, true)); // debug_log(var_export($where,true)); // debug_log($orderby); $this->getDbTable()->getAdapter()->setFetchMode(Zend_Db::FETCH_OBJ); if (is_array($filter->expr())) { noDBSeqScan(db()); // $fff = fopen(APPLICATION_PATH . "/../cache/debuglog", "a+"); // $sss = db()->quoteInto("SELECT ".$func_name."(array_agg(id $orderby)) as vo FROM filtervos((?)::text[],", php_to_pg_array($filter->fltstr, false)) . db()->quoteInto("(?)::text[],", php_to_pg_array($from, false)) . db()->quoteInto("(?)::text[]) AS vos $limit", str_replace("''", "\'", php_to_pg_array($where, false))); // fwrite($fff, $sss); // fclose($fff); $resultSet = db()->query("SELECT " . $func_name . "(array_agg(id {$orderby})) as vo FROM filtervos((?)::text[],(?)::text[],(?)::text[]) AS vos {$limit}", array(php_to_pg_array($filter->fltstr, false), php_to_pg_array($from, false), str_replace("''", "\\'", php_to_pg_array($where, false))))->fetchAll(); } else { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func_name . "(array_agg(id {$orderby})) AS vo FROM filtervos(?,?,?) AS vos {$limit}", array($filter->fltstr, $from, $where))->fetchAll(); } } else { $select = fixuZenduBuguru("" . $select); noDBSeqScan($executor); $resultSet = $executor->fetchAll($select); } $entries = array(); foreach ($resultSet as $row) { if ($format === 'xml') { $entry = $row->vo; } else { $entry = new Default_Model_VO(); $this->populate($entry, $row); } $entries[] = $entry; } return $entries; }
public function fetchAll($filter = null, $format = '', $xmldetailed = false) { $select = $this->getDbTable()->select()->from('sites'); $executor = $this->getDbTable(); if ($filter !== null) { $orderby = $filter->orderBy; if (is_array($orderby)) { $orderby = end($orderby); } } else { $orderby = null; } if ($format == '') { if ($filter !== null && is_array($filter->joins) && count($filter->joins) > 0) { return $this->fetchFilteredEntries($filter); } else { return parent::fetchAll($filter); } } else { if ($format === 'xml') { if ($filter !== null) { $select = $this->getDbTable()->getAdapter()->select()->distinct()->from('sites'); if (is_array($filter->expr()) || trim($filter->expr()) != '') { $this->joins($select, $filter); if (!is_array($filter->expr())) { $select->where($filter->expr()); } $executor = $this->getDbTable()->getAdapter(); $executor->setFetchMode(Zend_Db::FETCH_OBJ); } } $this->getDbTable()->getAdapter()->setFetchMode(Zend_Db::FETCH_OBJ); if ($filter !== null) { $ord = $filter->orderBy; // if ( $ord == '' ) $ord = 'name ASC'; $select->limit($filter->limit, $filter->offset); $select->order($ord); } if (is_array($filter->expr())) { $where = array(); $from = array(); foreach ($filter->expr() as $x) { $sss = clone $select; $sss->where($x); getZendSelectParts($sss, $f, $w, $orderby, $limit); $f = fixuZenduBuguru($f); $where[] = $w; $from[] = $f; } } else { getZendSelectParts($select, $from, $where, $orderby, $limit); $from = fixuZenduBuguru($from); } if ($from == '') { $from = 'FROM sites'; } if ($xmldetailed === true) { $func = "site_to_xml_ext"; } else { $func = "site_to_xml"; } db()->setFetchMode(Zend_Db::FETCH_OBJ); if (is_array($filter->expr())) { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(sites.guid::text {$orderby})) as site FROM filtersites((?)::text[],(?)::text[],(?)::text[]) AS sites INNER JOIN sites AS s ON s.id = sites.id {$limit}", array(php_to_pg_array($filter->fltstr, false), php_to_pg_array($from, false), str_replace("''", "\\'", php_to_pg_array($where, false))))->fetchAll(); } else { noDBSeqScan(db()); $resultSet = db()->query("SELECT " . $func . "(array_agg(sites.guid::text {$orderby})) as site FROM filtersites(?,?,?) AS sites INNER JOIN sites AS s ON s.id = sites.id {$limit}", array($filter->fltstr, $from, $where))->fetchAll(); } $entries = array(); foreach ($resultSet as $row) { $entry = $row->site; $entries[] = $entry; } return $entries; } } }