/** * Build SQL for Data Source filter * * @param array $data * @param string $joins * @param string $where * @param boolean $andOperation */ function buildDSRetrivalSQL($data, &$joins, &$where, $andOperation = FALSE) { $field_id = $this->get('id'); if (!is_array($data)) { $data = array($data); } foreach ($data as &$value) { $value = SearchIndex::manipulateKeywords($this->cleanValue($value)); } $this->_key++; if (is_array($data)) { $data = implode(" ", $data); } $joins .= " LEFT JOIN `tbl_search_index` AS search_index ON (e.id = search_index.entry_id) "; $where .= " AND MATCH(search_index.data) AGAINST ('{$data}' IN BOOLEAN MODE) "; return TRUE; }
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; }