public function grab(&$param_pool) { $result = new XMLElement($this->dsParamROOTELEMENT); self::__init(); $db = ASDCLoader::instance(); $sql = "SELECT SQL_CALC_FOUND_ROWS \n\t\t\t\t\t\tpinned.entry_id AS `id`, \n\t\t\t\t\t\tpinned.value AS `pinned`, \n\t\t\t\t\t\tclosed.value AS `closed`, \n\t\t\t\t\t\tcreation_date.local AS `creation-date`,\n\t\t\t\t\t\tlast_active.local AS `last-active`,\t\t\t\t\t\t\t\n\t\t\t\t\t\tcreated_by.member_id AS `created-by-member-id`,\n\t\t\t\t\t\tcreated_by.username AS `created-by-username`,\n\t\t\t\t\t\tlast_post.member_id AS `last-post-member-id`,\n\t\t\t\t\t\tlast_post.username AS `last-post-username`,\t\t\t\t\t\t\t\n\t\t\t\t\t\ttopic.value AS `topic`,\n\t\t\t\t\t\tCOUNT(comments.relation_id) AS `comments`\n\t\t\t\t\t\n\t\t\t\t\tFROM `tbl_entries_data_%d` AS `pinned`\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `closed` ON pinned.entry_id = closed.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `creation_date` ON pinned.entry_id = creation_date.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `last_active` ON pinned.entry_id = last_active.entry_id\t\t\t\t\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `created_by` ON pinned.entry_id = created_by.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `last_post` ON pinned.entry_id = last_post.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `topic` ON pinned.entry_id = topic.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `comments` ON pinned.entry_id = comments.relation_id\n\t\t\t\t\tWHERE 1 %s\n\t\t\t\t\tGROUP BY pinned.entry_id\n\t\t\t\t\tORDER BY pinned.value ASC, last_active.local DESC\n\t\t\t\t\tLIMIT %d, %d"; try { $rows = $db->query(sprintf($sql, self::findFieldID('pinned', 'discussions'), self::findFieldID('closed', 'discussions'), self::findFieldID('creation-date', 'discussions'), self::findFieldID('last-active', 'discussions'), self::findFieldID('created-by', 'discussions'), self::findFieldID('last-post', 'discussions'), self::findFieldID('topic', 'discussions'), self::findFieldID('parent-id', 'comments'), isset($this->dsParamFILTERS['id']) && (int) $this->dsParamFILTERS['id'] > 0 ? " AND pinned.entry_id = " . (int) $this->dsParamFILTERS['id'] : NULL, max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT)); } catch (Exception $e) { $result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError())))); return $result; } if ($rows->length() == 0 && strlen(trim($dsParamFILTERS['id'])) > 0) { $this->__redirectToErrorPage(); } elseif ($rows->length() == 0) { return $this->emptyXMLSet(); } $total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total; $result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); /* stdClass Object ( [id] => 666 [pinned] => yes [closed] => no [creation-date] => 1233599808 [last-active] => 1237161637 [created-by-member-id] => 2126 [created-by-username] => Lewis [last-post-member-id] => 2126 [last-post-username] => Lewis [topic] => Symphony 2 Documentation [comments] => 18 ) <entry id="595" comments="7"> <created-by id="2150">newnomad</created-by> <closed>No</closed> <last-active time="18:30" weekday="1">2009-02-09</last-active> <last-post id="2150">newnomad</last-post> <pinned>No</pinned> <topic handle="viewing-feeds">viewing feeds</topic> <creation-date time="19:31" weekday="3">2009-01-07</creation-date> </entry> */ $param_pool['ds-' . $this->dsParamROOTELEMENT] = DatabaseUtilities::resultColumn($rows, 'id'); foreach ($rows as $r) { $entry = new XMLElement('entry', NULL, array('id' => $r->id, 'comments' => $r->comments)); $entry->appendChild(new XMLElement('created-by', General::sanitize($r->{'created-by-username'}), array('id' => $r->{'created-by-member-id'}))); $entry->appendChild(new XMLElement('last-post', General::sanitize($r->{'last-post-username'}), array('id' => $r->{'last-post-member-id'}))); $entry->appendChild(new XMLElement('closed', ucfirst($r->closed))); $entry->appendChild(new XMLElement('pinned', ucfirst($r->pinned))); $entry->appendChild(new XMLElement('topic', General::sanitize($r->topic))); $entry->appendChild(General::createXMLDateObject($r->{'creation-date'}, 'creation-date')); $entry->appendChild(General::createXMLDateObject($r->{'last-active'}, 'last-active')); $result->appendChild($entry); } return $result; }
public function grab(&$param_pool) { self::__init(); /* var $dsParamINCLUDEDELEMENTS = array( 'system:pagination', 'comment', 'date', 'created-by' ); <pagination total-entries="28" total-pages="2" entries-per-page="20" current-page="2" /> <section id="39" handle="comments">Comments</section> */ $result = new XMLElement($this->dsParamROOTELEMENT); try { $comments = ASDCLoader::instance()->query(sprintf("SELECT SQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\tcomment.entry_id AS `id`,\n\t\t\t\t\t\t\tcomment.value_formatted AS `comment`, \n\t\t\t\t\t\t\tcreated_by.member_id, \n\t\t\t\t\t\t\tcreated_by.username, \n\t\t\t\t\t\t\tdate.local AS `date`,\n\t\t\t\t\t\t\temail.value AS `email`\n\n\t\t\t\t\t\tFROM `tbl_entries_data_%d` AS `comment`\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `created_by` ON comment.entry_id = created_by.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `date` ON comment.entry_id = date.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `email` ON created_by.member_id = email.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `discussion` ON comment.entry_id = discussion.entry_id\n\t\t\t\t\t\tWHERE discussion.relation_id = %d\n\t\t\t\t\t\tORDER BY date.local ASC\n\t\t\t\t\t\tLIMIT %d, %d", self::findFieldID('comment', 'comments'), self::findFieldID('created-by', 'comments'), self::findFieldID('date', 'comments'), self::findFieldID('email-address', 'members'), self::findFieldID('parent-id', 'comments'), (int) $this->dsParamFILTERS['discussion_id'], max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT)); } catch (Exception $e) { $result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', ASDCLoader::instance()->lastError())))); return $result; } if ($comments->length() == 0) { $this->__redirectToErrorPage(); } $total = ASDCLoader::instance()->query('SELECT FOUND_ROWS() AS `total`;')->current()->total; $result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); foreach ($comments as $c) { /* stdClass Object ( [id] => 20589 [comment] => <p>blah blah</p> [member_id] => 2103 [username] => Alistair [date] => 1241576727 [email] => alistair@21degrees.com.au ) <entry id="20515"> <date time="01:32" weekday="3">2009-05-06</date> <comment word-count="6"><p>This site looks awesome guys! Congrats!</p></comment> <created-by id="2694">davethegr8</created-by> </entry> */ $entry = new XMLElement('entry', NULL, array('id' => $c->id)); $entry->appendChild(new XMLElement('created-by', General::sanitize($c->username), array('email-address-hash' => md5($c->email), 'email-address' => General::sanitize($c->email), 'id' => $c->member_id))); $entry->appendChild(General::createXMLDateObject($c->date, 'date')); $c->comment = str_replace(array('<script', '</script'), array('<script', '</script'), $c->comment); $entry->appendChild(new XMLElement('comment', trim($c->comment))); $result->appendChild($entry); } return $result; }
private function __search($query) { $result = new XMLElement($this->dsParamROOTELEMENT); if (strlen(trim($query)) == 0) { return $this->emptyXMLSet($result); } $db = ASDCLoader::instance(); $result->appendChild(new XMLElement('query-string', General::sanitize($query), array('encoded' => urlencode($query)))); $sql = "SELECT SQL_CALC_FOUND_ROWS \n\t\t\t\t\t\tMATCH(comment.value) AGAINST ('%6\$s') AS `score`,\n\t\t\t\t\t\tcomment.entry_id AS `id`,\n\t\t\t\t\t\tdate.local AS `date`,\n\t\t\t\t\t\tcomment.value_formatted AS `description`,\n\t\t\t\t\t\tmember.member_id AS `member-id`, \n\t\t\t\t\t\tmember.username AS `username`,\n\t\t\t\t\t\ttopic.value AS `topic`,\n\t\t\t\t\t\tparent.relation_id AS `discussion-id`\n\t\t\t\t\t\t\n\t\t\t\t\tFROM `tbl_entries_data_%1\$d` AS `date`\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%2\$d` AS `comment` ON date.entry_id = comment.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%3\$d` AS `member` ON date.entry_id = member.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%4\$d` AS `parent` ON date.entry_id = parent.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%5\$d` AS `topic` ON parent.relation_id = topic.entry_id\n\n\t\t\t\t\tWHERE MATCH(comment.value) AGAINST ('%6\$s')\n\t\t\t\t\tORDER BY `score` DESC\n\t\t\t\t\tLIMIT %7\$d, %8\$d"; //MATCH(comment.value) AGAINST ('%s') AS `score`, //OR MATCH(comment.value) AGAINST ('%1\$s') //WITH QUERY EXPANSION //member.username = '******' OR comment.value LIKE '%%%6\$s%%' OR topic.value LIKE '%%%6\$s%%' try { $rows = $db->query(sprintf($sql, self::findFieldID('date', 'comments'), self::findFieldID('comment', 'comments'), self::findFieldID('created-by', 'comments'), self::findFieldID('parent-id', 'comments'), self::findFieldID('topic', 'discussions'), $db->escape($query), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT)); } catch (Exception $e) { $result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError())))); return $result; } if ($rows->length() == 0) { return $this->emptyXMLSet($result); } $total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total; $result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); /* <entry id="19753"> <name>Section Schema</name> <member id="2101">Allen</member> <description><p>Sect ... ollow).</p></description> </entry> */ foreach ($rows as $r) { $entry = new XMLElement('entry', NULL, array('discussion-id' => $r->{'discussion-id'}, 'id' => $r->id, 'score' => number_format($r->score, 3))); // Topic $entry->appendChild(new XMLElement('topic', General::sanitize($r->topic))); // Date $entry->appendChild(General::createXMLDateObject($r->date, 'date')); // Member $entry->appendChild(new XMLElement('member', General::sanitize($r->{'username'}), array('id' => $r->{'member-id'}))); // Comment $entry->appendChild(new XMLElement('comment', trim($r->description))); $result->appendChild($entry); } return $result; }
public function execute(array &$param_pool) { $result = new XMLElement($this->dsParamROOTELEMENT); $this->_param_pool = $param_pool; $where = NULL; $joins = NULL; $group = false; include_once TOOLKIT . '/class.entrymanager.php'; if (!($section = SectionManager::fetch((int) $this->getSource()))) { $about = $this->about(); trigger_error(__('The section associated with the data source %s could not be found.', array('<code>' . $about['name'] . '</code>')), E_USER_ERROR); } $sectioninfo = new XMLElement('section', General::sanitize($section->get('name')), array('id' => $section->get('id'), 'handle' => $section->get('handle'))); if ($this->_force_empty_result == true) { $this->_force_empty_result = false; //this is so the section info element doesn't disappear. $result = $this->emptyXMLSet(); $result->prependChild($sectioninfo); return; } if (is_array($this->dsParamINCLUDEDELEMENTS)) { $include_pagination_element = in_array('system:pagination', $this->dsParamINCLUDEDELEMENTS); } else { $this->dsParamINCLUDEDELEMENTS = array(); } if (isset($this->dsParamPARAMOUTPUT) && !is_array($this->dsParamPARAMOUTPUT)) { $this->dsParamPARAMOUTPUT = array($this->dsParamPARAMOUTPUT); } $this->_can_process_system_parameters = $this->canProcessSystemParameters(); if (!isset($this->dsParamPAGINATERESULTS)) { $this->dsParamPAGINATERESULTS = 'yes'; } // Process Filters $this->processFilters($where, $joins, $group); // Process Sorting if ($this->dsParamSORT == 'system:id') { EntryManager::setFetchSorting('id', $this->dsParamORDER); } else { if ($this->dsParamSORT == 'system:date') { EntryManager::setFetchSorting('date', $this->dsParamORDER); } else { EntryManager::setFetchSorting(FieldManager::fetchFieldIDFromElementName($this->dsParamSORT, $this->getSource()), $this->dsParamORDER); } } // combine `INCLUDEDELEMENTS`, `PARAMOUTPUT` and `GROUP` into an // array of field handles to optimise the `EntryManager` queries $datasource_schema = $this->dsParamINCLUDEDELEMENTS; if (is_array($this->dsParamPARAMOUTPUT)) { $datasource_schema = array_merge($datasource_schema, $this->dsParamPARAMOUTPUT); } if ($this->dsParamGROUP) { $datasource_schema[] = FieldManager::fetchHandleFromID($this->dsParamGROUP); } $entries = EntryManager::fetchByPage($this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1, $this->getSource(), $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : NULL, $where, $joins, $group, !$include_pagination_element ? true : false, true, array_unique($datasource_schema)); /** * Immediately after building entries allow modification of the Data Source entry list * * @delegate DataSourceEntriesBuilt * @param string $context * '/frontend/' * @param Datasource $datasource * @param array $entries * @param array $filters */ Symphony::ExtensionManager()->notifyMembers('DataSourceEntriesBuilt', '/frontend/', array('datasource' => &$this, 'entries' => &$entries, 'filters' => $this->dsParamFILTERS)); if (($entries['total-entries'] <= 0 || $include_pagination_element === true) && (!is_array($entries['records']) || empty($entries['records'])) || $this->dsParamSTARTPAGE == '0') { if ($this->dsParamREDIRECTONEMPTY == 'yes') { throw new FrontendPageNotFoundException(); } $this->_force_empty_result = false; $result = $this->emptyXMLSet(); $result->prependChild($sectioninfo); if ($include_pagination_element) { $pagination_element = General::buildPaginationElement(); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } } else { if (!$this->_param_output_only) { $result->appendChild($sectioninfo); if ($include_pagination_element) { $t = $this->dsParamPAGINATERESULTS == 'yes' && isset($this->dsParamLIMIT) && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries']; $pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } } // If this datasource has a Limit greater than 0 or the Limit is not set if (!isset($this->dsParamLIMIT) || $this->dsParamLIMIT > 0) { if (!isset($this->dsParamASSOCIATEDENTRYCOUNTS) || $this->dsParamASSOCIATEDENTRYCOUNTS == 'yes') { $this->_associated_sections = $section->fetchAssociatedSections(); } // If the datasource require's GROUPING if (isset($this->dsParamGROUP)) { self::$_fieldPool[$this->dsParamGROUP] =& FieldManager::fetch($this->dsParamGROUP); $groups = self::$_fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']); foreach ($groups as $element => $group) { foreach ($group as $g) { $result->appendChild($this->processRecordGroup($element, $g)); } } } else { if (isset($entries['records'][0])) { $data = $entries['records'][0]->getData(); $pool = FieldManager::fetch(array_keys($data)); self::$_fieldPool += $pool; } foreach ($entries['records'] as $entry) { $xEntry = $this->processEntry($entry); if ($xEntry instanceof XMLElement) { $result->appendChild($xEntry); } } } } } $param_pool = $this->_param_pool; return $result; }
if ($include_pagination_element) { $pagination_element = General::buildPaginationElement(); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } if (isset($this->dsParamPARAMOUTPUT)) { $param_pool[$key][] = ''; } } else { if (!$this->_param_output_only) { $result = new XMLElement($this->dsParamROOTELEMENT); $result->appendChild($sectioninfo); if ($include_pagination_element) { $t = $this->dsParamPAGINATERESULTS == 'yes' && isset($this->dsParamLIMIT) && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries']; $pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } } if (isset($this->dsParamPARAMOUTPUT) && !is_array($param_pool[$key])) { $param_pool[$key] = array(); } if (!isset($this->dsParamLIMIT) || $this->dsParamLIMIT > 0) { if (isset($this->dsParamGROUP)) { $fieldPool[$this->dsParamGROUP] =& $entryManager->fieldManager->fetch($this->dsParamGROUP); $groups = $fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']); foreach ($groups as $element => $group) { foreach ($group as $g) { processRecordGroup($result, $element, $g, $this, $entryManager, $fieldPool, $param_pool, $this->_param_output_only);
public function processResults(SymRead $query, Section $section, array $pagination, array $entries) { $section_xml = new XMLElement('section', $section->get('name')); $section_xml->setAttribute('id', $section->get('id')); $section_xml->setAttribute('handle', $section->get('handle')); $this->appendChild($section_xml); $this->appendChild(General::buildPaginationElement($pagination['total-entries'], $pagination['total-pages'], $pagination['entries-per-page'], $pagination['current-page'])); foreach ($entries as $entry) { $entry_xml = new XMLElement('entry'); $entry_xml->setAttribute('id', $entry->get('id')); foreach ($query->selects as $select) { $field = $select->get('object'); if ($field == SymQuery::SYSTEM_DATE) { $entry_xml->appendChild(General::createXMLDateObject(strtotime($entry->creationDate), 'system-date')); } if (!$field instanceof Field) { continue; } $data = $entry->getData($field->get('id')); if ($select->has('mode')) { $field->appendFormattedElement($entry_xml, $data, false, $select->get('mode')); } else { $field->appendFormattedElement($entry_xml, $data, false); } } $this->appendChild($entry_xml); } return $this; }
public static function run(SymQLQuery $query, $output = SymQL::RETURN_XML) { self::init(); self::getQueryCount(); // stores all config locally so that the same SymQLManager can be used for mutliple queries $section = null; $section_fields = array(); $where = null; $joins = null; $entry_ids = array(); // resolve section $resolved_section = self::getResolvedSection($query->section); if (is_null($resolved_section)) { $section = $query->section; if (!is_numeric($query->section)) { $section = self::$_sectionManager->fetchIDFromHandle($query->section); } $section = self::$_sectionManager->fetch($section); if (!$section instanceof Section) { throw new Exception(sprintf("%s: section '%s' does not not exist", __CLASS__, $query->section)); } $fields = $section->fetchFields(); self::$_resolved_sections[] = $section; self::$_resolved_fields[$section->get('id')] = $fields; } else { $section = $resolved_section; $fields = self::getResolvedFields($section->get('id')); } self::$_debug['queries']['Resolve section and fields'] = self::getQueryCount(); // cache list of field objects in this section (id => object) foreach ($fields as $field) { $section_fields[] = $field->get('id'); } $section_fields = self::indexFieldsByID($section_fields, $fields, true); // resolve list of fields from SELECT statement if ($query->fields == '*') { foreach ($fields as $field) { $select_fields[] = $field->get('element_name'); } } else { $select_fields = $query->fields; } $select_fields = self::indexFieldsByID($select_fields, $fields); // resolve list of fields from WHERE statements (filters) $filters = array(); if (is_array($query->filters)) { foreach ($query->filters as $i => $filter) { $field = self::indexFieldsByID($filter['field'], $fields); if ($field) { $filters[$i][reset(array_keys($field))]['value'] = $filter['value']; $filters[$i][reset(array_keys($field))]['type'] = $filter['type']; } } } // resolve sort field if (in_array($query->sort_field, self::$_reserved_fields)) { $handle_exploded = explode(':', $query->sort_field); if (count($handle_exploded) == 2) { self::$_entryManager->setFetchSorting(end($handle_exploded), $query->sort_direction); } } else { $sort_field = self::indexFieldsByID($query->sort_field, $fields); $sort_field = $section_fields[reset(array_keys($sort_field))]; if ($sort_field && $sort_field->isSortable()) { self::$_entryManager->setFetchSorting($sort_field->get('id'), $query->sort_direction); } } $where = null; $joins = null; foreach ($filters as $filter) { $field_id = reset(array_keys($filter)); $filter = reset($filter); if ($field_id == 'system:id') { $entry_ids[] = (int) $filter['value']; continue; } // get the cached field object $field = $section_fields[$field_id]; if (!$field) { throw new Exception(sprintf("%s: field '%s' does not not exist", __CLASS__, $field_id)); } if (!$field->canFilter() || !method_exists($field, 'buildDSRetrivalSQL')) { throw new Exception(sprintf("%s: field '%s' can not be used as a filter", __CLASS__, $field_id)); } // local $_where = null; $_joins = null; $filter_type = false === strpos($filter['value'], '+') ? self::DS_FILTER_OR : self::DS_FILTER_AND; $value = preg_split('/' . ($filter_type == self::DS_FILTER_AND ? '\\+' : ',') . '\\s*/', $filter['value'], -1, PREG_SPLIT_NO_EMPTY); if (!is_array($value)) { $value = array($value); } // Get the WHERE and JOIN from the field $where_before = $_where; $field->buildDSRetrivalSQL($value, $_joins, $_where, $filter_type == self::DS_FILTER_AND ? true : false); // HACK: if this is an OR statement, strip the first AND from the returned SQL // and replace with OR if ($filter['type'] == SymQL::DS_FILTER_OR) { $_where_after = substr($_where, strlen($_where_before), strlen($where)); $_where_after = preg_replace('/^AND/', 'OR', trim($_where_after)); $_where = $_where_before . $_where_after; } $joins .= $_joins; $where .= $_where; } // resolve the SELECT type and fetch entries if (reset(array_keys($select_fields)) == 'system:count') { $select_type = SymQL::SELECT_COUNT; $fetch_result = (int) self::$_entryManager->fetchCount($section->get('id'), $where, $joins); } else { if (count($entry_ids) > 0) { $select_type = SymQL::SELECT_ENTRY_ID; $fetch_result = self::$_entryManager->fetch($entry_ids, $section->get('id'), $query->per_page, null, null, false, false, true, array_values($select_fields)); } else { $select_type = SymQL::SELECT_ENTRIES; $fetch_result = self::$_entryManager->fetchByPage($query->page, $section->get('id'), $query->per_page, $where, $joins, false, false, true, array_values($select_fields)); } } self::$_debug['sql']['joins'] = $joins; self::$_debug['sql']['where'] = $where; self::$_debug['queries']['Fetch entries'] = self::getQueryCount(); // section metadata $section_metadata = array('name' => $section->get('name'), 'id' => $section->get('id'), 'handle' => $section->get('handle')); // build pagination metadata if ($select_type == SymQL::SELECT_ENTRIES) { $pagination = array('total-entries' => (int) $fetch_result['total-entries'], 'total-pages' => (int) $fetch_result['total-pages'], 'per-page' => (int) $fetch_result['limit'], 'current-page' => (int) $query->page); } // find the array of entries returned from EntryManager fetch $entries = array(); switch ($select_type) { case SymQL::SELECT_ENTRY_ID: $entries = $fetch_result; break; case SymQL::SELECT_ENTRIES: $entries = $fetch_result['records']; break; case SymQL::SELECT_COUNT: $count = $fetch_result; break; } // set up result container depending on return type switch ($output) { case SymQL::RETURN_ARRAY: case SymQL::RETURN_RAW_COLUMNS: case SymQL::RETURN_ENTRY_OBJECTS: $result = array(); $result['section'] = $section_metadata; if ($pagination) { $result['pagination'] = $pagination; } break; case SymQL::RETURN_XML: $result = new XMLElement($query->root_element ? $query->root_element : 'symql'); $result->appendChild(new XMLElement('section', $section_metadata['name'], array('id' => $section_metadata['id'], 'handle' => $section_metadata['handle']))); if ($pagination) { $result->appendChild(General::buildPaginationElement($pagination['total-entries'], $pagination['total-pages'], $pagination['per-page'], $pagination['current-page'])); } break; } // append returned entries to results container if ($select_type == SymQL::SELECT_ENTRY_ID || $select_type == SymQL::SELECT_ENTRIES) { foreach ($entries as $entry) { switch ($output) { case SymQL::RETURN_RAW_COLUMNS: $fields = array(); foreach ($entry->getData() as $field_id => $values) { $field = $section_fields[$field_id]; $fields[$field->get('element_name')] = $values; } $result['entries'][$entry->get('id')] = $fields; break; case SymQL::RETURN_ENTRY_OBJECTS: $result['entries'][$entry->get('id')] = $entry; break; case SymQL::RETURN_XML: case SymQL::RETURN_ARRAY: $xml_entry = new XMLElement('entry'); $xml_entry->setAttribute('id', $entry->get('id')); foreach ($entry->getData() as $field_id => $values) { $field = $section_fields[$field_id]; $handle = $field->get('element_name'); $handle_exploded = explode(':', $select_fields[$field_id]); if (count($handle_exploded) == 2) { $mode = end($handle_exploded); } $field->appendFormattedElement($xml_entry, $values, $encode, $mode); } if ($output == SymQL::RETURN_ARRAY) { $result['entries'][] = XMLToArray::convert($xml_entry->generate()); } else { $result->appendChild($xml_entry); } break; } } } elseif ($select_type == SymQL::SELECT_COUNT) { switch ($output) { case SymQL::RETURN_ARRAY: case SymQL::RETURN_RAW_COLUMNS: case SymQL::RETURN_ENTRY_OBJECTS: $result['count'] = $count; break; case SymQL::RETURN_XML: $xml_entry = new XMLElement('count', $count); $result->appendChild($xml_entry); break; } } self::$_debug['queries']['Total'] = self::$_context->Database->queryCount() - self::$_base_querycount; // reset for the next query self::$_entryManager->setFetchSorting(null, null); self::$_base_querycount = null; self::$_cumulative_querycount = null; return $result; }
public function grab(&$param_pool) { $Members = Frontend::instance()->ExtensionManager->create('members'); $Members->initialiseCookie(); if ($Members->isLoggedIn() !== true) { // Oi! you can't be here redirect(URL . '/forbidden/'); exit; } $result = new XMLElement($this->dsParamROOTELEMENT); self::__init(); $db = ASDCLoader::instance(); $sql = 'SELECT SQL_CALC_FOUND_ROWS pinned.entry_id AS `id`, pinned.value AS `pinned`, closed.value AS `closed`, creation_date.local AS `creation-date`, last_active.local AS `last-active`, created_by.member_id AS `created-by-member-id`, created_by.username AS `created-by-username`, last_post.member_id AS `last-post-member-id`, last_post.username AS `last-post-username`, topic.value AS `topic` FROM `tbl_entries_data_%d` AS `pinned` LEFT JOIN `tbl_entries_data_%d` AS `closed` ON pinned.entry_id = closed.entry_id LEFT JOIN `tbl_entries_data_%d` AS `creation_date` ON pinned.entry_id = creation_date.entry_id LEFT JOIN `tbl_entries_data_%d` AS `last_active` ON pinned.entry_id = last_active.entry_id LEFT JOIN `tbl_entries_data_%d` AS `created_by` ON pinned.entry_id = created_by.entry_id LEFT JOIN `tbl_entries_data_%d` AS `last_post` ON pinned.entry_id = last_post.entry_id LEFT JOIN `tbl_entries_data_%d` AS `topic` ON pinned.entry_id = topic.entry_id LEFT JOIN `tbl_entries_data_%d` AS `comments` ON pinned.entry_id = comments.relation_id LEFT JOIN `tbl_entries_data_%d` AS `discussion_comments_member` ON comments.entry_id = discussion_comments_member.entry_id WHERE 1 %s AND (created_by.member_id = %11$d || discussion_comments_member.member_id = %11$d) GROUP BY pinned.entry_id ORDER BY pinned.value ASC, last_active.local DESC LIMIT %12$d, %13$d'; try { $rows = $db->query(sprintf($sql, self::findFieldID('pinned', 'discussions'), self::findFieldID('closed', 'discussions'), self::findFieldID('creation-date', 'discussions'), self::findFieldID('last-active', 'discussions'), self::findFieldID('created-by', 'discussions'), self::findFieldID('last-post', 'discussions'), self::findFieldID('topic', 'discussions'), self::findFieldID('parent-id', 'comments'), self::findFieldID('created-by', 'comments'), isset($this->dsParamFILTERS['id']) && (int) $this->dsParamFILTERS['id'] > 0 ? " AND pinned.entry_id = " . (int) $this->dsParamFILTERS['id'] : NULL, (int) $Members->Member->get('id'), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT)); } catch (Exception $e) { $result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError())))); return $result; } if ($rows->length() == 0) { return $this->emptyXMLSet(); } $total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total; $result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); /* stdClass Object ( [id] => 666 [pinned] => yes [closed] => no [creation-date] => 1233599808 [last-active] => 1237161637 [created-by-member-id] => 2126 [created-by-username] => Lewis [last-post-member-id] => 2126 [last-post-username] => Lewis [topic] => Symphony 2 Documentation [comments] => 18 ) <entry id="595" comments="7"> <created-by id="2150">newnomad</created-by> <closed>No</closed> <last-active time="18:30" weekday="1">2009-02-09</last-active> <last-post id="2150">newnomad</last-post> <pinned>No</pinned> <topic handle="viewing-feeds">viewing feeds</topic> <creation-date time="19:31" weekday="3">2009-01-07</creation-date> </entry> */ $param_pool['ds-' . $this->dsParamROOTELEMENT] = DatabaseUtilities::resultColumn($rows, 'id'); foreach ($rows as $r) { // Need to do a seperate query to find the comment counts. try { $comments = $db->query(sprintf("SELECT COUNT(*) AS `count` FROM `tbl_entries_data_%d` WHERE `relation_id` = %d ", self::findFieldID('parent-id', 'comments'), $r->id))->current()->count; } catch (Exception $e) { $result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError())))); return $result; } $entry = new XMLElement('entry', NULL, array('id' => $r->id, 'comments' => $comments)); $entry->appendChild(new XMLElement('created-by', General::sanitize($r->{'created-by-username'}), array('id' => $r->{'created-by-member-id'}))); $entry->appendChild(new XMLElement('last-post', General::sanitize($r->{'last-post-username'}), array('id' => $r->{'last-post-member-id'}))); $entry->appendChild(new XMLElement('closed', ucfirst($r->closed))); $entry->appendChild(new XMLElement('pinned', ucfirst($r->pinned))); $entry->appendChild(new XMLElement('topic', General::sanitize($r->topic))); $entry->appendChild(General::createXMLDateObject($r->{'creation-date'}, 'creation-date')); $entry->appendChild(General::createXMLDateObject($r->{'last-active'}, 'last-active')); $result->appendChild($entry); } return $result; }
public function execute(array &$param_pool = null) { $result = new XMLElement($this->dsParamROOTELEMENT); $config = (object) Symphony::Configuration()->get('search_index'); // Setup /*-----------------------------------------------------------------------*/ // look for key in GET array if it's specified if (!empty($config->{'get-param-prefix'})) { if ($config->{'get-param-prefix'} == 'param_pool') { $_GET = $this->_env['param']; } else { $_GET = $_GET[$config->{'get-param-prefix'}]; } } // get input parameters from GET request $param_keywords = isset($_GET[$config->{'get-param-keywords'}]) ? trim($_GET[$config->{'get-param-keywords'}]) : ''; $param_sort = isset($_GET[$config->{'get-param-sort'}]) ? $_GET[$config->{'get-param-sort'}] : $config->{'default-sort'}; $param_direction = isset($_GET[$config->{'get-param-direction'}]) ? strtolower($_GET[$config->{'get-param-direction'}]) : $config->{'default-direction'}; // set pagination on the data source $this->dsParamSTARTPAGE = isset($_GET[$config->{'get-param-page'}]) ? (int) $_GET[$config->{'get-param-page'}] : $this->dsParamSTARTPAGE; $this->dsParamLIMIT = isset($_GET[$config->{'get-param-per-page'}]) && (int) $_GET[$config->{'get-param-per-page'}] > 0 ? (int) $_GET[$config->{'get-param-per-page'}] : $config->{'default-per-page'}; // build ORDER BY statement for later switch ($param_sort) { case 'date': $sql_order_by = "e.creation_date {$param_direction}"; break; case 'id': $sql_order_by = "e.id {$param_direction}"; break; default: $sql_order_by = "score {$param_direction}"; break; } // Find valid sections to query /*-----------------------------------------------------------------------*/ if (isset($_GET[$config->{'get-param-sections'}]) && !empty($_GET[$config->{'get-param-sections'}])) { $param_sections = $_GET[$config->{'get-param-sections'}]; // allow sections to be sent as an array if the user wishes (multi-select or checkboxes) if (is_array($param_sections)) { implode(',', $param_sections); } } elseif (!empty($config->{'default-sections'})) { $param_sections = $config->{'default-sections'}; } else { $param_sections = ''; } $sections = array(); foreach (array_map('trim', explode(',', $param_sections)) as $handle) { $section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle))); if ($section) { $sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']); } } if (count($sections) == 0) { return $this->errorXML('Invalid search sections'); } // Set up and manipulate keywords /*-----------------------------------------------------------------------*/ // should we apply word stemming? $do_stemming = $config->{'stem-words'} == 'yes' ? TRUE : FALSE; // replace synonyms $keywords = SearchIndex::applySynonyms($param_keywords); $keywords_boolean = SearchIndex::parseKeywordString($keywords, $do_stemming); $keywords_highlight = trim(implode(' ', $keywords_boolean['highlight']), '"'); // Set up weighting /*-----------------------------------------------------------------------*/ $sql_weighting = ''; foreach (SearchIndex::getIndexes() as $section_id => $index) { $weight = isset($index['weighting']) ? $index['weighting'] : 2; switch ($weight) { case 0: $weight = 4; break; // highest // highest case 1: $weight = 2; break; // high // high case 2: $weight = 1; break; // none // none case 3: $weight = 0.5; break; // low // low case 4: $weight = 0.25; break; // lowest } $sql_weighting .= sprintf("WHEN e.section_id = %d THEN %d \n", $section_id, $weight); } // Build search SQL /*-----------------------------------------------------------------------*/ $mode = !is_null($config->{'mode'}) ? $config->{'mode'} : 'like'; $mode = strtoupper($mode); switch ($mode) { case 'FULLTEXT': $sql = sprintf("SELECT\n\t\t\t\t\t\t\tSQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\t\t\tdata,\n\t\t\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s') *\n\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t%2\$s\n\t\t\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t%3\$s\n\t\t\t\t\t\t\t) AS `score`\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tMATCH(index.data) AGAINST ('%4\$s' IN BOOLEAN MODE)\n\t\t\t\t\t\t\tAND e.section_id IN ('%5\$s')\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t%6\$s\n\t\t\t\t\t\tLIMIT %7\$d, %8\$d", Symphony::Database()->cleanValue($keywords), $sql_weighting, $param_sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', Symphony::Database()->cleanValue($keywords), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($sql_order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT); break; case 'LIKE': case 'REGEXP': $sql_locate = ''; $sql_replace = ''; $sql_where = ''; // by default, no wildcard separators $prefix = ''; $suffix = ''; // append wildcard for LIKE if ($mode == 'LIKE') { $prefix = $suffix = '%'; } // apply word boundary separator if ($mode == 'REGEXP') { $prefix = '[[:<:]]'; $suffix = '[[:>:]]'; } // all words to include in the query (single words and phrases) foreach ($keywords_boolean['include-words-all'] as $keyword) { $keyword_stem = NULL; $keyword = Symphony::Database()->cleanValue($keyword); if ($do_stemming) { $keyword_stem = Symphony::Database()->cleanValue(PorterStemmer::Stem($keyword)); } // if the word can be stemmed, look for the word or the stem version if ($do_stemming && $keyword_stem != $keyword) { $sql_where .= "(index.data {$mode} '{$prefix}{$keyword}{$suffix}' OR index.data {$mode} '{$prefix}{$keyword}{$suffix}') AND "; } else { $sql_where .= "index.data {$mode} '{$prefix}{$keyword}{$suffix}' AND "; } // if this keyword exists in the entry contents, add 1 to "keywords_matched" // which represents number of unique keywords in the search string that are found $sql_locate .= "IF(LOCATE('{$keyword}', LOWER(`data`)) > 0, 1, 0) + "; // see how many times this word is found in the entry contents by removing it from // the column text then compare length to see how many times it was removed $sql_replace .= "(LENGTH(`data`) - LENGTH(REPLACE(LOWER(`data`),LOWER('{$keyword}'),''))) / LENGTH('{$keyword}') + "; } // all words or phrases that we do not want foreach ($keywords_boolean['exclude-words-all'] as $keyword) { $keyword = Symphony::Database()->cleanValue($keyword); $sql_where .= "index.data NOT {$mode} '{$prefix}{$keyword}{$suffix}' AND "; } // append to complete SQL $sql_locate = $sql_locate == '' ? $sql_locate = '1' : ($sql_locate .= '0'); $sql_replace = $sql_replace == '' ? $sql_replace = '1' : ($sql_replace .= '0'); $sql_where = $sql_where == '' ? $sql_where = 'NOT 1' : $sql_where; // trim unnecessary boolean conditions from SQL $sql_where = preg_replace("/ OR \$/", "", $sql_where); $sql_where = preg_replace("/ AND \$/", "", $sql_where); // if ordering by score, use a function of the two columns // we are calculating rather than just "score" if (preg_match("/^score/", $sql_order_by)) { $sql_order_by = preg_replace("/^score/", "(keywords_matched * score)", $sql_order_by); } $sql = sprintf("SELECT\n\t\t\t\t\t\t\tSQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\t\t\tdata,\n\t\t\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t%1\$s\n\t\t\t\t\t\t\t) AS keywords_matched,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t(%2\$s)\n\t\t\t\t\t\t\t\t*\n\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t%3\$s\n\t\t\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t%4\$s\n\t\t\t\t\t\t\t) AS score\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t%5\$s\n\t\t\t\t\t\t\tAND e.section_id IN ('%6\$s')\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t%7\$s\n\t\t\t\t\t\tLIMIT\n\t\t\t\t\t\t\t%8\$d, %9\$d", $sql_locate, $sql_replace, $sql_weighting, $param_sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', $sql_where, implode("','", array_keys($sections)), Symphony::Database()->cleanValue($sql_order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT); //echo $sql;die; break; } // Add soundalikes ("did you mean?") to XML /*-----------------------------------------------------------------------*/ // we have search words, check for soundalikes if (count($keywords_boolean['include-words-all']) > 0) { $sounds_like = array(); foreach ($keywords_boolean['include-words-all'] as $word) { $soundalikes = Symphony::Database()->fetchCol('keyword', sprintf("SELECT keyword FROM tbl_search_index_keywords WHERE SOUNDEX(keyword) = SOUNDEX('%s')", Symphony::Database()->cleanValue($word))); foreach ($soundalikes as $i => &$soundalike) { if ($soundalike == $word) { unset($soundalikes[$i]); continue; } $soundalike = array('word' => $soundalike, 'distance' => levenshtein($soundalike, $word)); } usort($soundalikes, array('datasourcesearch', 'sortWordDistance')); $sounds_like[$word] = $soundalikes[0]['word']; } // add words to XML if (count($sounds_like) > 0) { $alternative_spelling = new XMLElement('alternative-keywords'); foreach ($sounds_like as $word => $soundalike) { $alternative_spelling->appendChild(new XMLElement('keyword', NULL, array('original' => $word, 'alternative' => $soundalike, 'distance' => levenshtein($soundalike, $word)))); } $result->appendChild($alternative_spelling); } } // Run search SQL! /*-----------------------------------------------------------------------*/ // get our entries, returns entry IDs $entries = Symphony::Database()->fetch($sql); $total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`'); // append input values $result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => General::sanitize($param_sort), 'direction' => General::sanitize($param_direction))); // append pagination $result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); // append list of sections $sections_xml = new XMLElement('sections'); foreach ($sections as $id => $section) { $sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle']))); } $result->appendChild($sections_xml); // Append entries to XML, build if desired /*-----------------------------------------------------------------------*/ // if true then the entire entry will be appended to the XML. If not, only // a "stub" of the entry ID is provided, allowing other data sources to // supplement with the necessary fields $build_entries = $config->{'build-entries'} == 'yes' ? TRUE : FALSE; if ($build_entries) { $field_pool = array(); } // container for entry ID output parameter $param_output = array(); foreach ($entries as $entry) { $param_output[] = $entry['entry_id']; $entry_xml = new XMLElement('entry', NULL, array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle'])); // add excerpt with highlighted search terms $excerpt = SearchIndex::parseExcerpt($keywords_highlight, $entry['data']); $excerpt = $this->fixEncoding($excerpt); $entry_xml->appendChild(new XMLElement('excerpt', $excerpt)); // build and append entry data if ($build_entries) { $e = reset(EntryManager::fetch($entry['entry_id'])); $data = $e->getData(); foreach ($data as $field_id => $values) { if (!isset($field_pool[$field_id]) || !is_object($field_pool[$field_id])) { $field_pool[$field_id] = FieldManager::fetch($field_id); } $field_pool[$field_id]->appendFormattedElement($entry_xml, $values, FALSE, !empty($values['value_formatted']) ? 'formatted' : null, $e->get('id')); } } $result->appendChild($entry_xml); } // send entry IDs as Output Parameterss $param_pool['ds-' . $this->dsParamROOTELEMENT . '.id'] = $param_output; $param_pool['ds-' . $this->dsParamROOTELEMENT] = $param_output; // Log query /*-----------------------------------------------------------------------*/ if ($config->{'log-keywords'} == 'yes' && trim($keywords)) { $section_handles = array_map('reset', array_values($sections)); // has this search (keywords+sections) already been logged this session? $already_logged = Symphony::Database()->fetch(sprintf("SELECT * FROM `tbl_search_index_logs` WHERE keywords='%s' AND sections='%s' AND session_id='%s'", Symphony::Database()->cleanValue($param_keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), session_id())); $log_sql = sprintf("INSERT INTO `tbl_search_index_logs`\n\t\t\t\t\t(date, keywords, keywords_manipulated, sections, page, results, session_id)\n\t\t\t\t\tVALUES('%s', '%s', '%s', '%s', %d, %d, '%s')", date('Y-m-d H:i:s', time()), Symphony::Database()->cleanValue($param_keywords), Symphony::Database()->cleanValue($keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), $this->dsParamSTARTPAGE, $total_entries, session_id()); Symphony::Database()->query($log_sql); } return $result; }
$result = $this->emptyXMLSet(); $result->prependChild($sectioninfo); if ($include_pagination_element) { $pagination_element = General::buildPaginationElement(); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } $param_pool[$key][] = ''; } else { if (!$this->_param_output_only) { $result = new XMLElement($this->dsParamROOTELEMENT); $result->appendChild($sectioninfo); if ($include_pagination_element) { $t = $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries']; $pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamSTARTPAGE); if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) { $result->prependChild($pagination_element); } } } if (isset($this->dsParamPARAMOUTPUT) && !is_array($param_pool[$key])) { $param_pool[$key] = array(); } if ($this->dsParamLIMIT > 0) { if (isset($this->dsParamGROUP)) { $fieldPool[$this->dsParamGROUP] =& $entryManager->fieldManager->fetch($this->dsParamGROUP); $groups = $fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']); foreach ($groups as $element => $group) { foreach ($group as $g) { processRecordGroup($result, $element, $g, $this, $this->_Parent, $entryManager, $fieldPool, $param_pool, $this->_param_output_only);
public function render(Register $ParameterOutput, $joins = NULL, array $where = array(), $filter_operation_type = self::FILTER_AND) { $execute = true; $result = new XMLDocument(); $result->appendChild($result->createElement($this->parameters()->{'root-element'})); $root = $result->documentElement; // Conditions // If any one condtion returns true (that is, do not execute), the DS will not execute at all if (is_array($this->parameters()->conditions)) { foreach ($this->parameters()->conditions as $condition) { if (preg_match('/:/', $condition['parameter'])) { $c = Datasource::replaceParametersInString($condition['parameter'], $ParameterOutput); } else { $c = Datasource::resolveParameter($condition['parameter'], $ParameterOutput); } // Is Empty if ($condition['logic'] == 'empty' && (is_null($c) || strlen($c) == 0)) { $execute = false; } elseif ($condition['logic'] == 'set' && !is_null($c)) { $execute = false; } if ($execute !== true) { return NULL; } } } // Grab the section try { $section = Section::loadFromHandle($this->parameters()->section); } catch (SectionException $e) { } catch (Exception $e) { } $pagination = (object) array('total-entries' => NULL, 'entries-per-page' => max(1, (int) self::replaceParametersInString($this->parameters()->limit, $ParameterOutput)), 'total-pages' => NULL, 'current-page' => max(1, (int) self::replaceParametersInString($this->parameters()->page, $ParameterOutput))); $pagination->{'record-start'} = max(0, ($pagination->{'current-page'} - 1) * $pagination->{'entries-per-page'}); $order = $sort = NULL; // Apply the Sorting & Direction if ($this->parameters()->{'sort-order'} == 'random') { $order = 'RAND()'; } else { $sort = strtolower($this->parameters()->{'sort-order'}) == 'asc' ? 'ASC' : 'DESC'; // System Field if (preg_match('/^system:/i', $this->parameters()->{'sort-field'})) { switch (preg_replace('/^system:/i', NULL, $this->parameters()->{'sort-field'})) { case 'id': $order = "e.id {$sort}"; break; case 'creation-date': $order = "e.creation_date {$sort}"; break; case 'modification-date': $order = "e.modification_date {$sort}"; break; } } else { $join = NULL; $sort_field = $section->fetchFieldByHandle($this->parameters()->{'sort-field'}); if ($sort_field instanceof Field && $sort_field->isSortable() && method_exists($sort_field, "buildSortingQuery")) { $sort_field->buildSortingQuery($join, $order); $joins .= sprintf($join, $sort_field->section, $sort_field->{'element-name'}); $order = sprintf($order, $sort); } } } // Process Datasource Filters for each of the Fields if (is_array($this->parameters()->filters) && !empty($this->parameters()->filters)) { foreach ($this->parameters()->filters as $k => $filter) { if ($filter['element-name'] == 'system:id') { $filter_value = $this->prepareFilterValue($filter['value'], $ParameterOutput); if (!is_array($filter_value)) { continue; } $filter_value = array_map('intval', $filter_value); if (empty($filter_value)) { continue; } $where[] = sprintf("(e.id %s IN (%s))", $filter['type'] == 'is-not' ? 'NOT' : NULL, implode(',', $filter_value)); } else { $field = $section->fetchFieldByHandle($filter['element-name']); if ($field instanceof Field) { $field->buildFilterQuery($filter, $joins, $where, $ParameterOutput); } } } } // Escape percent symbold: $where = array_map(create_function('$string', 'return str_replace(\'%\', \'%%\', $string);'), $where); $query = sprintf('SELECT DISTINCT SQL_CALC_FOUND_ROWS e.* FROM `tbl_entries` AS `e` %1$s WHERE `section` = "%2$s" %3$s ORDER BY %4$s LIMIT %5$d, %6$d', $joins, $section->handle, is_array($where) && !empty($where) ? 'AND (' . implode($filter_operation_type == self::FILTER_AND ? ' AND ' : ' OR ', $where) . ')' : NULL, $order, $pagination->{'record-start'}, $pagination->{'entries-per-page'}); try { $entries = Symphony::Database()->query($query, array($section->handle, $section->{'publish-order-handle'}), 'EntryResult'); if (isset($this->parameters()->{'append-pagination'}) && $this->parameters()->{'append-pagination'} === true) { $pagination->{'total-entries'} = (int) Symphony::Database()->query("SELECT FOUND_ROWS() AS `total`")->current()->total; $pagination->{'total-pages'} = (int) ceil($pagination->{'total-entries'} * (1 / $pagination->{'entries-per-page'})); // Pagination Element $root->appendChild(General::buildPaginationElement($result, $pagination->{'total-entries'}, $pagination->{'total-pages'}, $pagination->{'entries-per-page'}, $pagination->{'current-page'})); } if (isset($this->parameters()->{'append-sorting'}) && $this->parameters()->{'append-sorting'} === true) { $sorting = $result->createElement('sorting'); $sorting->setAttribute('field', $this->parameters()->{'sort-field'}); $sorting->setAttribute('order', $this->parameters()->{'sort-order'}); $root->appendChild($sorting); } $schema = array(); // Build Entry Records if ($entries->valid()) { // Do some pre-processing on the include-elements. if (is_array($this->parameters()->{'included-elements'}) && !empty($this->parameters()->{'included-elements'})) { $included_elements = (object) array('system' => array(), 'fields' => array()); foreach ($this->parameters()->{'included-elements'} as $element) { $element_name = $mode = NULL; if (preg_match_all('/^([^:]+):\\s*(.+)$/', $element, $matches, PREG_SET_ORDER)) { $element_name = $matches[0][1]; $mode = $matches[0][2]; } else { $element_name = $element; } if ($element_name == 'system') { $included_elements->system[] = $mode; } else { $field = $section->fetchFieldByHandle($element_name); if (!$field instanceof Field) { continue; } $schema[] = $element_name; $included_elements->fields[] = array('element-name' => $element_name, 'instance' => $field, 'mode' => !is_null($mode) > 0 ? trim($mode) : NULL); } } } // Do some pre-processing on the param output array if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { $parameter_output = (object) array('system' => array(), 'fields' => array()); foreach ($this->parameters()->{'parameter-output'} as $element) { if (preg_match('/^system:/i', $element)) { $parameter_output->system[preg_replace('/^system:/i', NULL, $element)] = array(); } else { $schema[] = $element; $parameter_output->fields[$element] = array(); } } } $entries->setSchema($schema); foreach ($entries as $e) { // If there are included elements, need an entry element. if (is_array($this->parameters()->{'included-elements'}) && !empty($this->parameters()->{'included-elements'})) { $entry = $result->createElement('entry'); $entry->setAttribute('id', $e->id); $root->appendChild($entry); foreach ($included_elements->system as $field) { switch ($field) { case 'creation-date': $entry->appendChild(General::createXMLDateObject($result, DateTimeObj::fromGMT($e->creation_date), 'creation-date')); break; case 'modification-date': $entry->appendChild(General::createXMLDateObject($result, DateTimeObj::fromGMT($e->modification_date), 'modification-date')); break; case 'user': $obj = User::load($e->user_id); $user = $result->createElement('user', $obj->getFullName()); $user->setAttribute('id', $e->user_id); $user->setAttribute('username', $obj->username); $user->setAttribute('email-address', $obj->email); $entry->appendChild($user); break; } } foreach ($included_elements->fields as $field) { $field['instance']->appendFormattedElement($entry, $e->data()->{$field['element-name']}, false, $field['mode'], $e); } } if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { foreach ($parameter_output->system as $field => $existing_values) { switch ($field) { case 'id': $parameter_output->system[$field][] = $e->id; break; case 'creation-date': $parameter_output->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::fromGMT($e->creation_date)); break; case 'modification-date': $parameter_output->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::fromGMT($e->modification_date)); break; case 'user': $parameter_output->system[$field][] = $e->user_id; break; } } foreach ($parameter_output->fields as $field => $existing_values) { if (!isset($e->data()->{$field}) or is_null($e->data()->{$field})) { continue; } $o = $section->fetchFieldByHandle($field)->getParameterOutputValue($e->data()->{$field}, $e); if (is_array($o)) { $parameter_output->fields[$field] = array_merge($o, $parameter_output->fields[$field]); } else { $parameter_output->fields[$field][] = $o; } } } } // Add in the param output values to the ParameterOutput object if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { foreach ($parameter_output->system as $field => $values) { $key = sprintf('ds-%s.system.%s', $this->parameters()->{'root-element'}, $field); $values = array_filter($values); if (is_array($values) && !empty($values)) { $ParameterOutput->{$key} = array_unique($values); } } foreach ($parameter_output->fields as $field => $values) { $key = sprintf('ds-%s.%s', $this->parameters()->{'root-element'}, $field); $values = array_filter($values); if (is_array($values) && !empty($values)) { $ParameterOutput->{$key} = array_unique($values); } } } } elseif ($this->parameters()->{'redirect-404-on-empty'} === true) { throw new FrontendPageNotFoundException(); } else { $this->emptyXMLSet($root); } } catch (DatabaseException $e) { $root->appendChild($result->createElement('error', $e->getMessage())); } return $result; }
public function grab(&$param_pool = NULL) { $config = (object) Symphony::Configuration()->get('elasticsearch'); // build an object of runtime parameters $params = (object) array('keywords' => isset($_GET['keywords']) ? $_GET['keywords'] : '', 'current-page' => isset($_GET['page']) && is_numeric($_GET['page']) ? (int) $_GET['page'] : 1, 'per-page' => isset($_GET['per-page']) && is_numeric($_GET['per-page']) ? (int) $_GET['per-page'] : $config->{'per-page'}, 'sort' => isset($_GET['sort']) ? $_GET['sort'] : $config->sort, 'direction' => isset($_GET['direction']) && in_array($_GET['direction'], array('asc', 'desc')) ? $_GET['direction'] : $config->direction, 'sections' => isset($_GET['sections']) && !empty($_GET['sections']) ? array_map('trim', explode(',', $_GET['sections'])) : NULL, 'default-sections' => !empty($config->{'default-sections'}) ? explode(',', $config->{'default-sections'}) : NULL, 'language' => isset($_GET['language']) && !empty($_GET['language']) ? array_map('trim', explode(',', $_GET['language'])) : NULL, 'default-language' => !empty($config->{'default-language'}) ? explode(',', $config->{'default-language'}) : NULL); $params->{'keywords-raw'} = $params->keywords; $params->keywords = ElasticSearch::filterKeywords($params->keywords); // don't run search if not searching for anything if (empty($params->keywords)) { return; } // check valid page number if ($params->{'current-page'} < 1) { $params->{'current-page'} = 1; } // if no language passed but there are defaults, use the defaults if ($params->{'language'} === NULL && count($params->{'default-language'})) { $params->{'language'} = $params->{'default-language'}; } // include this extension's own library ElasticSearch::init(); // a query_string search type in ES accepts common (Lucene) search syntax such as // prefixing terms with +/- and surrounding exact phrases with quotes $query_querystring = new Elastica_Query_QueryString(); // all terms are required $query_querystring->setDefaultOperator('AND'); // pass in keywords $query_querystring->setQueryString($params->keywords); // only apply the search to fields mapped as multi-type with a sub-type named "symphony_fulltext" // this allows us to exclude fields from this generic full-site search but search them elsewhere if ($params->{'language'}) { $fields = array(); foreach ($params->{'language'} as $language) { $fields[] = '*_' . $language . '.symphony_fulltext'; } $query_querystring->setFields($fields); } else { $query_querystring->setFields(array('*.symphony_fulltext')); } // create the parent query object (a factory) into which the query_string is passed $query = new Elastica_Query($query_querystring); $query->setLimit($params->{'per-page'}); // TODO: check this. should it be + 1? $query->setFrom($params->{'per-page'} * ($params->{'current-page'} - 1)); $query->setSort(array($params->{'sort'} => $params->{'direction'})); // build a search object, this wraps an Elastica_Client and handles requests to and from the ElasticSearch server $search = new Elastica_Search(ElasticSearch::getClient()); // search on our site index only (in case the server is running multiple indexes) $search->addIndex(ElasticSearch::getIndex()); // create a new facet on the entry _type (section handle). this will return a list // of sections in which the matching entries reside, and a count of matches in each $facet = new Elastica_Facet_Terms('filtered-sections'); $facet->setField('_type'); $query->addFacet($facet); // we also want a list of _all_ sections and their total entry counts. facets run within the context // of the query they are attached to, so we want a new query that searches within the specified sections // but doesn't search on the keywords (so it finds everything). ES supports this with a match_all query // which Elastica creates by default when you create a plain query object $query_all = new Elastica_Query(); $facet = new Elastica_Facet_Terms('all-sections'); $facet->setField('_type'); $query_all->addFacet($facet); // build an array of all valid section handles that have mappings $all_mapped_sections = array(); $section_full_names = array(); foreach (ElasticSearch::getAllTypes() as $type) { // if using default config sections, check that the type exists in the default if (count($params->{'default-sections'}) > 0 && !in_array($type->section->get('handle'), $params->{'default-sections'})) { continue; } $all_mapped_sections[] = $type->section->get('handle'); // cache an array of section names indexed by their handles, quick lookup later $section_full_names[$type->section->get('handle')] = $type->section->get('name'); } $sections = array(); // no specified sections were sent in the params, so default to all available sections if ($params->sections === NULL) { $sections = $all_mapped_sections; } else { foreach ($params->sections as $handle) { if (!in_array($handle, $all_mapped_sections)) { continue; } $sections[] = $handle; } } // a filter is an additional set of filtering that can be added to a query. filters are run // after the query has executed, so run over the resultset and remove documents that don't // match the criteria. they are fast and are cached by ES. we want to restrict the search // results to within the specified sections only, so we add a filter on the _type (section handle) // field. the filter is of type "terms" (an array of exact-match strings) $filter = new Elastica_Filter_Terms('_type'); // build an array of field handles which should be highlighted in search results, used for building // the excerpt on results pages. a field is marked as highlightable by giving it a "symphony_fulltext" // field in the section mappings $highlights = array(); // iterate over each valid section, adding it as a filter and finding any highlighted fields within foreach ($sections as $section) { // add these sections to the entry search $filter->addTerm($section); // read the section's mapping JSON from disk $mapping = json_decode(ElasticSearch::getTypeByHandle($section)->mapping_json, FALSE); // find fields that have symphony_highlight foreach ($mapping->{$section}->properties as $field => $properties) { if (!$properties->fields->symphony_fulltext) { continue; } $highlights[] = array($field => (object) array()); } } // add the section filter to both queries (keyword search and the all entries facet search) $query->setFilter($filter); $query_all->setFilter($filter); // configure highlighting for the keyword search $query->setHighlight(array('fields' => $highlights, 'encoder' => 'html', 'fragment_size' => $config->{'highlight-fragment-size'}, 'number_of_fragments' => $config->{'highlight-per-field'}, 'pre_tags' => array('<strong class="highlight">'), 'post_tags' => array('</strong>'))); // run both queries! $query_result = $search->search($query); $query_all_result = $search->search($query_all); // build root XMK element $xml = new XMLElement($this->dsParamROOTELEMENT, NULL, array('took' => $query_result->getResponse()->getEngineTime() . 'ms', 'max-score' => round($query_result->getMaxScore(), 4))); // append keywords to the XML $xml_keywords = new XMLElement('keywords'); $xml_keywords->appendChild(new XMLElement('raw', General::sanitize($params->{'keywords-raw'}))); $xml_keywords->appendChild(new XMLElement('filtered', General::sanitize($params->{'keywords'}))); $xml->appendChild($xml_keywords); // build pagination $xml->appendChild(General::buildPaginationElement($query_result->getTotalHits(), ceil($query_result->getTotalHits() * (1 / $params->{'per-page'})), $params->{'per-page'}, $params->{'current-page'})); // build facets $xml_facets = new XMLElement('facets'); // merge the facets from both queries so they appear as one $facets = array_merge($query_result->getFacets(), $query_all_result->getFacets()); foreach ($facets as $handle => $facet) { $xml_facet = new XMLElement('facet', NULL, array('handle' => $handle)); foreach ($facet['terms'] as $term) { // only show sections that are in default config, if it is being used if (!in_array($term['term'], $all_mapped_sections)) { continue; } $xml_facet_term = new XMLElement('term', $section_full_names[$term['term']], array('handle' => $term['term'], 'entries' => $term['count'], 'active' => in_array($term['term'], $sections) ? 'yes' : 'no')); $xml_facet->appendChild($xml_facet_term); } $xml_facets->appendChild($xml_facet); } $xml->appendChild($xml_facets); // if each entry is to have its full XML built and appended to the result, // create a new EntryManager for using later on if ($config->{'build-entry-xml'} === 'yes') { $em = new EntryManager(Frontend::instance()); $field_pool = array(); } // append entries $xml_entries = new XMLElement('entries'); foreach ($query_result->getResults() as $data) { $entry = new XMLElement('entry', NULL, array('id' => $data->getId(), 'section' => $data->getType(), 'score' => is_array($data->getScore()) ? reset($data->getScore()) : round($data->getScore(), 4))); // append field highlights foreach ($data->getHighlights() as $field => $highlight) { foreach ($highlight as $html) { $entry->appendChild(new XMLElement('highlight', $html, array('field' => $field))); } } // build and append entry data // this was pinched from Symphony's datasource class if ($config->{'build-entry-xml'} === 'yes') { $e = reset($em->fetch($data->getId())); $field_data = $e->getData(); foreach ($field_data as $field_id => $values) { if (!isset($field_pool[$field_id]) || !is_object($field_pool[$field_id])) { $field_pool[$field_id] = FieldManager::fetch($field_id); } $field_pool[$field_id]->appendFormattedElement($entry, $values, FALSE, NULL, $e->get('id')); } } $xml_entries->appendChild($entry); // put each entry ID into the param pool for chaining $param_pool['ds-elasticsearch'][] = $data->getId(); } $xml->appendChild($xml_entries); // log query if logging is enabled if ($config->{'log-searches'} === 'yes') { ElasticSearchLogs::save($params->keywords, $params->{'keywords-raw'}, $sections, $params->{'current-page'}, $query_result->getTotalHits()); } return $xml; }
public function grab(&$param_pool) { $result = new XMLElement($this->dsParamROOTELEMENT); $param_output = array(); $get = $_GET; // look for key in GET array if it's specified if (Symphony::Configuration()->get('get-param-prefix', 'search_index') != '') { if (Symphony::Configuration()->get('get-param-prefix', 'search_index') == 'param_pool') { $get = $this->_env['param']; } else { $get = $get[Symphony::Configuration()->get('get-param-prefix', 'search_index')]; } } $param_keywords = Symphony::Configuration()->get('get-param-keywords', 'search_index'); $param_per_page = Symphony::Configuration()->get('get-param-per-page', 'search_index'); $param_sort = Symphony::Configuration()->get('get-param-sort', 'search_index'); $param_direction = Symphony::Configuration()->get('get-param-direction', 'search_index'); $param_sections = Symphony::Configuration()->get('get-param-sections', 'search_index'); $param_page = Symphony::Configuration()->get('get-param-page', 'search_index'); $keywords = $get[$param_keywords]; $this->dsParamLIMIT = isset($get[$param_per_page]) && (int) $get[$param_per_page] > 0 ? (int) $get[$param_per_page] : $this->dsParamLIMIT; $sort = isset($get[$param_sort]) ? $get[$param_sort] : 'score'; $direction = isset($get[$param_direction]) ? strtolower($get[$param_direction]) : 'desc'; $sections = isset($get[$param_sections]) ? $get[$param_sections] : NULL; if ($sections == NULL && Symphony::Configuration()->get('default-sections', 'search_index') != '') { $sections = Symphony::Configuration()->get('default-sections', 'search_index'); } $this->dsParamSTARTPAGE = isset($get[$param_page]) ? (int) $get[$param_page] : $this->dsParamSTARTPAGE; if (is_null($sections)) { return $this->errorXML('Invalid search sections'); } else { $section_handles = explode(',', $sections); $sections = array(); foreach ($section_handles as $handle) { $section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle))); if ($section) { $sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']); } } if (count($sections) == 0) { return $this->errorXML('Invalid search sections'); } } if ($sort == 'date') { $order_by = "e.creation_date {$direction}"; } else { if ($sort == 'id') { $order_by = "e.id {$direction}"; } else { $order_by = "score {$direction}"; } } $weighting = ''; $indexed_sections = SearchIndex::getIndexes(); foreach ($indexed_sections as $section_id => $index) { $weight = is_null($index['weighting']) ? 2 : $index['weighting']; switch ($weight) { case 0: $weight = 4; break; // highest // highest case 1: $weight = 2; break; // high // high case 2: $weight = 1; break; // none // none case 3: $weight = 0.5; break; // low // low case 4: $weight = 0.25; break; // lowest } $weighting .= sprintf("WHEN e.section_id = %d THEN %d \n", $section_id, $weight); } /* MULTILANGUAGE SUPPORT: */ require_once TOOLKIT . '/class.extensionmanager.php'; $extensionManager = new ExtensionManager($this); $status = $extensionManager->fetchStatus('multilanguage'); $languageSuffix = ''; if ($status == EXTENSION_ENABLED) { $languages = explode(',', file_get_contents(MANIFEST . '/multilanguage-languages')); if (count($languages) > 0) { $code = isset($_GET['language-code']) ? strtolower($_GET['language-code']) : $languages[0]; // Override: if the parameter &lang is also in the URL, search that language instead: $code = isset($_GET['lang']) ? strtolower($_GET['lang']) : $code; $languageSuffix = '_' . $code; // SQL injection prevention: if (in_array($code, $languages)) { $languageSuffix = '_' . $code; } } } $sql = sprintf("SELECT \n\t\t\t\t\tSQL_CALC_FOUND_ROWS \n\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\tdata" . $languageSuffix . ",\n\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t(\n\t\t\t\t\t\tMATCH(index.data" . $languageSuffix . ") AGAINST ('%1\$s') * \n\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t%2\$s\n\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\tEND\n\t\t\t\t\t\t%3\$s\t\t\t\t\t\t\n\t\t\t\t\t) AS `score`\n\t\t\t\tFROM\n\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\tWHERE\n\t\t\t\t\tMATCH(index.data" . $languageSuffix . ") AGAINST ('%4\$s' IN BOOLEAN MODE)\n\t\t\t\t\tAND e.section_id IN ('%5\$s')\n\t\t\t\tORDER BY\n\t\t\t\t\t%6\$s\n\t\t\t\tLIMIT %7\$d, %8\$d", Symphony::Database()->cleanValue($keywords), $weighting, $sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', Symphony::Database()->cleanValue(SearchIndex::manipulateKeywords($keywords)), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT); /* END MULTILANGUAGE SUPPORT */ //echo $sql;die; $result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => $sort, 'direction' => $direction)); // get our entries! $entries = Symphony::Database()->fetch($sql); $total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`'); $result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); $sections_xml = new XMLElement('sections'); foreach ($sections as $id => $section) { $sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle']))); } $result->appendChild($sections_xml); foreach ($entries as $entry) { $param_output[] = $entry['entry_id']; $result->appendChild(new XMLElement('entry', General::sanitize(SearchIndex::parseExcerpt($keywords, $entry['data'])), array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle'], 'score' => round($entry['score'], 3)))); } // send entry IDs as Output Parameterss $param_pool['ds-' . $this->dsParamROOTELEMENT] = $param_output; $log_sql = sprintf("INSERT INTO `tbl_search_index_logs`\n\t\t\t\t(date, keywords, sections, page, results, session_id)\n\t\t\t\tVALUES('%s', '%s', '%s', %d, %d, '%s')", date('Y-m-d H:i:s', time()), Symphony::Database()->cleanValue($keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), $this->dsParamSTARTPAGE, $total_entries, session_id()); if ($this->log === TRUE) { Symphony::Database()->query($log_sql); } return $result; }
public function grab(&$param_pool) { $result = new XMLElement($this->dsParamROOTELEMENT); $param_output = array(); $get = $_GET; // look for key in GET array if it's specified if (Symphony::Configuration()->get('get-param-prefix', 'search_index') != '') { $get = $get[Symphony::Configuration()->get('get-param-prefix', 'search_index')]; } $param_keywords = Symphony::Configuration()->get('get-param-keywords', 'search_index'); $param_per_page = Symphony::Configuration()->get('get-param-per-page', 'search_index'); $param_sort = Symphony::Configuration()->get('get-param-sort', 'search_index'); $param_direction = Symphony::Configuration()->get('get-param-direction', 'search_index'); $param_sections = Symphony::Configuration()->get('get-param-sections', 'search_index'); $param_page = Symphony::Configuration()->get('get-param-page', 'search_index'); $keywords = $get[$param_keywords]; $this->dsParamLIMIT = isset($get[$param_per_page]) && (int) $get[$param_per_page] > 0 ? (int) $get[$param_per_page] : $this->dsParamLIMIT; $sort = isset($get[$param_sort]) ? $get[$param_sort] : 'score'; $direction = isset($get[$param_direction]) ? strtolower($get[$param_direction]) : 'desc'; $sections = isset($get[$param_sections]) ? $get[$param_sections] : null; $language = isset($_REQUEST['language']) ? $_REQUEST['language'] : '%'; $this->dsParamSTARTPAGE = isset($get[$param_page]) ? (int) $get[$param_page] : $this->dsParamSTARTPAGE; if (is_null($sections)) { return $this->errorXML('Invalid search sections'); } else { $section_handles = explode(',', $sections); $sections = array(); foreach ($section_handles as $handle) { $section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle))); if ($section) { $sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']); } } if (count($sections) == 0) { return $this->errorXML('Invalid search sections'); } } if ($sort == 'date') { $order_by = "e.creation_date {$direction}"; } else { if ($sort == 'id') { $order_by = "e.id {$direction}"; } else { $order_by = "score {$direction}"; } } $sql = sprintf("SELECT\n\t\t\t\t\tSQL_CALC_FOUND_ROWS \n\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s' IN BOOLEAN MODE) AS `score`,\n\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`\n\t\t\t\tFROM\n\t\t\t\t\tsym_search_index as `index`\n\t\t\t\t\tJOIN sym_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\tWHERE\n\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s' IN BOOLEAN MODE)\n\t\t\t\t\tAND e.section_id IN ('%2\$s')\n\t\t\t\t\tAND (index.language LIKE '%6\$s' OR index.language IS NULL)\n\t\t\t\tORDER BY\n\t\t\t\t\t%3\$s\n\t\t\t\tLIMIT %4\$d, %5\$d", Symphony::Database()->cleanValue(SearchIndex::wildcardSearchKeywords($keywords)), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT, $language); $result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => $sort, 'direction' => $direction)); // get our entries! $entries = Symphony::Database()->fetch($sql); $total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`'); $result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE)); $sections_xml = new XMLElement('sections'); foreach ($sections as $id => $section) { $sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle']))); } $result->appendChild($sections_xml); //$highest_score = null; foreach ($entries as $entry) { //if (is_null($highest_score)) $highest_score = $entry['score']; $param_output[] = $entry['entry_id']; $result->appendChild(new XMLElement('entry', null, array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle']))); } // send entry IDs as Output Parameterss $param_pool['ds-' . $this->dsParamROOTELEMENT] = implode(', ', $param_output); return $result; }