/**
  * Displays a list of all members on the site that belong to the selected
  * groups.
  *
  * @return string
  */
 public function handleList($request)
 {
     $fields = $this->parent->Fields()->filter('MemberListVisible', true);
     $members = $this->parent->Groups()->relation('Members');
     $members = new PaginatedList($members, $request);
     $list = new PaginatedList(new ArrayList(), $request);
     $list->setLimitItems(false);
     $list->setTotalItems($members->getTotalItems());
     foreach ($members as $member) {
         $cols = new ArrayList();
         $public = $member->getPublicFields();
         $link = $this->Link($member->ID);
         foreach ($fields as $field) {
             if ($field->PublicVisibility == 'MemberChoice' && !in_array($field->MemberField, $public)) {
                 $value = null;
             } else {
                 $value = $member->{$field->MemberField};
             }
             $cols->push(new ArrayData(array('Name' => $field->MemberField, 'Title' => $field->Title, 'Value' => $value, 'Sortable' => $member->hasDatabaseField($field->MemberField), 'Link' => $link)));
         }
         $list->push($member->customise(array('Fields' => $cols)));
     }
     $this->data()->Title = _t('MemberProfiles.MEMBERLIST', 'Member List');
     $this->data()->Parent = $this->parent;
     $controller = $this->customise(array('Members' => $list));
     return $controller->renderWith(array('MemberProfileViewer_list', 'MemberProfileViewer', 'Page'));
 }
 /**
  * Return the submissions from the site
  *
  * @return PaginatedList
  */
 public function getSubmissions($page = 1)
 {
     $record = $this->form->getRecord();
     $submissions = $record->getComponents('Submissions', null, "\"Created\" DESC");
     $query = DB::query(sprintf("SELECT COUNT(*) AS \"CountRows\" FROM \"SubmittedForm\" WHERE \"ParentID\" = '%d'", $record->ID));
     $totalCount = 0;
     foreach ($query as $r) {
         $totalCount = $r['CountRows'];
     }
     $list = new PaginatedList($submissions);
     $list->setCurrentPage($page);
     $list->setPageLength(10);
     $list->setTotalItems($totalCount);
     return $list;
 }
示例#3
0
 /**
  * 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;
 }
示例#4
0
 /**
  * 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;
 }
 /**
  * The core search engine configuration.
  * @todo Properly extract the search functions out of the core.
  *
  * @param string $keywords Keywords as a space separated string
  * @return object DataObjectSet of result pages
  */
 public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "ts_rank DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
 {
     //Fix the keywords to be ts_query compatitble:
     //Spaces must have pipes
     //@TODO: properly handle boolean operators here.
     $keywords = trim($keywords);
     $keywords = str_replace(' ', ' | ', $keywords);
     $keywords = str_replace('"', "'", $keywords);
     $keywords = Convert::raw2sql(trim($keywords));
     $htmlEntityKeywords = htmlentities($keywords, ENT_NOQUOTES);
     //We can get a list of all the tsvector columns though this query:
     //We know what tables to search in based on the $classesToSearch variable:
     $result = DB::query("SELECT table_name, column_name, data_type FROM information_schema.columns WHERE data_type='tsvector' AND table_name in ('" . implode("', '", $classesToSearch) . "');");
     if (!$result->numRecords()) {
         throw new Exception('there are no full text columns to search');
     }
     $tables = array();
     // Make column selection lists
     $select = array('SiteTree' => array("\"ClassName\"", "\"SiteTree\".\"ID\"", "\"ParentID\"", "\"Title\"", "\"URLSegment\"", "\"Content\"", "\"LastEdited\"", "\"Created\"", "NULL AS \"Filename\"", "NULL AS \"Name\"", "\"CanViewType\""), 'File' => array("\"ClassName\"", "\"File\".\"ID\"", "0 AS \"ParentID\"", "\"Title\"", "NULL AS \"URLSegment\"", "\"Content\"", "\"LastEdited\"", "\"Created\"", "\"Filename\"", "\"Name\"", "NULL AS \"CanViewType\""));
     foreach ($result as $row) {
         if ($row['table_name'] == 'SiteTree') {
             $showInSearch = "AND \"ShowInSearch\"=1 ";
         } elseif ($row['table_name'] == 'File') {
             // File.ShowInSearch was added later, keep the database driver backwards compatible
             // by checking for its existence first
             $fields = $this->fieldList($row['table_name']);
             if (array_key_exists('ShowInSearch', $fields)) {
                 $showInSearch = "AND \"ShowInSearch\"=1 ";
             } else {
                 $showInSearch = '';
             }
         } else {
             $showInSearch = '';
         }
         //public function extendedSQL($filter = "", $sort = "", $limit = "", $join = "", $having = ""){
         $where = "\"" . $row['table_name'] . "\".\"" . $row['column_name'] . "\" " . $this->default_fts_search_method . ' q ' . $showInSearch;
         $query = DataList::create($row['table_name'])->where($where, '')->dataQuery()->query();
         $query->addFrom(array('tsearch' => ", to_tsquery('" . $this->get_search_language() . "', '{$keywords}') AS q"));
         $query->setSelect(array());
         foreach ($select[$row['table_name']] as $clause) {
             if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause, $matches)) {
                 $query->selectField($matches[1], $matches[2]);
             } else {
                 $query->selectField($clause);
             }
         }
         $query->selectField("ts_rank(\"{$row['table_name']}\".\"{$row['column_name']}\", q)", 'Relevance');
         $query->setOrderBy(array());
         //Add this query to the collection
         $tables[] = $query->sql();
     }
     $limit = $pageLength;
     $offset = $start;
     if ($keywords) {
         $orderBy = " ORDER BY {$sortBy}";
     } else {
         $orderBy = '';
     }
     $fullQuery = "SELECT * FROM (" . implode(" UNION ", $tables) . ") AS q1 {$orderBy} LIMIT {$limit} OFFSET {$offset}";
     // Get records
     $records = DB::query($fullQuery);
     $totalCount = 0;
     foreach ($records as $record) {
         $objects[] = new $record['ClassName']($record);
         $totalCount++;
     }
     if (isset($objects)) {
         $results = new ArrayList($objects);
     } else {
         $results = new ArrayList();
     }
     $list = new PaginatedList($results);
     $list->setPageStart($start);
     $list->setPageLength($pageLength);
     $list->setTotalItems($totalCount);
     return $list;
 }
 public function testPrevLink()
 {
     $list = new PaginatedList(new ArrayList());
     $list->setTotalItems(50);
     $this->assertNull($list->PrevLink());
     $list->setCurrentPage(2);
     $this->assertContains('start=0', $list->PrevLink());
     $list->setCurrentPage(3);
     $this->assertContains('start=10', $list->PrevLink());
     $list->setCurrentPage(5);
     $this->assertContains('start=30', $list->PrevLink());
     // Disable paging
     $list->setPageLength(0);
     $this->assertNull($list->PrevLink());
 }
 /**
  * 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)
 {
     if (isset($objects)) {
         $results = new ArrayList($objects);
     } else {
         $results = new ArrayList();
     }
     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, array_values(ClassInfo::dataClassesFor($class)));
     }
     $allClassesToSearch = array_unique($allClassesToSearch);
     //Get a list of all the tables and columns we'll be searching on:
     $fulltextColumns = $this->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 $results;
         }
         // 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");
         } elseif (strpos($tableName, 'File') === 0) {
             // File.ShowInSearch was added later, keep the database driver backwards compatible
             // by checking for its existence first
             $fields = $this->fieldList($tableName);
             if (array_key_exists('ShowInSearch', $fields)) {
                 $where = array("\"{$tableName}\".\"ShowInSearch\"!=0");
             }
         }
         $queries[$tableName] = DataList::create($tableName)->where($where, '')->dataQuery()->query();
         $queries[$tableName]->setOrderBy(array());
         // Join with CONTAINSTABLE, a full text searcher that includes relevance factor
         $queries[$tableName]->setFrom(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]->setFrom("INNER JOIN \"{$baseClass}\" ON  \"{$baseClass}\".\"ID\"=\"{$tableName}\".\"ID\"");
         }
         $queries[$tableName]->setSelect(array("\"{$tableName}\".\"ID\""));
         $queries[$tableName]->selectField("'{$tableName}'", 'Source');
         $queries[$tableName]->selectField('Rank', 'Relevance');
         if ($extraFilter) {
             $queries[$tableName]->addWhere($extraFilter);
         }
         if (count($allClassesToSearch)) {
             $classesPlaceholder = DB::placeholders($allClassesToSearch);
             $queries[$tableName]->addWhere(array("\"{$baseClass}\".\"ClassName\" IN ({$classesPlaceholder})" => $allClassesToSearch));
         }
         // Reset the parameters that would get in the way
     }
     // Generate SQL
     $querySQLs = array();
     $queryParameters = array();
     foreach ($queries as $query) {
         $querySQLs[] = $query->sql($parameters);
         $queryParameters = array_merge($queryParameters, $parameters);
     }
     // Unite the SQL
     $fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY {$sortBy}";
     // Perform the search
     $result = $this->preparedQuery($fullQuery, $queryParameters);
     // Regenerate DataObjectSet - watch out, numRecords doesn't work on sqlsrv driver on Windows.
     $current = -1;
     $objects = array();
     foreach ($result as $row) {
         $current++;
         // Select a subset for paging
         if ($current >= $start && $current < $start + $pageLength) {
             $objects[] = DataObject::get_by_id($row['Source'], $row['ID']);
         }
     }
     if (isset($objects)) {
         $results = new ArrayList($objects);
     } else {
         $results = new ArrayList();
     }
     $list = new PaginatedList($results);
     $list->setPageStart($start);
     $list->setPageLength($pageLength);
     $list->setTotalItems($current + 1);
     return $list;
 }
 /**
  * Perform a search against the data table.
  *
  * @param array $where Array of strings to add into the WHERE clause
  * @param array $orderby Array of column as key, to direction as value to add into the ORDER BY clause
  * @param string|int $start Record to start at (for paging)
  * @param string|int $pageLength Number of results per page (for paging)
  * @param boolean $paged Paged results or not?
  * @return ArrayList|PaginatedList
  */
 protected function queryList($where = array(), $orderby = array(), $start, $pageLength, $paged = true)
 {
     $dataClass = $this->dataRecord->getDataClass();
     if (!$dataClass) {
         return new PaginatedList(new ArrayList());
     }
     $resultColumns = $this->dataRecord->getDataSingleton()->summaryFields();
     $resultColumns['ID'] = 'ID';
     $results = new ArrayList();
     $query = new SQLQuery();
     $query->setSelect($this->escapeSelect(array_keys($resultColumns)))->setFrom("\"{$dataClass}\"");
     $query->addWhere($where);
     $query->addOrderBy($orderby);
     $query->setConnective('AND');
     if ($paged) {
         $query->setLimit($pageLength, $start);
     }
     foreach ($query->execute() as $record) {
         $result = new $dataClass($record);
         $result->Columns = $this->Columns($result);
         // we attach Columns here so the template can loop through them on each result
         $results->push($result);
     }
     if ($paged) {
         $list = new PaginatedList($results);
         $list->setPageStart($start);
         $list->setPageLength($pageLength);
         $list->setTotalItems($query->unlimitedRowCount());
         $list->setLimitItems(false);
     } else {
         $list = $results;
     }
     return $list;
 }
 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');
     }
     $keywords = $this->escapeString($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, MetaDescription) AGAINST ('{$keywords}' {$boolean})\n\t\t\t\t+ MATCH (Title, MenuTitle, Content, MetaDescription) 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, MetaDescription) " . "AGAINST ('{$relevanceKeywords}') " . "+ MATCH (Title, MenuTitle, Content, MetaDescription) AGAINST ('{$htmlEntityRelevanceKeywords}')";
         $relevance['File'] = "MATCH (Filename, Title, Content) AGAINST ('{$relevanceKeywords}')";
     } else {
         $relevance['SiteTree'] = $relevance['File'] = 1;
         $match['SiteTree'] = $match['File'] = "1 = 1";
     }
     // Generate initial DataLists and base table names
     $lists = array();
     $baseClasses = array('SiteTree' => '', 'File' => '');
     foreach ($classesToSearch as $class) {
         $lists[$class] = DataList::create($class)->where($notMatch . $match[$class] . $extraFilters[$class], "");
         $baseClasses[$class] = '"' . $class . '"';
     }
     $charset = Config::inst()->get('MySQLDatabase', 'charset');
     // Make column selection lists
     $select = array('SiteTree' => array("ClassName", "{$baseClasses['SiteTree']}.\"ID\"", "ParentID", "Title", "MenuTitle", "URLSegment", "Content", "LastEdited", "Created", "Filename" => "_{$charset}''", "Name" => "_{$charset}''", "Relevance" => $relevance['SiteTree'], "CanViewType"), 'File' => array("ClassName", "{$baseClasses['File']}.\"ID\"", "ParentID", "Title", "MenuTitle" => "_{$charset}''", "URLSegment" => "_{$charset}''", "Content", "LastEdited", "Created", "Filename", "Name", "Relevance" => $relevance['File'], "CanViewType" => "NULL"));
     // Process and combine queries
     $querySQLs = array();
     $queryParameters = array();
     $totalCount = 0;
     foreach ($lists as $class => $list) {
         $query = $list->dataQuery()->query();
         // There's no need to do all that joining
         $query->setFrom(array(str_replace(array('"', '`'), '', $baseClasses[$class]) => $baseClasses[$class]));
         $query->setSelect($select[$class]);
         $query->setOrderBy(array());
         $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);
     $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);
     // The list has already been limited by the query above
     $list->setLimitItems(false);
     return $list;
 }
 /**
  * The core search engine configuration.
  * @todo Properly extract the search functions out of the core.
  *
  * @param string $keywords Keywords as a space separated string
  * @return object DataObjectSet of result pages
  */
 public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "ts_rank DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
 {
     //Fix the keywords to be ts_query compatitble:
     //Spaces must have pipes
     //@TODO: properly handle boolean operators here.
     $keywords = trim($keywords);
     $keywords = str_replace(' ', ' | ', $keywords);
     $keywords = str_replace('"', "'", $keywords);
     $keywords = $this->quoteString(trim($keywords));
     //We can get a list of all the tsvector columns though this query:
     //We know what tables to search in based on the $classesToSearch variable:
     $classesPlaceholders = DB::placeholders($classesToSearch);
     $result = $this->preparedQuery("\n\t\t\tSELECT table_name, column_name, data_type\n\t\t\tFROM information_schema.columns\n\t\t\tWHERE data_type='tsvector' AND table_name in ({$classesPlaceholders});", $classesToSearch);
     if (!$result->numRecords()) {
         throw new Exception('there are no full text columns to search');
     }
     $tables = array();
     $tableParameters = array();
     // Make column selection lists
     $select = array('SiteTree' => array('"ClassName"', '"SiteTree"."ID"', '"ParentID"', '"Title"', '"URLSegment"', '"Content"', '"LastEdited"', '"Created"', 'NULL AS "Name"', '"CanViewType"'), 'File' => array('"ClassName"', '"File"."ID"', '0 AS "ParentID"', '"Title"', 'NULL AS "URLSegment"', 'NULL AS "Content"', '"LastEdited"', '"Created"', '"Name"', 'NULL AS "CanViewType"'));
     foreach ($result as $row) {
         $conditions = array();
         if ($row['table_name'] === 'SiteTree' || $row['table_name'] === 'File') {
             $conditions[] = array('"ShowInSearch"' => 1);
         }
         $method = self::default_fts_search_method();
         $conditions[] = "\"{$row['table_name']}\".\"{$row['column_name']}\" {$method} q ";
         $query = DataObject::get($row['table_name'], $conditions)->dataQuery()->query();
         // Could parameterise this, but convention is only to to so for where conditions
         $query->addFrom(array('tsearch' => ", to_tsquery('" . self::search_language() . "', {$keywords}) AS q"));
         $query->setSelect(array());
         foreach ($select[$row['table_name']] as $clause) {
             if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause, $matches)) {
                 $query->selectField($matches[1], $matches[2]);
             } else {
                 $query->selectField($clause);
             }
         }
         $query->selectField("ts_rank(\"{$row['table_name']}\".\"{$row['column_name']}\", q)", 'Relevance');
         $query->setOrderBy(array());
         //Add this query to the collection
         $tables[] = $query->sql($parameters);
         $tableParameters = array_merge($tableParameters, $parameters);
     }
     $limit = $pageLength;
     $offset = $start;
     if ($keywords) {
         $orderBy = " ORDER BY {$sortBy}";
     } else {
         $orderBy = '';
     }
     $fullQuery = "SELECT * FROM (" . implode(" UNION ", $tables) . ") AS q1 {$orderBy} LIMIT {$limit} OFFSET {$offset}";
     // Get records
     $records = $this->preparedQuery($fullQuery, $tableParameters);
     $totalCount = 0;
     foreach ($records as $record) {
         $objects[] = new $record['ClassName']($record);
         $totalCount++;
     }
     if (isset($objects)) {
         $results = new ArrayList($objects);
     } else {
         $results = new ArrayList();
     }
     $list = new PaginatedList($results);
     $list->setLimitItems(false);
     $list->setPageStart($start);
     $list->setPageLength($pageLength);
     $list->setTotalItems($totalCount);
     return $list;
 }