/**
* The core search engine configuration.
* @todo There is a fulltext search for SQLite making use of virtual tables, the fts3 extension and the
* MATCH operator
* there are a few issues with fts:
* - shared cached lock doesn't allow to create virtual tables on versions prior to 3.6.17
* - there must not be more than one MATCH operator per statement
* - the fts3 extension needs to be available
* for now we use the MySQL implementation with the MATCH()AGAINST() uglily replaced with LIKE
*
* @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)
{
$keywords = $this->escapeString(str_replace(array('*', '+', '-', '"', '\''), '', $keywords));
$htmlEntityKeywords = htmlentities(utf8_decode($keywords));
$extraFilters = array('SiteTree' => '', 'File' => '');
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->getSchemaManager()->fieldList('File');
if (array_key_exists('ShowInSearch', $fields)) {
$extraFilters['File'] .= " AND ShowInSearch <> 0";
}
$limit = $start . ", " . (int) $pageLength;
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$match['SiteTree'] = "\r\n\t\t\t\t(Title LIKE '%{$keywords}%' OR MenuTitle LIKE '%{$keywords}%' OR Content LIKE '%{$keywords}%' OR MetaDescription LIKE '%{$keywords}%' OR\r\n\t\t\t\tTitle LIKE '%{$htmlEntityKeywords}%' OR MenuTitle LIKE '%{$htmlEntityKeywords}%' OR Content LIKE '%{$htmlEntityKeywords}%' OR MetaDescription LIKE '%{$htmlEntityKeywords}%')\r\n\t\t\t";
$match['File'] = "(Filename LIKE '%{$keywords}%' OR Title LIKE '%{$keywords}%' OR Content LIKE '%{$keywords}%') AND ClassName = 'File'";
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = $keywords;
$htmlEntityRelevanceKeywords = $htmlEntityKeywords;
$relevance['SiteTree'] = "(Title LIKE '%{$relevanceKeywords}%' OR MenuTitle LIKE '%{$relevanceKeywords}%' OR Content LIKE '%{$relevanceKeywords}%' OR MetaDescription LIKE '%{$relevanceKeywords}%') + (Title LIKE '%{$htmlEntityRelevanceKeywords}%' OR MenuTitle LIKE '%{$htmlEntityRelevanceKeywords}%' OR Content LIKE '%{$htmlEntityRelevanceKeywords}%' OR MetaDescription LIKE '%{$htmlEntityRelevanceKeywords}%')";
$relevance['File'] = "(Filename LIKE '%{$relevanceKeywords}%' OR Title LIKE '%{$relevanceKeywords}%' OR Content LIKE '%{$relevanceKeywords}%')";
} else {
$relevance['SiteTree'] = $relevance['File'] = 1;
$match['SiteTree'] = $match['File'] = "1 = 1";
}
// Generate initial queries and base table names
$baseClasses = array('SiteTree' => '', 'File' => '');
$queries = array();
foreach ($classesToSearch as $class) {
$queries[$class] = DataList::create($class)->where($notMatch . $match[$class] . $extraFilters[$class], "")->dataQuery()->query();
$fromArr = $queries[$class]->getFrom();
$baseClasses[$class] = reset($fromArr);
}
// Make column selection lists
$select = array('SiteTree' => array("\"ClassName\"", "\"ID\"", "\"ParentID\"", "\"Title\"", "\"URLSegment\"", "\"Content\"", "\"LastEdited\"", "\"Created\"", "NULL AS \"Filename\"", "NULL AS \"Name\"", "\"CanViewType\"", "{$relevance['SiteTree']} AS Relevance"), 'File' => array("\"ClassName\"", "\"ID\"", "NULL AS \"ParentID\"", "\"Title\"", "NULL AS \"URLSegment\"", "\"Content\"", "\"LastEdited\"", "\"Created\"", "\"Filename\"", "\"Name\"", "NULL AS \"CanViewType\"", "{$relevance['File']} AS Relevance"));
// Process queries
foreach ($classesToSearch as $class) {
// There's no need to do all that joining
$queries[$class]->setFrom($baseClasses[$class]);
$queries[$class]->setSelect(array());
foreach ($select[$class] as $clause) {
if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause, $matches)) {
$queries[$class]->selectField($matches[1], $matches[2]);
} else {
$queries[$class]->selectField(str_replace('"', '', $clause));
}
}
$queries[$class]->setOrderBy(array());
}
// Combine queries
$querySQLs = array();
$queryParameters = array();
$totalCount = 0;
foreach ($queries as $query) {
$querySQLs[] = $query->sql($parameters);
$queryParameters = array_merge($queryParameters, $parameters);
$totalCount += $query->unlimitedRowCount();
}
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy} LIMIT {$limit}";
// Get records
$records = $this->preparedQuery($fullQuery, $queryParameters);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new ArrayList($objects);
} else {
$doSet = new ArrayList();
}
$list = new PaginatedList($doSet);
$list->setPageStart($start);
$list->setPageLEngth($pageLength);
$list->setTotalItems($totalCount);
return $list;
}
/**
* 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)
{
if (!class_exists('SiteTree')) {
throw new Exception('MySQLDatabase->searchEngine() requires "SiteTree" class');
}
if (!class_exists('File')) {
throw new Exception('MySQLDatabase->searchEngine() requires "File" class');
}
$fileFilter = '';
$keywords = Convert::raw2sql($keywords);
$htmlEntityKeywords = htmlentities($keywords, ENT_NOQUOTES, 'UTF-8');
$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);
$objects = array();
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
$list = new PaginatedList(new ArrayList($objects));
$list->setPageStart($start);
$list->setPageLEngth($pageLength);
$list->setTotalItems($totalCount);
return $list;
}
