/**
* The core search engine configuration.
* Picks up the fulltext-indexed tables from the database and executes search on all of them.
* Results are obtained as ID-ClassName pairs which is later used to reconstruct the DataObjectSet.
*
* @param array classesToSearch computes all descendants and includes them. Check is done via WHERE clause.
* @param string $keywords Keywords as a space separated string
* @return object DataObjectSet of result pages
*/
public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
{
$results = new DataObjectSet();
if (!$this->fullTextEnabled()) {
return $results;
}
if (!in_array(substr($sortBy, 0, 9), array('"Relevanc', 'Relevance'))) {
user_error("Non-relevance sort not supported.", E_USER_ERROR);
}
$allClassesToSearch = array();
foreach ($classesToSearch as $class) {
$allClassesToSearch = array_merge($allClassesToSearch, ClassInfo::dataClassesFor($class));
}
$allClassesToSearch = array_unique($allClassesToSearch);
//Get a list of all the tables and columns we'll be searching on:
$fulltextColumns = DB::query('EXEC sp_help_fulltext_columns');
$queries = array();
// Sort the columns back into tables.
$tables = array();
foreach ($fulltextColumns as $column) {
// Skip extension tables.
if (substr($column['TABLE_NAME'], -5) == '_Live' || substr($column['TABLE_NAME'], -9) == '_versions') {
continue;
}
// Add the column to table.
$table =& $tables[$column['TABLE_NAME']];
if (!$table) {
$table = array($column['FULLTEXT_COLUMN_NAME']);
} else {
array_push($table, $column['FULLTEXT_COLUMN_NAME']);
}
}
// Create one query per each table, $columns not used. We want just the ID and the ClassName of the object from this query.
foreach ($tables as $tableName => $columns) {
$baseClass = ClassInfo::baseDataClass($tableName);
$join = $this->fullTextSearchMSSQL($tableName, $keywords);
if (!$join) {
return new DataObjectSet();
}
// avoid "Null or empty full-text predicate"
// Check if we need to add ShowInSearch
$where = null;
if (strpos($tableName, 'SiteTree') === 0) {
$where = array("\"{$tableName}\".\"ShowInSearch\"!=0");
}
$queries[$tableName] = singleton($tableName)->extendedSQL($where);
$queries[$tableName]->orderby = null;
// Join with CONTAINSTABLE, a full text searcher that includes relevance factor
$queries[$tableName]->from = array("\"{$tableName}\" INNER JOIN {$join} AS \"ft\" ON \"{$tableName}\".\"ID\"=\"ft\".\"KEY\"");
// Join with the base class if needed, as we want to test agains the ClassName
if ($tableName != $baseClass) {
$queries[$tableName]->from[] = "INNER JOIN \"{$baseClass}\" ON \"{$baseClass}\".\"ID\"=\"{$tableName}\".\"ID\"";
}
$queries[$tableName]->select = array("\"{$tableName}\".\"ID\"", "'{$tableName}' AS Source", "\"Rank\" AS \"Relevance\"");
if ($extraFilter) {
$queries[$tableName]->where[] = $extraFilter;
}
if (count($allClassesToSearch)) {
$queries[$tableName]->where[] = "\"{$baseClass}\".\"ClassName\" IN ('" . implode($allClassesToSearch, "', '") . "')";
}
// Reset the parameters that would get in the way
}
// Generate SQL
$querySQLs = array();
foreach ($queries as $query) {
$querySQLs[] = $query->sql();
}
// Unite the SQL
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy}";
// Perform the search
$result = DB::query($fullQuery);
// Regenerate DataObjectSet - watch out, numRecords doesn't work on sqlsrv driver on Windows.
$current = -1;
$results = new DataObjectSet();
foreach ($result as $row) {
$current++;
// Select a subset for paging
if ($current >= $start && $current < $start + $pageLength) {
$results->push(DataObject::get_by_id($row['Source'], $row['ID']));
}
}
$results->setPageLimits($start, $pageLength, $current + 1);
return $results;
}
protected function parseResults(RestfulService_Response $results)
{
$data = new DataObjectSet();
// Parse out items in the request element so we can create pager and other items.
$request = $results->xpath('/shrs/rq');
$request = $request[0];
$rqTitle = $request->xpath('t');
$rqDate = $request->xpath('dt');
$rqStart = $request->xpath('si');
$rqCount = $request->xpath('rpd');
$rqTotal = $request->xpath('tr');
$rqTotalViewable = $request->xpath('tv');
$rqTotalViewable = intval($rqTotalViewable[0]);
$this->searchTitle = strval($rqTitle[0]);
$records = $results->xpath('/shrs/rs/r');
$zebra = 0;
foreach ($records as $rec) {
// create an empty dataobject to hold the job data
$job = array();
// get each data node from the xml
$title = $rec->xpath('jt');
$company = $rec->xpath('cn');
$source = $rec->xpath('src');
$type = $rec->xpath('ty');
$location = $rec->xpath('loc');
$seen = $rec->xpath('ls');
$posted = $rec->xpath('dp');
$excerpt = $rec->xpath('e');
// create new fields for each piece of data in the dataobject
$job['ID'] = new Text(NULL);
$job['ID']->setValue(md5(strval($title[0])));
$job['Title'] = new Text(NULL);
$job['Title']->setValue(strval($title[0]));
$job['Company'] = new Text(NULL);
$job['Company']->setValue(strval($company[0]));
$job['Source'] = new Text(NULL);
// Need to grab the url attribute from the source element.
$srcAttribs = $source[0]->attributes();
$job['Source']->setValue(strval($srcAttribs['url']));
$job['Location'] = new Text(NULL);
$job['Location']->setValue(strval($location[0]));
$job['LastSeen'] = new SS_DateTime();
$job['LastSeen']->setValue(strval($seen[0]));
$job['DatePosted'] = new SS_DateTime();
$job['DatePosted']->setValue(strval($posted[0]));
$job['Excerpt'] = new HTMLText();
$job['Excerpt']->setValue('<p>' . strval($excerpt[0]) . '</p>');
$job['Zebra'] = !$zebra ? 'odd' : 'even';
$zebra = !$zebra ? 1 : 0;
$data->push(new ArrayData($job));
unset($job, $title, $company, $source, $type, $location, $seen, $posted, $excerpt);
}
$data->setPageLimits($this->page, $this->window, $rqTotalViewable);
$data->setPaginationGetVar($this->pageVar);
return $data;
}
/**
* The core search engine, used by this class and its subclasses to do fun stuff.
* Searches both SiteTree and File.
*
* @param string $keywords Keywords as a string.
*/
public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
{
$fileFilter = '';
$keywords = Convert::raw2sql($keywords);
$htmlEntityKeywords = htmlentities($keywords, ENT_NOQUOTES);
$extraFilters = array('SiteTree' => '', 'File' => '');
if ($booleanSearch) {
$boolean = "IN BOOLEAN MODE";
}
if ($extraFilter) {
$extraFilters['SiteTree'] = " AND {$extraFilter}";
if ($alternativeFileFilter) {
$extraFilters['File'] = " AND {$alternativeFileFilter}";
} else {
$extraFilters['File'] = $extraFilters['SiteTree'];
}
}
// Always ensure that only pages with ShowInSearch = 1 can be searched
$extraFilters['SiteTree'] .= " AND ShowInSearch <> 0";
// File.ShowInSearch was added later, keep the database driver backwards compatible
// by checking for its existence first
$fields = $this->fieldList('File');
if (array_key_exists('ShowInSearch', $fields)) {
$extraFilters['File'] .= " AND ShowInSearch <> 0";
}
$limit = $start . ", " . (int) $pageLength;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$match['SiteTree'] = "\n\t\t\t\tMATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$keywords}' {$boolean})\n\t\t\t\t+ MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$htmlEntityKeywords}' {$boolean})\n\t\t\t";
$match['File'] = "MATCH (Filename, Title, Content) AGAINST ('{$keywords}' {$boolean}) AND ClassName = 'File'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = str_replace(array('*', '+', '-'), '', $keywords);
$htmlEntityRelevanceKeywords = str_replace(array('*', '+', '-'), '', $htmlEntityKeywords);
$relevance['SiteTree'] = "MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$relevanceKeywords}') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$htmlEntityRelevanceKeywords}')";
$relevance['File'] = "MATCH (Filename, Title, Content) AGAINST ('{$relevanceKeywords}')";
} else {
$relevance['SiteTree'] = $relevance['File'] = 1;
$match['SiteTree'] = $match['File'] = "1 = 1";
}
// Generate initial queries and base table names
$baseClasses = array('SiteTree' => '', 'File' => '');
foreach ($classesToSearch as $class) {
$queries[$class] = singleton($class)->extendedSQL($notMatch . $match[$class] . $extraFilters[$class], "");
$baseClasses[$class] = reset($queries[$class]->from);
}
// Make column selection lists
$select = array('SiteTree' => array("ClassName", "{$baseClasses['SiteTree']}.ID", "ParentID", "Title", "MenuTitle", "URLSegment", "Content", "LastEdited", "Created", "_utf8'' AS Filename", "_utf8'' AS Name", "{$relevance['SiteTree']} AS Relevance", "CanViewType"), 'File' => array("ClassName", "{$baseClasses['File']}.ID", "_utf8'' AS ParentID", "Title", "_utf8'' AS MenuTitle", "_utf8'' AS URLSegment", "Content", "LastEdited", "Created", "Filename", "Name", "{$relevance['File']} AS Relevance", "NULL AS CanViewType"));
// Process queries
foreach ($classesToSearch as $class) {
// There's no need to do all that joining
$queries[$class]->from = array(str_replace('`', '', $baseClasses[$class]) => $baseClasses[$class]);
$queries[$class]->select = $select[$class];
$queries[$class]->orderby = null;
}
// Combine queries
$querySQLs = array();
$totalCount = 0;
foreach ($queries as $query) {
$querySQLs[] = $query->sql();
$totalCount += $query->unlimitedRowCount();
}
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy} LIMIT {$limit}";
// Get records
$records = DB::query($fullQuery);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new DataObjectSet($objects);
} else {
$doSet = new DataObjectSet();
}
$doSet->setPageLimits($start, $pageLength, $totalCount);
return $doSet;
}
/**
* The core search engine, used by this class and its subclasses to do fun stuff.
* Searches both SiteTree and File.
*/
public function searchEngine($keywords, $numPerPage = 10, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
{
$fileFilter = '';
$keywords = addslashes($keywords);
if ($booleanSearch) {
$boolean = "IN BOOLEAN MODE";
}
if ($extraFilter) {
$extraFilter = " AND {$extraFilter}";
if ($alternativeFileFilter) {
$fileFilter = " AND {$alternativeFileFilter}";
} else {
$fileFilter = $extraFilter;
}
}
if ($this->showInSearchTurnOn) {
$extraFilter .= " AND showInSearch <> 0";
}
$start = isset($_GET['start']) ? (int) $_GET['start'] : 0;
$limit = $start . ", " . (int) $numPerPage;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$matchContent = "MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$keywords}' {$boolean})";
$matchFile = "MATCH (Filename, Title, Content) AGAINST ('{$keywords}' {$boolean}) AND ClassName = 'File'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = str_replace(array('*', '+', '-'), '', $keywords);
$relevanceContent = "MATCH (Title) AGAINST ('{$relevanceKeywords}') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$relevanceKeywords}')";
$relevanceFile = "MATCH (Filename, Title, Content) AGAINST ('{$relevanceKeywords}')";
} else {
$relevanceContent = $relevanceFile = 1;
$matchContent = $matchFile = "1 = 1";
}
$queryContent = singleton('SiteTree')->extendedSQL($notMatch . $matchContent . $extraFilter, "");
$baseClass = reset($queryContent->from);
// There's no need to do all that joining
$queryContent->from = array(str_replace('`', '', $baseClass) => $baseClass);
$queryContent->select = array("ClassName", "{$baseClass}.ID", "ParentID", "Title", "URLSegment", "Content", "LastEdited", "Created", "_utf8'' AS Filename", "_utf8'' AS Name", "{$relevanceContent} AS Relevance");
$queryContent->orderby = null;
$queryFiles = singleton('File')->extendedSQL($notMatch . $matchFile . $fileFilter, "");
$baseClass = reset($queryFiles->from);
// There's no need to do all that joining
$queryFiles->from = array(str_replace('`', '', $baseClass) => $baseClass);
$queryFiles->select = array("ClassName", "{$baseClass}.ID", "_utf8'' AS ParentID", "Title", "_utf8'' AS URLSegment", "Content", "LastEdited", "Created", "Filename", "Name", "{$relevanceFile} AS Relevance");
$queryFiles->orderby = null;
$fullQuery = $queryContent->sql() . " UNION " . $queryFiles->sql() . " ORDER BY {$sortBy} LIMIT {$limit}";
$totalCount = $queryContent->unlimitedRowCount() + $queryFiles->unlimitedRowCount();
// die($fullQuery);
// Debug::show($fullQuery);
$records = DB::query($fullQuery);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new DataObjectSet($objects);
} else {
$doSet = new DataObjectSet();
}
$doSet->setPageLimits($start, $numPerPage, $totalCount);
return $doSet;
/*
$keywords = preg_replace_callback('/("[^"]+")(\040or\040)("[^"]+")/', $orProcessor, $keywords);
$keywords = preg_replace_callback('/([^ ]+)(\040or\040)([^ ]+)/', $orProcessor, $keywords);
$limit = (int)$_GET['start'] . ", " . $numPerPage;
$ret = DataObject::get("SiteTree", "MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) "
."AGAINST ('$keywords' IN BOOLEAN MODE) AND `ShowInSearch` = 1","Title","", $limit);
return $ret;
*/
}
/**
* The core search engine, used by this class and its subclasses to do fun stuff.
* Searches both SiteTree and File.
*
* @param string $keywords Keywords as a string.
*/
public function searchEngine($keywords, $pageLength = null, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
{
if (!$pageLength) {
$pageLength = $this->pageLength;
}
$fileFilter = '';
$keywords = addslashes($keywords);
$extraFilters = array('SiteTree' => '', 'File' => '');
if ($booleanSearch) {
$boolean = "IN BOOLEAN MODE";
}
if ($extraFilter) {
$extraFilters['SiteTree'] = " AND {$extraFilter}";
if ($alternativeFileFilter) {
$extraFilters['File'] = " AND {$alternativeFileFilter}";
} else {
$extraFilters['File'] = $extraFilters['SiteTree'];
}
}
if ($this->showInSearchTurnOn) {
$extraFilters['SiteTree'] .= " AND showInSearch <> 0";
}
$start = isset($_GET['start']) ? (int) $_GET['start'] : 0;
$limit = $start . ", " . (int) $pageLength;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$match['SiteTree'] = "MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$keywords}' {$boolean})";
$match['File'] = "MATCH (Filename, Title, Content) AGAINST ('{$keywords}' {$boolean}) AND ClassName = 'File'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = str_replace(array('*', '+', '-'), '', $keywords);
$relevance['SiteTree'] = "MATCH (Title) AGAINST ('{$relevanceKeywords}') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('{$relevanceKeywords}')";
$relevance['File'] = "MATCH (Filename, Title, Content) AGAINST ('{$relevanceKeywords}')";
} else {
$relevance['SiteTree'] = $relevance['File'] = 1;
$match['SiteTree'] = $match['File'] = "1 = 1";
}
// Generate initial queries and base table names
$baseClasses = array('SiteTree' => '', 'File' => '');
foreach ($this->classesToSearch as $class) {
$queries[$class] = singleton($class)->extendedSQL($notMatch . $match[$class] . $extraFilters[$class], "");
$baseClasses[$class] = reset($queries[$class]->from);
}
// Make column selection lists
$select = array('SiteTree' => array("ClassName", "{$baseClasses['SiteTree']}.ID", "ParentID", "Title", "URLSegment", "Content", "LastEdited", "Created", "_utf8'' AS Filename", "_utf8'' AS Name", "{$relevance['SiteTree']} AS Relevance", "CanViewType"), 'File' => array("ClassName", "{$baseClasses['File']}.ID", "_utf8'' AS ParentID", "Title", "_utf8'' AS URLSegment", "Content", "LastEdited", "Created", "Filename", "Name", "{$relevance['File']} AS Relevance", "NULL AS CanViewType"));
// Process queries
foreach ($this->classesToSearch as $class) {
// There's no need to do all that joining
$queries[$class]->from = array(str_replace('`', '', $baseClasses[$class]) => $baseClasses[$class]);
$queries[$class]->select = $select[$class];
$queries[$class]->orderby = null;
}
// Combine queries
$querySQLs = array();
$totalCount = 0;
foreach ($queries as $query) {
$querySQLs[] = $query->sql();
$totalCount += $query->unlimitedRowCount();
}
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy} LIMIT {$limit}";
// Get records
$records = DB::query($fullQuery);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new DataObjectSet($objects);
} else {
$doSet = new DataObjectSet();
}
$doSet->setPageLimits($start, $pageLength, $totalCount);
return $doSet;
}
/**
* The core search engine, used by this class and its subclasses to do fun stuff.
* Searches Businesses.
*/
public function searchEngine($keywords, $numPerPage = 10, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $invertedMatch = false)
{
/* $fileFilter = '';
$keywords = addslashes($keywords);
*/
if ($booleanSearch) {
$boolean = "IN BOOLEAN MODE";
}
if ($extraFilter) {
$extraFilter = " AND {$extraFilter}";
}
if ($this->showInSearchTurnOn) {
$extraFilter .= " AND SiteTree_Live.showInSearch <> 0";
}
$start = isset($_GET['start']) ? (int) $_GET['start'] : 0;
$limit = $start . ", " . (int) $numPerPage;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$matchContent = "MATCH (SiteTree_Live.Title, SiteTree_Live.MenuTitle, SiteTree_Live.Content, SiteTree_Live.MetaTitle, SiteTree_Live.MetaDescription, SiteTree_Live.MetaKeywords) AGAINST ('{$keywords}' {$boolean})";
$matchFile = "MATCH (Filename, Title, Content) AGAINST ('{$keywords}' {$boolean}) AND ClassName = 'File'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = str_replace(array('*', '+', '-'), '', $keywords);
$relevanceContent = "MATCH (SiteTree_Live.Title) AGAINST ('{$relevanceKeywords}') + MATCH (SiteTree_Live.Title, SiteTree_Live.MenuTitle, SiteTree_Live.Content, SiteTree_Live.MetaTitle, SiteTree_Live.MetaDescription, SiteTree_Live.MetaKeywords) AGAINST ('{$relevanceKeywords}')";
$relevanceFile = "MATCH (SiteTree_Live.Filename, SiteTree_Live.Title, SiteTree_Live.Content) AGAINST ('{$relevanceKeywords}')";
} else {
$relevanceContent = $relevanceFile = 1;
$matchContent = $matchFile = "1 = 1";
}
$queryContent = singleton('BusinessPage')->extendedSQL($notMatch . $matchContent . $extraFilter, "");
$baseClass = reset($queryContent->from);
// There's no need to do all that joining
// However - now we end up joining lots again!
$queryContent->from = array(str_replace('`', '', $baseClass) => $baseClass, 'INNER JOIN BusinessPage_Live ON SiteTree_Live.ID = BusinessPage_Live.ID', 'LEFT JOIN BusinessPage_Certifications ON BusinessPage_Live.ID = BusinessPage_Certifications.BusinessPageID', 'LEFT JOIN Certification ON Certification.ID = BusinessPage_Certifications.CertificationID ', 'LEFT JOIN SiteTree_Live as BusinessParent ON SiteTree_Live.ParentID = BusinessParent.ID', 'LEFT JOIN SiteTree_Live as BusinessGParent ON BusinessParent.ParentID = BusinessGParent.ID', 'LEFT JOIN SiteTree_Live as BusinessGGParent ON BusinessGParent.ParentID = BusinessGGParent.ID', 'LEFT JOIN SiteTree_Live as BusinessGGGParent ON BusinessGGParent.ParentID = BusinessGGGParent.ID');
$queryContent->select = array("DISTINCT {$baseClass}.ID", "{$baseClass}.ClassName", "{$baseClass}.ParentID", "{$baseClass}.Title", "{$baseClass}.URLSegment", "{$baseClass}.Content", "{$baseClass}.LastEdited", "{$baseClass}.Created", "_utf8'' AS Filename", "_utf8'' AS Name", "{$relevanceContent} AS Relevance");
$queryContent->orderby = null;
$fullQuery = $queryContent->sql() . " ORDER BY {$sortBy} LIMIT {$limit}";
$totalCount = $queryContent->unlimitedRowCount();
// die($fullQuery);
//Debug::show($fullQuery);
$records = DB::query($fullQuery);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new DataObjectSet($objects);
} else {
$doSet = new DataObjectSet();
}
$doSet->setPageLimits($start, $numPerPage, $totalCount);
return $doSet;
}
