/**
* Returns a list of all the comments attached to this record.
*
* @return PaginatedList
*/
public function Comments()
{
$order = Commenting::get_config_value($this->ownerBaseClass, 'order_comments_by');
$list = new PaginatedList(Comment::get()->where(sprintf("ParentID = '%s' AND BaseClass = '%s'", $this->owner->ID, $this->ownerBaseClass))->sort($order));
$list->setPageLength(Commenting::get_config_value($this->ownerBaseClass, 'comments_per_page'));
$controller = Controller::curr();
$list->setPageStart($controller->request->getVar("commentsstart" . $this->owner->ID));
$list->setPaginationGetVar("commentsstart" . $this->owner->ID);
$list->MoreThanOnePage();
return $list;
}
/**
* 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;
}
/**
* Returns a list of paginated blog posts based on the BlogPost dataList.
*
* @return PaginatedList
*/
public function PaginatedList()
{
/**
* @var Blog $dataRecord
*/
$dataRecord = $this->dataRecord;
$posts = new PaginatedList($this->blogPosts);
if ($this->PostsPerPage > 0) {
$posts->setPageLength($this->PostsPerPage);
} else {
$pageSize = 99999;
if ($count = $dataRecord->getBlogPosts()->count()) {
$pageSize = $count;
}
$posts->setPageLength($pageSize);
}
$start = $this->request->getVar($posts->getPaginationGetVar());
$posts->setPageStart($start);
return $posts;
}
/**
* 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 testFirstItem()
{
$list = new PaginatedList(new ArrayList());
$this->assertEquals(1, $list->FirstItem());
$list->setPageStart(10);
$this->assertEquals(11, $list->FirstItem());
}
/**
* Builds the DataObject of filtered products
*
* @return DataList
*
* @author Sebastian Diel <sdiel@πixeltricks.de>
* @since 23.09.2014
*/
public function buildSearchResultProducts()
{
$searchResultProducts = $this->searchResultProducts;
$productsPerPage = $this->getProductsPerPageSetting();
$SQL_start = $this->getSqlOffset();
$searchQuery = $this->getSearchQuery();
$searchTerms = explode(' ', $searchQuery);
$filter = '';
$useExtensionResults = $this->extend('updateSearchResult', $searchResultProducts, $searchQuery, $SQL_start);
if (empty($useExtensionResults)) {
$this->listFilters['original'] = sprintf('
"SilvercartProduct"."SilvercartProductGroupID" IS NOT NULL AND
"SilvercartProduct"."SilvercartProductGroupID" > 0 AND
"SPGPL"."ID" > 0 AND
"SilvercartProduct"."isActive" = 1 AND (
(
"SilvercartProductLanguage"."Title" LIKE \'%s%%\' OR
"SilvercartProductLanguage"."ShortDescription" LIKE \'%s%%\' OR
"SilvercartProductLanguage"."LongDescription" LIKE \'%s%%\' OR
"SilvercartProductLanguage"."Title" LIKE \'%%%s%%\' OR
"SilvercartProductLanguage"."ShortDescription" LIKE \'%%%s%%\' OR
"SilvercartProductLanguage"."LongDescription" LIKE \'%%%s%%\'
) OR
"SilvercartProductLanguage"."MetaKeywords" LIKE \'%%%s%%\' OR
"SilvercartProduct"."ProductNumberShop" LIKE \'%%%s%%\' OR
"SilvercartProduct"."EANCode" LIKE \'%%%s%%\' OR
STRCMP(
SOUNDEX("SilvercartProductLanguage"."Title"), SOUNDEX(\'%s\')
) = 0
)
', $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery, $searchQuery);
if (count(self::$registeredFilterPlugins) > 0) {
foreach (self::$registeredFilterPlugins as $registeredPlugin) {
$pluginFilters = $registeredPlugin->filter();
if (is_array($pluginFilters)) {
$this->listFilters = array_merge($this->listFilters, $pluginFilters);
}
}
}
$this->extend('updateListFilters', $this->listFilters, $searchTerms);
foreach ($this->listFilters as $listFilter) {
if (empty($filter)) {
$filter = $listFilter;
} else {
if (strpos(trim($listFilter), 'AND') !== 0 && strpos(trim($listFilter), 'OR') !== 0) {
$listFilter = 'AND ' . $listFilter;
}
$filter = '(' . $filter . ') ' . $listFilter;
}
}
if (SilvercartProduct::defaultSort() == 'relevance') {
$sort = '';
} else {
$sort = SilvercartProduct::defaultSort();
}
$searchResultProductsRaw = SilvercartProduct::getProducts($filter, $sort, array(array('table' => 'SilvercartProductGroupPage_Live', 'on' => '"SPGPL"."ID" = "SilvercartProduct"."SilvercartProductGroupID"', 'alias' => 'SPGPL')));
$searchResultProducts = new PaginatedList($searchResultProductsRaw, $this->getRequest());
$searchResultProducts->setPageStart($SQL_start);
$searchResultProducts->setPageLength($productsPerPage);
}
$this->searchResultProducts = $searchResultProducts;
$this->totalNumberOfProducts = $searchResultProducts->count();
$searchQueryObject = SilvercartSearchQuery::get_by_query(Convert::raw2sql($searchQuery));
if ($searchQueryObject->Hits != $this->totalNumberOfProducts) {
$searchQueryObject->Hits = $this->totalNumberOfProducts;
$searchQueryObject->write();
}
return $this->searchResultProducts;
}
/**
* All viewable product groups of this group.
*
* @param int $numberOfProductGroups Number of product groups to display
*
* @return PaginatedList
*
* @author Sebastian Diel <*****@*****.**>, Ramon Kupper <*****@*****.**>
* @since 04.01.2014
*/
public function getViewableChildren($numberOfProductGroups = false)
{
if ($this->viewableChildren === null) {
$viewableChildren = new ArrayList();
foreach ($this->Children() as $child) {
if ($child->hasProductsOrChildren()) {
$viewableChildren->push($child);
}
}
if ($viewableChildren->count() > 0) {
if ($numberOfProductGroups == false) {
if ($this->productGroupsPerPage) {
$pageLength = $this->productGroupsPerPage;
} else {
$pageLength = SilvercartConfig::ProductGroupsPerPage();
}
} else {
$pageLength = $numberOfProductGroups;
}
$pageStart = $this->getSqlOffsetForProductGroups($numberOfProductGroups);
$viewableChildrenPage = new PaginatedList($viewableChildren, $this->getRequest());
$viewableChildrenPage->setPaginationGetVar('groupStart');
$viewableChildrenPage->setPageStart($pageStart);
$viewableChildrenPage->setPageLength($pageLength);
$this->viewableChildren = $viewableChildrenPage;
} else {
return false;
}
}
return $this->viewableChildren;
}
/**
* 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;
}
/**
* Returns a list of paginated blog posts based on the BlogPost dataList.
*
* @return PaginatedList
*/
public function PaginatedList()
{
$allPosts = $this->blogPosts ?: new ArrayList();
$posts = new PaginatedList($allPosts);
// Set appropriate page size
if ($this->PostsPerPage > 0) {
$pageSize = $this->PostsPerPage;
} elseif ($count = $allPosts->count()) {
$pageSize = $count;
} else {
$pageSize = 99999;
}
$posts->setPageLength($pageSize);
// Set current page
$start = $this->request->getVar($posts->getPaginationGetVar());
$posts->setPageStart($start);
return $posts;
}
/**
* 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;
}
/**
* Gets the results from the given query.
*
* Appends to the usual results the matches on the GtkdocSection
* instances on the following order:
*
* 1. GtkdocSections with matches in 'Title'
* 2. GtkdocSections with matches in 'MetaDescription'
* 3. GtkdocSections with matches in 'Content'
*
* Eventual duplicates are dropped from the list.
*
* @param array $data The raw data submitted by user
* @param SearchForm $form The form instance
* @param SS_HTTPRequest $request Request for this action
*/
public function results($data, $form, $request)
{
$keywords = $data['Search'];
// Populate the first matches in the usual way
$matches = $form->getResults()->toArray();
// Get the list of GtkdocSection with matching title...
$context = singleton('GtkdocSection')->getDefaultSearchContext();
$gtkdoc_matches = $context->getResults(array('Title' => $keywords))->toArray();
// ...and add to them the list of GtkdocSection with matching
// description and matching content, skipping the duplicates...
self::merge($gtkdoc_matches, $context->getResults(array('MetaDescription' => $keywords))->toArray());
self::merge($gtkdoc_matches, $context->getResults(array('Content' => $keywords))->toArray());
// Append the GtkdocSection matches to the original ones
$matches = array_merge($matches, $gtkdoc_matches);
$results = new PaginatedList(new ArrayList($matches));
$results->setPageLength(10);
$results->setLimitItems(true);
$results->setPageStart($request->getVar('start'));
$data = array('Results' => $results, 'Query' => $form->getSearchQuery(), 'Title' => _t('SearchForm.SearchResults', 'Search Results'));
return $this->owner->customise($data)->renderWith(array('Page_results', 'Page'));
}
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;
}
/**
* Returns a list of paginated blog posts based on the blogPost dataList
*
* @return PaginatedList
**/
public function PaginatedList()
{
$posts = new PaginatedList($this->blogPosts);
// If pagination is set to '0' then no pagination will be shown.
if ($this->PostsPerPage > 0) {
$posts->setPageLength($this->PostsPerPage);
} else {
$posts->setPageLength($this->getBlogPosts()->count());
}
$start = $this->request->getVar($posts->getPaginationGetVar());
$posts->setPageStart($start);
return $posts;
}
/**
* 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;
}
