/** * 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; }