/**
* 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::wildcardSearchKeywords($this->cleanValue($value));
}
$this->_key++;
$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') != '') {
$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;
}